sum total between two dates in a query

[wire_jp]

Hello,

I would like to show the sum total of new respondents in 1 day ago (i.e. yesterday and today) and the sum total of new respondents in the last 30 days in a query.
I tried this SQL code in a query called "qryNoOfResponsesByDay" but it is not showing the corrrect results: -

Code: Select all

SELECT tblFormResponses.Timestamp AS [New Respondents 1 day ago], tblFormResponses.Timestamp AS [Respondents in the last 30 days], Count(tblFormResponses.[BARP member number]) AS [CountOfBARP member number]
FROM tblFormResponses
GROUP BY tblFormResponses.Timestamp
HAVING (((tblFormResponses.Timestamp)<Date()-1) AND ((tblFormResponses.Timestamp)<Date()-30));

The OneDrive web link is attached with the sample database:

https://1drv.ms/f/s!AtOdPG-IN8CYgcV-YYl ... Q?e=nH6Iu2

Thanks,

wire_jp

[xps35]

I cannot open tblFormResponses as it is linked to an Excel file that is not on the OneDrive folder.

[HansV]

The query makes no sense. You need three queries:
- A query to count new respondents in the last 2 days.
- A query to count new respondents in the last 30 days.
- A query to combine the two previous queries.

See BCTSurveyFormDatabase - Demo v.3.accdb

You'll have to relink the Excel table as usual.

[wire_jp]

Hi Hans,

Thank you for the help.

@ xps35; the tblVolunteerQuestionnaire Excel spreadsheet is the tblFormResponses excel spreadsheet