User queries for past week and month sums, averages, etc

[wickrama]

I want to make a query that can calculate averages and counts, etc. for the previous week and the previous month. Ideally, the user could enter in a date range for the query to run. However, I want this to be in user-facing. In other words, I want them to be able to do all this easily without having to modify the query. Is this possible? Thanks.

[JSR]

Use parameters. In the query design, for the field [date_field] on the criteria line, type >=[enter start date], and in another column [date_field]<=[enter end date]. When the user runs the query, Access will ask for "enter start date" and "enter end date" and that query will return rows with date between the two.

[wickrama]

Thank you, that worked as you said. Now how do I design the query so that instead of returning rows with the date between the two entered, it returns a count of the number of entries between those dates?

[CJ_London]

just change the query to an aggregate (group by) query and count the PK. Click on the Totals option on the ribbon in the query design window

[wickrama]

I did this, and I get an error message which says the expression is typed incorrectly or is too complex. The only change I made was adding the totals option and changing it to "Count"

[CJ_London]

show the sql that was generated

[wickrama]

SELECT Count(Nutrition.Date_of_admission) AS CountOfDate_of_admission
FROM Nutrition
HAVING (((Count(Nutrition.Date_of_admission))>=[enter start date] And (Count(Nutrition.Date_of_admission))<=[enter end date]));

[CJ_London]

you are trying to compare a count with a date. try

SELECT Count(Nutrition.Date_of_admission) AS CountOfDate_of_admission
 FROM Nutrition
WHERE Nutrition.Date_of_admission>=[enter start date] And Nutrition.Date_of_admission<=[enter end date]

Just copy and paste the code into a new query

Note: please get used to using the code tags when posting code

[wickrama]

Worked. thank you!!