Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Florida, USA
    Thanked 0 Times in 0 Posts

    query for end of year report (access xp)

    Need to have a count of the persons receiving any service during the year--BUT only count them once-- first tried to just get all the persons to show in a query-- this isn't working but I am not sure what I have done wrong-- I can see I am not getting OR it is retreiving those who have all of the services rather than those who have any of them. Any suggestions will be appreciated.

    SELECT Aid.[Date Received], attendance.Date, [2003ChristmasData].EntryDate, [Person (new)].[Personal ID]
    FROM (([Person (new)] INNER JOIN Aid ON [Person (new)].[Personal ID] = Aid.[Personal ID]) INNER JOIN attendance ON [Person (new)].[Personal ID] = attendance.[Personal ID]) INNER JOIN 2003ChristmasData ON [Person (new)].[Personal ID] = [2003ChristmasData].PersonalID
    WHERE (((Aid.[Date Received]) Between #1/1/2003# And #12/31/2003#)) OR (((attendance.Date) Between #1/1/2003# And #12/31/2003#)) OR ((([2003ChristmasData].EntryDate) Between #1/1/2003# And #12/31/2003#));

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Schenectady, New York, USA
    Thanked 0 Times in 0 Posts

    Re: query for end of year report (access xp)


    This is one solution that should work.

    Create 3 querys

    Query 1 -

    SELECT [2003ChristmasData].EntryDate, [Person (new)].[Personal Id]
    FROM [Person (new)] INNER JOIN 2003ChristmasData ON [Person (new)].[Personal Id] = [2003ChristmasData].PersonalId
    WHERE ((([2003ChristmasData].EntryDate) Between #1/1/2003# And #12/31/2003#))
    SELECT Attendance.Date, [Person (new)].[Personal Id]
    FROM [Person (new)] INNER JOIN Attendance ON [Person (new)].[Personal Id] = Attendance.[Personal Id]
    WHERE (((Attendance.Date) Between #1/1/2003# And #12/31/2003#))
    SELECT Aid.[Date Received] AS [Date], [Person (new)].[Personal Id]
    FROM [Person (new)] INNER JOIN Aid ON [Person (new)].[Personal Id] = Aid.[Personal Id]
    WHERE (((Aid.[Date Received]) Between #1/1/2003# And #12/31/2003#));

    Query 2 -
    SELECT Query1.[Personal Id]
    FROM Query1
    GROUP BY Query1.[Personal Id];

    Query 3 -
    SELECT Count(Query2.[Personal Id]) AS [CountOfPersonal Id]
    FROM Query2;

    Hope this helps.
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts