In the continuing saga of my current work, I have one table with CustomerNumber, Company, SalesOrderDate, and GrossSales as the important fields. What they want me to do is prepare a couple of reports, one taking into account Company(furniture line) and one that does not, that sums the GrossSales for each quarter from 2001 to 2004 and displays it one one line for each Customer(Customer/Company) combination. This sounded like the perfect thing for a crosstab query to me, but when I set CustomerNumber( or CustomerNumber/Company) as rows, SalesOrderDate as Column, Sum of GrossSales as Value, it either says too many crosstab headers or, if I restrict the date range to smaller it gives me one column for each date, not for each quarter. Anyone have an idea how to get quarterly sums in an arrangement like this?
I've managed to accomplish this in very slow and brutal ways using dsums in a query, VB programing at the section detail level, and one very ugly looking query with an overall sum query outer joined to sum queries for each quarter, but they are all very slow and clunky. I was hoping for a better and less ugly and slow way to do it. Thank you all in advance for your time and replies
I have found, in the past, that a decent way to get in the door quickly with situations where I need to do some sort of grouping by date, I handle it like so:
I create a "Totals" query, and I add a field that takes the date and performs some sort of format to it, and group by it.
This query typically contains the Key (like Customer key in your case) of the table and the grouped date function. A subsequent select query can add the rest of the data.
For example, the expression "Quarter:Format([TheDate],"q") will return a 1, 2, 3 or 4, representing the quarter the date falls in.
So, if you Group by Customer, then Group by "Quarter", and sum "Sales", the rest can be handled by the report.
If you setup the report to have the detail be in columns, then each "Quarter" can land in it's own column and you can end up with a format that looks like a pivot table that will execute pretty fast.
I hope this helps. The first time I had to do this, I had to calculate demand, by month, with everything past due in one bucket. So, I had 1 totals query for Format([Date],"m") < Format(Now(),"m") and a second for the remainder by Format([Date],"m"). Doing it by quarter would work exactly the same.