OKay this may seem a little complicated.
Some background : We have a access database which we use for our invoicing to customer for a t-shirt business. We export the T-Shirts to overseas market and the country that we are in , requires us to breakdown each commodity type we are exporting with the values of each commodity. We have been doing this manually, but we feel since all the data is already stored in access why not have access build up a list.
Here is a example
Query Name : Invoice_Filter which holds the current invoice that is open
REF | PRODUCT_NAME | UNIT PRICE | QTY | GRANDTOTAL
AA1 Cotton T-Shirt "Frog" 2.00 4 6
AA2 Poly T-Shirt "Apple" 1.50 6 9
AA3 Cotton Jersey "TV" 4.00 12 48
AA4 Cotton T-shirt "Dog" 2.50 50 125
AA5 Poly T-shirt "Cool" 18.00 8 144
GRAND TOTAL = 332
So thats all fine, what I would like is a query that spits out the following
COMODITY VALUE
COTTON T-SHIRT $131 (TOTAL)
POLY T-SHIRT $153 (TOTAL)
COTTON JERSEY $48 (322)
GRAND TOTAL 322
Now it seems simple as we need need to make queries to do this, and Im sure very little coding is actually required. I think in a query I specify a critera for productname LIKE *"POLY" AND LIKE *"T-SHIRT*" this should return a list from the table of only POLY T-SHIRT and their total cost. --- I would have to create a query for each commodity changing the criteria each time.. I dont mind doing this since there are only 20 commodities, which would result in 20 queries... -- I can do all that, but then how would i join them all together for a final query that brings all the data together to display as follows :
COTTON T-SHIRT $131 (TOTAL)
POLY T-SHIRT $153 (TOTAL)
COTTON JERSEY $48 (322)
GRAND TOTAL 322
I am sure some joins and GROUP BY and SUM(grandtotal) etc.. but not sure exactly.
Tks.
Start Free Trial
