StrugglingWithAccess
New member
- Local time
- Today, 04:09
- Joined
- Apr 29, 2018
- Messages
- 3
have created a query that counts the number of times a pupil has attended a lesson. It tells me the total of how many each pupil has had but when I add a date range to the query it ungroups the data again.
I have added the lesson name field into the query and used count on the 2nd one like I have seen on this site but as soon as I add the date criteria to only show the last month at the end the count disappears and it all goes back to being uncounted.
This works code works and tells me how many times each lesson has been attended:
I want to now keep it grouped as above but only show from last year, when I add this it loses all the lesson counts :banghead:
how can I keep the count of each lesson but include a year please
Thanks in advance
I have added the lesson name field into the query and used count on the 2nd one like I have seen on this site but as soon as I add the date criteria to only show the last month at the end the count disappears and it all goes back to being uncounted.
This works code works and tells me how many times each lesson has been attended:
Code:
SELECT TBL_Lesson.LessonName, Count(TBL_Lesson.LessonName) AS CountOfLessonName, TBL_Lesson.LessonLevel
FROM TBL_Class INNER JOIN (TBL_Lesson INNER JOIN TBL_RegistrationofClass ON TBL_Lesson.[ID] = TBL_RegistrationofClass.[LessonID]) ON TBL_Class.ClassID = TBL_RegistrationofClass.ClassID
GROUP BY TBL_Lesson.LessonName, TBL_Lesson.LessonLevel
HAVING (((TBL_Lesson.LessonLevel)>4));
I want to now keep it grouped as above but only show from last year, when I add this it loses all the lesson counts :banghead:
Code:
SELECT TBL_Lesson.LessonName, Count(TBL_Lesson.LessonName) AS CountOfLessonName, TBL_Lesson.LessonLevel, TBL_Class.Date
FROM TBL_Class INNER JOIN (TBL_Lesson INNER JOIN TBL_RegistrationofClass ON TBL_Lesson.[ID] = TBL_RegistrationofClass.[LessonID]) ON TBL_Class.ClassID = TBL_RegistrationofClass.ClassID
GROUP BY TBL_Lesson.LessonName, TBL_Lesson.LessonLevel, TBL_Class.Date
HAVING (((TBL_Lesson.LessonLevel)>4) AND ((TBL_Class.Date) Like ("*2018")));
how can I keep the count of each lesson but include a year please
Thanks in advance