Help on a writing a dynamic SQL Query

Question

I am attempting to write an access query that will be dynamic - in the sense that I don't want a date to be entered to summarize the data (if possible).

I have a sales table that covers 2 years. I need to create a rolling 1 month (this was easy), rolling 3 months, rolling 6 months, rolling 9 months and rolling 12 months.

For simplicity sake, the table has the following information:

Posting Date Item number Sales

I need to summarize by item number the sales for the rolling period(s).

Example, using 7/31/2023 as the date, all of July sales would be 1 month .... July/June/May would be 3 months, July/June/May/Apr/Mar/Apr would be 6 months.

Is there a way to do this? I have gone through various iterations with no luck. I was using datediff- which worked, but I couldn't determine how to summarize the data.

Thank you

Answer 1

Try something like this:

PARAMETERS Forms!NameOfForm!txtStartDate DATETIME;

SELECT ItemNumber,

SUM(TransactionAmount * ABS(TransactionDate BETWEEN DATEADD("m",-1,DATESERIAL(YEAR(Forms!NameOfForm!txtStartDate), MONTH(Forms!NameOfForm!txtStartDate),1)) AND DATESERIAL(YEAR(Forms!NameOfForm!txtStartDate), MONTH(Forms!NameOfForm!txtStartDate)+1,0))) AS [1 Month],

SUM(TransactionAmount * ABS(TransactionDate BETWEEN DATEADD("m",-3,DATESERIAL(YEAR(Forms!NameOfForm!txtStartDate), MONTH(Forms!NameOfForm!txtStartDate),1)) AND DATESERIAL(YEAR(Forms!NameOfForm!txtStartDate), MONTH(Forms!NameOfForm!txtStartDate)+1,0))) AS [3 Months],

SUM(TransactionAmount * ABS(TransactionDate BETWEEN DATEADD("m",-6,DATESERIAL(YEAR(Forms!NameOfForm!txtStartDate), MONTH(Forms!NameOfForm!txtStartDate),1)) AND DATESERIAL(YEAR(Forms!NameOfForm!txtStartDate), MONTH(Forms!NameOfForm!txtStartDate)+1,0))) AS [6 Months],

SUM(TransactionAmount * ABS(TransactionDate BETWEEN DATEADD("m",-9,DATESERIAL(YEAR(Forms!NameOfForm!txtStartDate), MONTH(Forms!NameOfForm!txtStartDate),1)) AND DATESERIAL(YEAR(Forms!NameOfForm!txtStartDate), MONTH(Forms!NameOfForm!txtStartDate)+1,0))) AS [9 Months],

SUM(TransactionAmount * ABS(TransactionDate BETWEEN DATEADD("m",-12,DATESERIAL(YEAR(Forms!NameOfForm!txtStartDate), MONTH(Forms!NameOfForm!txtStartDate),1)) AND DATESERIAL(YEAR(Forms!NameOfForm!txtStartDate), MONTH(Forms!NameOfForm!txtStartDate)+1,0))) AS [12 Months]

FROM Transactions

GROUP BY ItemNumber;

where txtStartDate is a text box control containing a date in the final month of each date range, and NameOfForm is the name of an open form containing the text box.

Answer 2

using 7/31/2023 as the date, all of July sales would be 1 month .... July/June/May would be 3 months, July/June/May/Apr/Mar/Apr would be 6 months.

I can not visualize what you need.

Can you post some data in table ?

And show.your expected result as well.

Answer 3

Thank you. I will try this. I should have added that I don't want a report, but rather am going to extract (or link Excel) haven't decided yet. This information is part of a bigger exercise.

Answer 4

Let's say you have frmReportCriteria with txtReportStartDate and txtReportEndDate, and also txtNumberOfMonths.

You enter the end date, and the number of months.

In txtNumberOfMonths you write in AfterUpdateEvent: txtReportStartDate = dateadd("m", -txtNumberOfMonths, txtReportEndDate)

That gets you the start date.

Now you create one query that "looks back" on this form for the start and end date:

select ....

where PostingDate between Forms!frmReportCriteria!txtStartDate and Forms!frmReportCriteria!txtEndDate

This query is the starting point for your report.