DSum vs Multiple Queries

[michael c]

I have a list of product orders that shows Customer
Number, Order Date, Order Amount, and Product. I'm
currently creating separate queries for Grouping order by
Customer Number and Date for:

Current Month To Date
Current Quarter To Date
Current Year To Date
Prior Month To Date
Prior Quarter To Date
Prior Year To Date

These are ultimately linked together and displayed on a
report so that I can see what a customer has order for
different time periods. This makes for a messy database
window, especially when I start grouping further by
product. I tried using DSum on the report but it seems
pretty slow (my recordset is about 2M lines). Any
suggestions? THANKS!!!

 

[Matt]

Hey Michael,

It sounds like you could possibly achieve the same result using only
ONE query, if that query is a CROSSTAB type.

What this does, basically, is to "swing" rows of data (ie your date
ranges) into column headings, and then give you numbers (ie totals,
NOT detail-data) in each of those ranges.

The best place to start if you're unfamiliar is the Access on-line
help, followed by using the wizard to try creating a couple of queries
for you - after the first couple yuou'll get the idea of what you can
do, and the effects of the changes you can make...

HTH.