Sum and Group in a Query

[Dan]

I am not sure how to do this any information would be helpful.

I am wanting to create a query that will Sum values if it falls beween
dates. Example:

Table 100 rows of
Column1: Date
Column2: Amount

Query:
Date is 1 year or less Date is 1 to 5 years Date is 5 to 10
years
Sum Amounts Sum Amounts Sum Amounts


Thanks for any info you can provide.

 

[Dan S.]

I've had similar situations come up in the past. The way
I've solved them is to create a checkbox (or checkboxes,
for multiple selections) or a blank field (call
it "Date_Range"). Then, use update queries to update those
fields to the correct value based on the criteria you set.
So, if you use checkboxes, create an update query that
updates the "LessThanOneYear" checkbox to a Yes if the
Date is less than a year old. Do the same with the other
date ranges to update your other checkboxes to Yes. If you
prefer to just create the blank "Date_Range" field (just a
blank text field in the table), just create an update
query that updates that field to "Less Than One Year Old"
for those accounts; then do the same with the other ranges
that you have.

Once you've done this, just create a select query that
gives you the Sum of Accounts, sorted by either your
checkboxes or "Date_Range" field.


Hope this makes sense, and hope it helps.


-Dan.

-----Original Message-----
I am not sure how to do this any information would be helpful.

I am wanting to create a query that will Sum values if it falls beween
dates. Example:

Table 100 rows of
Column1: Date
Column2: Amount

Query:
Date is 1 year or less Date is 1 to 5 years Date is 5 to 10
years
Sum Amounts Sum

Amounts Sum Amounts

 

[John Spencer (MVP)]

One method among several. Try the UNTESTED SQL below.

SELECT
SUM(IIF(DateField Between
DateAdd("YYYY",-1,Date()) And Date(),Amount,0)) as OneOrLess,
SUM(IIF(DateField <
DateAdd("YYYY",-1,Date()) And
DateField >= DateAdd("YYYY",-5,Date()),Amount,0)) as OneToFive,
SUM(IIF(DateField <
DateAdd("YYYY",-5,Date()) And
DateField >= DateAdd("YYYY",-10,Date()),Amount,0)) as FiveToTen
FROM YourTable


You might also take a look at the Partition function, but since your date
periods are not equal that probably will not work for you.