Date Range

[eckert1961]

Count Dates within Date Range Problem.

Hello,

I have the following query that I need some assistance with.

SELECT [LastName] & ", " & [FirstName] AS [Student Name], Count(tblAttendance.AttendanceDate) AS Attendance, GetDays([Enter Start Date],[Enter End Date],"46") AS [Total Classes], Members.Active
FROM Members INNER JOIN tblAttendance ON Members.MemberID = tblAttendance.MemberID
GROUP BY [LastName] & ", " & [FirstName], GetDays([Enter Start Date],[Enter End Date],"46"), Members.Active
HAVING (((Members.Active)=Yes));

What I want to do is to also have a count of the tblAttendance.AttendanceDate between the Start and End date range.

Thanks in advance.

Chris

 

[DCrake]

Not quite sure what GetDays() does, this must be a UDF. But here is a link to a FAQ Paassing public variable

This may help you.

David

 

[eckert1961]

Hello,

GetDays is a Public Function that counts the number of Wednesday's and Friday's within a date range. I also need this query to count the number of AttendanceDate that a student attends during this same period. In other words the dates that I enter for the GetDays function also needs to be passed to AttendanceDate to restrict the count to that same range.

Count(tblAttendance.AttendanceDate) AS Attendance, GetDays([Enter Start Date],[Enter End Date],"46") AS [Total Classes], Members.Active
FROM Members INNER JOIN tblAttendance ON Members.MemberID = tblAttendance.MemberID

I've tried to enter the following criteria into the above portion of my query.

Between [Enter Start Date] And [Enter End Date]

When I run the query I am prompted for the dates but after entering them I get the following error.

The expression is typed incorrectly, or it is too complex to be evaluated.

Any ideas on what I need to change to make this work?

Regards,
Chris

 

[eckert1961]

I created the following query to count the AttendanceDate.

SELECT DISTINCTROW Members.FirstName, Members.LastName, Count(*) AS [Count Of tblAttendance], Members.Active
FROM Members INNER JOIN tblAttendance ON Members.MemberID = tblAttendance.MemberID
GROUP BY Members.FirstName, Members.LastName, Members.Active
HAVING (((Members.Active)=Yes));

How would I go about restricting the count to a specific date range?

Chris

 

[eckert1961]

Re: Date Range - SOLUTION

Success!!!!!

After many hours of trial and error I finally have a query that gives me the desired results. What I had to do was create 2 querys.

Query1:

SELECT [LastName] & ", " & [FirstName] AS [Student Name], tblAttendance.AttendanceDate AS Attendance, GetDays([Enter Start Date],[Enter End Date],"46") AS [Total Classes], Members.Active
FROM Members INNER JOIN tblAttendance ON Members.MemberID = tblAttendance.MemberID
GROUP BY [LastName] & ", " & [FirstName], tblAttendance.AttendanceDate, GetDays([Enter Start Date],[Enter End Date],"46"), Members.Active
HAVING (((tblAttendance.AttendanceDate) Between [Enter Start Date] And [Enter End Date]) AND ((Members.Active)=Yes));

Query 2:

SELECT DISTINCTROW qryAttendanceByRange.[Student Name], Count(qryAttendanceByRange.Attendance) AS [Count Of qryAttendanceByRange], qryAttendanceByRange.[Total Classes], qryAttendanceByRange.Active
FROM qryAttendanceByRange
GROUP BY qryAttendanceByRange.[Student Name], qryAttendanceByRange.[Total Classes], qryAttendanceByRange.Active
HAVING (((qryAttendanceByRange.Active)=Yes));

This works great, however, if anyone knows of an alternate / better solution please reply.

Regards,
Chris

 

[Brianwarnock]

As I don't have a DB to test this it is untested, but have you tried

SELECT [LastName] & ", " & [FirstName] AS [Student Name], Count(tblAttendance.AttendanceDate) AS Attendance, GetDays([Enter Start Date],[Enter End Date],"46") AS [Total Classes], Members.Active
FROM Members INNER JOIN tblAttendance ON Members.MemberID = tblAttendance.MemberID
Where ((tblAttendance.AttendanceDate) Between [Enter Start Date] And [Enter End Date]) AND ((Members.Active)=Yes)
GROUP BY [LastName] & ", " & [FirstName], tblAttendance.AttendanceDate

Brian

 

[raskew]

Brian, David -

That latest post sure looks like it'll work.

The GetDays() function comes from here (2nd post):
http://www.access-programmers.co.uk/forums/showthread.php?t=167149&highlight=getdays(

Best wishes -- Bob

 

[Brianwarnock]

Good to have your backing Bob, sure hope he lets me know the result.

Brian

 

[eckert1961]

Thanks for the replies. I tried Brian's suggestion and I get the following error when I run the query.

You tried to execute a query that does not include the specified expression 'Active' as part of an aggregate function.

Any thoughts?

Regards,
Chris

 

[Brianwarnock]

It was missed out of the Group by, this is the problem with air code simple errors can creep in.

Brian

 

[eckert1961]

Thanks Brian,

I added Members.Active to GROUP BY and now the query runs. However, I don't get a total COUNT of Attendance per Member. What I get is the name displayed multiple times with a Count of Attendance showing 1 for each line.

To clarify, if I run the query from 01-Sep-08 to 31-Oct-08, one member attended 11 classes out of 18 Total Classes. When I run the query I want to see the following.

Student Name Attendance Total Classes Active
test 11 18 Yes

What I get when I run the query is.

Student Name Attendance Total Classes Active
test 1 18 Yes
test 1 18 Yes
test 1 18 Yes
test 1 18 Yes
test 1 18 Yes
test 1 18 Yes

This is what I was getting until I created my second query. I couldn't figure out how to get the desired results in 1 query.

Any thoughts?

Chris

 

[Brianwarnock]

This would be simpler if I had some data, I'm just trying to amend your first SQL and making silly errors
Remove tblAttendance.AttendanceDate from the group by we don't need to Group on this we are counting it.
Why did we need to select and group on active as its always "Yes" ?

 

[eckert1961]

Hi Brian,

I implemented your recommendations and this is what I ended up with.

SELECT [LastName] & ", " & [FirstName] AS [Student Name], Count(tblAttendance.AttendanceDate) AS Attendance, GetDays([Enter Start Date],[Enter End Date],"46") AS [Total Classes] 
FROM Members INNER JOIN tblAttendance ON Members.MemberID = tblAttendance.MemberID
WHERE ((tblAttendance.AttendanceDate) Between [Enter Start Date] And [Enter End Date]) AND ((Members.Active)=Yes)
GROUP BY [LastName] & ", " & [FirstName],Members.Active

It works great and all in one query. I really appreciate your help on this.

Thanks and regards,
Chris

 

[Brianwarnock]

Glad we got there.
Air code is always a bit dodgy as we can all make silly mistakes that are easily picked up in testing, which is why supplying test data is always worthwhile.

Brian