make sql query without crosstab

[aljoh...@yahoo.com.sg]

I have created the following tables like the Ms-Access Nwind database.

the following tables are normalised

i.Group Table

ID Group
1 Cupboard
2 Chair
3 Bed

ii.Catagory Table

ID Catagory UnderGroup
1 Acat 1
2 Bcat 2
3 Ccat 1
4 Acat 2

iii.Products Table

ProdId Catagory
2AD99 1
2H899 2
5KKJ 4
3HHF 3
5KKJ 3
77NF 1

iv.Invoice Table

Area PrdCode Qty Group Catagory
MX 2AD54 2 1 1
AA 2H899 5 2 1
FD 5KKJ 17 1 3
MX 3HHF 1 1 2
FD 5KKJ 5 3 2
FD 77NF 3 1 3

as you can see there is a group above the catagory

i want to make query group-catagory from Invoice Table
in querybuilder there is crosstab but i prefer a sql query)
how is this possible

By Groups

Area Total-Cupboard Total-Chair Total-Bed
MX 3 0 0
AA 0 2 0
FD 20 0 5

for example
By Catagory (Cupboard)

Area Total-Acat Total-Bcat Total-Ccat
MX 2 1 0
AA 0 0 0
FD 0 0 20

Dear all group members

Your help for a beginner will be highly appriciated.