MS Access - count function doesn't work for events which occur on different dates

Question

I have two tables, one with types of event and another with dates that those events occurred.

I want to run a count query which finds out how many of each event happened in a particular timeframe.

The query works fine with no dates, and groups the workouts and counts them. However, with the date field added to the query it splits each occurrence of the event into a new line and gives it a count of 1.

Any ideas on how I can solve this? Would a dcount work?

Thanks,

Ed.

Answer 1

I expect you only need to uncheck the Show for the date field as well as set its Totals to "Where"

Answer 2

Hi Edward,

I'm not clear whether you want to show a count of all events within a single period or a count of events by period. If the former, then a query like:

SELECT Event, Count(EventID) as Counter

FROM table1 INNER on table1.EventID = table2.EventID

WHERE EventDate BETWEEN #date1# AND #date2#;

Should give you want you want.

If you need for different period you are going to have to assign each date to a period of time.

Answer 3

You need to change the Totals to Where so your query might look like:

SELECT tblClass.Workout_ID, tblWorkout.Workout_name, tblWorkout.Workout_difficulty, Count(tblWorkout.Workout_ID) AS CountOfWorkout_ID

FROM tblWorkout INNER JOIN tblClass ON tblWorkout.Workout_ID = tblClass.Workout_ID

WHERE tblWorkout.Workout_difficulty>3 AND tblClass.Class_date Between #1/1/2017# And #12/31/2017#

GROUP BY tblClass.Workout_ID, tblWorkout.Workout_name, tblWorkout.Workout_difficulty;

I'm not sure if you should also remove the Worku_ID from the GROUP BY.

Answer 4

Thanks Duane. I'd tried unchecking the Show for the date field but that didn't work.

Is there a tweak I could do on the SQL?

SELECT DISTINCTROW tblClass.Workout_ID, tblWorkout.Workout_name, tblWorkout.Workout_difficulty, Count(tblWorkout.Workout_ID) AS CountOfWorkout_ID

FROM tblWorkout INNER JOIN tblClass ON tblWorkout.Workout_ID = tblClass.Workout_ID

GROUP BY tblClass.Workout_ID, tblWorkout.Workout_name, tblWorkout.Workout_difficulty, tblClass.Class_date

HAVING (((tblWorkout.Workout_difficulty)>3) AND ((tblClass.Class_date) Between #1/1/2017# And #12/31/2017#));

Any help greatly appreciated!

Answer 5

Hi Edward,

Can you provide some information about your significant tables and fields as well as the SQL statements you have tried?

Thanks