Sales report details

[primerov]

I have a daunting task to make a report for the sales per month subdivided per segment and per type.

The order should look like that:
This is an example:
January
segment1 segment2
total sales: 100 tons 80 tons 20 tons

total sales
Sales in Segment1 : 80 tons
In this figure i must list all the sales per TypeID in the table Types.


In order to help me i shall describe what i have done.
I have made 2 tables, Segments and Types, with segmentID and TypeID.
Then, in the table Customers i have added the lookup field TypeID to choose from the table TypeID. In the table TypeID i have a lookup field for the SegmentID in the table Segments.

I have made a query, with an example of SegmentID = 1 and Customers.TypeID = 1
Can you tell me am i on the right way?

Below is my query:

SELECT Format([InvoiceDate],"mmmm&quot AS MonthName, DatePart("m",[invoicedate]) AS MonthNumber, Sum([order details].liters) AS SumOfLiters
FROM Segments, Types, (customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)) INNER JOIN (products INNER JOIN [order details] ON (products.Productid = [order details].ProductID) AND (products.Productid = [order details].ProductID)) ON orders.orderid = [order details].OrderID
WHERE (((orders.paymentid)=True) AND ((customers.afid)=1) AND ((Year([invoicedate]))=2003) AND ((Segments.SegmentID)=1) AND ((customers.TypeID)=1))
GROUP BY Format([InvoiceDate],"mmmm&quot, DatePart("m",[invoicedate]);

With the above code i can get a nice report enumerating the totals for each month.
But how can i subdivide these figures ? Does anybody has a database of a similar task in order to learn how can i do this task ?

 

[kerplunkitt]

hi there - i think i might have done something similar, but i don't quite fully understand what you are trying to do.. It sounds like you may need to change this into a make table query and then run another query on the newly created table to get the sub figures - it's only a guess as i cannot see your tables! if you have tried this let me know as what you are trying to do does not sound too difficult..
Regards
Ian