Query when date criteria added count is lost (1 Viewer)

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:

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
 

CJ_London

Super Moderator
Staff member
Joined
Feb 19, 2013
Messages
11,577
first a quick lesson about dates.

dates are formatted decimal numbers, the value before the dp represents the day, counting up from 31/12/1899 which is 'day zero'. Time is expressed in the decimal part as the time divided by 86400 (the number of seconds in a day). The format is whatever you want but defaults to your country based on windows settings.

in sql, the default format for dates is US style mm/dd/yyyy unless the date is unambiguous - 1/5/2018 is ambiguous because it could be 1st May or it could be 5th Jan - sql will default to 5th Jan. 31/12/2018 is not ambiguous because there are not 31 months in the year. Similarly a long date such as 1 May 2018 is also not ambiguous.

moving to your specific issue, you are grouping by a date - you need to group by a year - so you use the year function

year(TBL_Class.Date)

this will then solve your criteria problem which becomes

((year(TBL_Class.Date)) =2018))

note that Date is a reserved word (it is a function which means today) so you are strongly advised to change it to something more meaningful such as ClassDate or AttendanceDate. If you don't, expect to get problems down the line when Access won't know whether you are referring to the Date function or the Date field and will produce a error which may point you in the wrong direction. You can try surrounding it with square brackets - usually works, but not always.

Finally, construction of GROUP BY queries - your design is inefficient. You are requiring access to bring through all the records, and then apply a criteria. You should only use HAVING on summary values - e.g.

HAVING Count(TBL_Lesson.LessonName) >3

All other criteria should be WHERE - to do this, bring the field down a second time, change the group by to where and apply your criteria there instead.

Then Access will apply the criteria first and only count the records brought through.

Performance wise, it won't have a significant impact with small datasets but when you get to larger ones, it will.
 
Joined
Apr 29, 2018
Messages
3
Hi sorry I am still struggling. I now have this, I have renamed the date field as suggested
Code:
SELECT TBL_Lesson.LessonName, Count(TBL_Lesson.LessonName) AS [Times Completed], TBL_Lesson.LessonLevel, TBL_Class.ClassDate
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.ClassDate
HAVING (((TBL_Lesson.LessonLevel)>3) AND ((Year([TBL_Class].[ClassDate]))=2018));
But I still have lost my count and grouping, I have tried to follow your instruction here with no luck
HAVING Count(TBL_Lesson.LessonName) >3

All other criteria should be WHERE - to do this, bring the field down a second time, change the group by to where and apply your criteria there instead.
I am really trying honest :banghead:
 

CJ_London

Super Moderator
Staff member
Joined
Feb 19, 2013
Messages
11,577
you have only changed it in one place try

Code:
SELECT TBL_Lesson.LessonName, Count(TBL_Lesson.LessonName) AS [Times Completed], TBL_Lesson.LessonLevel, [COLOR=red]Year([TBL_Class].[ClassDate]) AS ClassYear 
 
[/COLOR]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
 
WHERE TBL_Lesson.LessonLevel>3 AND [COLOR=red]Year([TBL_Class].[ClassDate])=2018
[/COLOR]
GROUP BY TBL_Lesson.LessonName, TBL_Lesson.LessonLevel, [COLOR=red]Year([TBL_Class].[ClassDate])
[/COLOR]
I've also corrected for a more efficient query
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom