sales totals in a report from multiple records

[Andy_]

I have a relational database containing reseller details, sales records, stock levels etc.

I'm quite happy with the way it's working, however, I have run into some problems as I try to create some meaningful reports to run.

I have a 'sales details' database containing all of my sales information. I want a sales report that can show me the total sales for all my products, by month or year or region or whatever.

The issue I have is as follows. When I find all of my sales, say for a particular month, I get something that looks like this as I have different resellers making sales of the same product in the same month.

Item ----- Quantity

ProductA ----- 1

ProductA ----- 1

ProductA ----- 3

ProductB ----- 1

ProductB ----- 7

PrdocutC ----- 1

ProductE ----- 1

ProductE ----- 4

Can anybody suggest a useful way to generate a report which would total these for me? I do have a separate file for stock items I could run some script against but I haven't been able to think of a way to make it work.

Any help would be appreciated.

[Damocles]

Any chance for a screenshot of your report in Layout Mode?

[LaRetta]

Andy, you need a Columnar List Report with Grouped Data. You can put your Product in a leading part (Organize by Category) and then sub-total all Product A records, then Product B, etc. And since your Product will be displayed in a leading part, you can then delete it from the body after the report has been made.

Be sure to sort this report by Product or it won't display properly.

LaRetta

[Ender]

Use Sub-Summary parts:

You might have layout parts for Sub-Summary by Product, Sub-Summary by Month, and Sub-Summary by Year. In each part put a field representing the break field and a summary field (like Count of Record ID or Total of Quantity).

Then sort by Year, Month, and Product to get the parts to show up in preview mode.

[Andy_]

Thanks guys, that was really helpful.

One more question though if I may...

Is there any clever way to sort by subtotal of quantity so that the highest selling products appear at the top of the report?

Because of the grouped data it seems to have to sort by product name.