Help in writing a query

[creative1]

hi everyone
I am using vb6 and access2003 combination. I have divided product codes in several catagories(11,1 2... ) 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,1 2... ) 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.Cat Name, Sum(P.Qty), Count(P.*) From
CatMas C, Product P Where C.CatCode = P.CatCode Group By
C.CatCode,C.Cat Name

Regards
Veena

[creative1]

I ahev follwoing table
catagory_codes( cat_code,descri ption)
product_codes(c at_code, item_code,descr iption,unit_pri ce,...)
inv_total(tran_ id,date........ .........i m not using date yet)
inv_detail(tran _id,item_code,q uantity,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.quan tity) AS price1 FROM catagory_codes, product_codes, inv_detail WHERE catagory_codes. cat_code = product_codes.c at_code AND product_codes.I tem_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