Group by criteria (1 Viewer)

[russiver]

Not sure if the following is possible, but below is a simplified example of what I'm trying to achieve with a parameter query.

The source table for the query contains two fields:

Reading_Date (short date) and Use_value (integer)

The parameter query sums Use_value between two dates for various date ranges specified as 'or' criteria. SQL as follows:

SELECT Sum(Table1.Use_Value) AS SumOfUse_Value
FROM Table1
WHERE (((Table1.Reading_Date) Between #1/1/2013# And #1/5/2013#)) OR (((Table1.Reading_Date) Between #1/1/2014# And #1/5/2014#));

This produces a single sum total, but I'd like the query to give a total per criteria date range. In other words to group results by criteria. As date ranges may span year change, grouping by year is not possible.

Any ideas much appreciated.

Russ

 

[plog]

You would replace the value in your SELECT clause with 2 IIF statements that effectively move the criteria to the SELECT clause:

SUM(IIF((Reading_Date) Between #1/1/2013# And #1/5/2013#), Use_Value,0) AS 2013Value

 

[russiver]

Many thanks for the reply.

Slightly confused

Are you suggesting using a calculated field for each data range?

So

SELECT Sum(IIf([reading_Date] Between #01/01/2013# And #01/05/2013#,[Use_Value],0)) AS Expr1, Sum(IIf([reading_Date] Between #01/01/2014# And #01/05/2014#,[Use_Value],0)) AS Expr2
FROM Table1;

This works and produces a single row of data.

 

[plog]

Yup, that's the way to do it.

 

[Brianwarnock]

Plog is correct, this a pretty standard approach.