Query for Sum of Purchases in the Last 30 Days

[FullMoonMadness]

Hello,

After much scouring of the web, I am unable to find a solution to the following: I want to create a query for the sum of purchases for the last 30 days grouped by item.

This will be a rolling 30 days, so will likely involve the CURRENT_DATE function.

The fields are:

• Items (the names of the products) - group by this field
• Charge (the cost of the item) - sum this field
• Purchase Date (date of purchase) - I’m not wanting to display this in the results, but need to set the criterion for last 30 days

It’s the criterion for the Purchase Date that I’m struggling with. Thanks in advance!

[gkick]

Hi,

it depends on what database you are using
so for example if you use HSQL2.4 onwards you can utilize this function

DATEDIFF(‘dd’, “yourdatefield”, CURRENT_DATE) < 30

[FullMoonMadness]

That worked, however I was mistaken that the DateDiff info needed to be placed in the Criterion field when using Design View. I had been trying to place that info in Criterion under the “Purchase Date” column.

DATEDIFF( ‘dd’, “Transactions”.“Purchase Date”, CURRENT_DATE ) gets it’s own column and goes into the “Field” field
< 30 goes into the Criterion

Thanks for your help!

[Ratslinger]

@FullMoonMadness,

Instead of marking a question as closed, you should mark the answer as accepted. Please tick the (upper left area of answer). It helps others to know there was an accepted answer.

Not closing an answer also allows others to perhaps offer other insights.