Grouping by ranges of dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way in the QBE grid to group by ranges of dates and perform an
aggregate function on each group? I've seen solutions to do this by month or
by quarter by formatting the dates appropriately, but what if the date ranges
that you want to group are more arbitrary than that? Say, for example, one
group should be between 12/1/2000 and 11/15/2001, then the next should be
11/16/2001 thru 12/3/2002, etc. Is this doable? Thanks!
 
Ann,

The best way to do this, arbitrary groupings, is to create a new table, with
ID, StartDate and EndDate columns. Add the date groupings to this table.
Then when you create your query, add this new table to the mix (no joins).
Your query might look something like:

SELECT G.ID,
First(G.StartDate) AS StartDate,
First(G.EndDate) AS EndDate,
Sum(S.Sales) AS CumSales
FROM tbl_Sales AS S, tbl_Group AS G
WHERE DateValue([SalesDate])>=[StartDate] And
DateValue([SalesDate])<=[EndDate]
GROUP BY G.ID;

This example uses a sample sales table which includes a SalesDate column
that contains Date and time data. Because of this, I used the DateValue( )
function to strip out the time portion of the date for my date comparison.

HTH
Dale
 
Back
Top