Results 1 to 3 of 3
  1. #1
    eckert1961 is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    3

    Count Dates within Date Range

    Hello,



    I created the following query to count AttendanceDate.

    Code:
     
    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

  2. #2
    eckert1961 is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    3

    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:

    Code:
    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:

    Code:
    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

  3. #3
    eckert1961 is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    3

    Better Solution

    Here is a better solution.

    Code:
    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.

    Regards,
    Chris

Please reply to this thread with any new information or opinions.

Similar Threads

  1. how to convert week number to date range
    By qwang1115 in forum Access
    Replies: 1
    Last Post: 02-13-2009, 11:35 AM
  2. Search form with a date range
    By mantro174 in forum Forms
    Replies: 1
    Last Post: 02-11-2009, 10:45 PM
  3. Searching Dates
    By knightjp in forum Database Design
    Replies: 2
    Last Post: 08-16-2008, 11:39 PM
  4. Help with Searching Dates
    By rededdie in forum Access
    Replies: 1
    Last Post: 11-02-2007, 08:34 AM
  5. Retrieving Week of Year in a date range
    By Korvega in forum Queries
    Replies: 2
    Last Post: 05-27-2006, 06:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums