Totals queries with many conditions

[bobfin]

I have a table with a date field and a currency field. I need to get a count of all the records where the date falls within various ranges. I could make a totals query for each range and then use those queries as the source for a combined query. But is there a simpler way? There are a lot of ranges to cover. I also need to do the same type of thing with multiple ranges for the currency field. Those ranges are not uniform in size.

 

[Jon K]

You can create a lookup table to hold the start and end of each range. Then join the main table with this lookup table in your totals query using the operator Between ... And ...


A demo DB is attached. It contains an Invoice table, a DateRange table and a totals query to find the total invoice amount of each date range:

qryDateRanges:-
SELECT b.[StartDate], b.[EndDate], Count(*) AS NumberOfInvoices,
Sum(a.[Amount]) AS TotalAmount
FROM Invoice AS a, DateRange AS b
WHERE a.[InvoiceDate] Between b.[StartDate] And b.[EndDate]
GROUP BY b.[StartDate], b.[EndDate];


Hope it helps.