Help in writing a query

[creative1]

hi everyone
I am using vb6 and access2003 combination. I have divided product codes in several catagories(11,12... ) and then using these catagories to make product codes (11001,11002,...) . For my daily closing report I want to see totals of all catagories belonging to one catagory group. I want to retrieve sum of particular group of catagories.
these catagories are
Metals (products having catagory codes 10-16)
Papers (................................................. ..18)
UBC (...............................................19-31)

I have two tables one for category codes, one for product codes, but no table for group codes.
I have tries doing this using parent child commands(poor approach because I could not use all child commands one report if I use it with dataenviornment)
any suggestions please.
regards

[QVeen72]

hi everyone
I am using vb6 and access2003 combination. I have divided product codes in several catagories(11,12... ) and then using these catagories to make product codes (11001,11002,...) . For my daily closing report I want to see totals of all catagories belonging to one catagory group. I want to retrieve sum of particular group of catagories.
these catagories are
Metals (products having catagory codes 10-16)
Papers (................................................. ..18)
UBC (...............................................19-31)

I have two tables one for category codes, one for product codes, but no table for group codes.
I have tries doing this using parent child commands(poor approach because I could not use all child commands one report if I use it with dataenviornment)
any suggestions please.
regards

use this SQL:

Select C.CatCode,C.CatName, Sum(P.Qty), Count(P.*) From
CatMas C, Product P Where C.CatCode = P.CatCode Group By
C.CatCode,C.CatName

Regards
Veena

[creative1]

I ahev follwoing table
catagory_codes(cat_code,description)
product_codes(cat_code, item_code,description,unit_price,...)
inv_total(tran_id,date.................i m not using date yet)
inv_detail(tran_id,item_code,quantity,price..)

here I have to use orice in table inv_detail not in product_codes because they may vary.

here is what i am using to get total of metals(by grouping cat code)

SELECT SUM(inv_detail.price * inv_detail.quantity) AS price1 FROM catagory_codes, product_codes, inv_detail WHERE catagory_codes.cat_code = product_codes.cat_code AND product_codes.Item_code = inv_detail.item_code GROUP BY catagory_codes.cat_code HAVING (catagory_codes.cat_code > 9 AND catagory_codes.cat_code < 19)

the problem is it only get me for one group.
It looks like I should create another table. It has screwed my mind. Please help me.
regards
farhana

[creative1]

never mind I did it on my own.
thank for help Veena I got some clues though
farhana