Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using Access 2003 (2000 Format)

    I am working on implementing an Archive process which will run in November or December of the year - to archive all member attendance data older than October of 2 years ago DateSerial(Year()-2,10,1)

    What I want to do is pull the data from the current records and the archived records and combine them.

    I have 2 Select queries. Following are their SQLs
    [codebox]SELECT qryAllDatesAndMembers.MemberID, qryAllDatesAndMembers.MonthYear,
    qryAllDatesAndMembers.Required, Nz([MeetingsAttended],0) AS MeetingsAttendedCount,
    nz([Makeups],0) AS MakeupCount, IIf(nz([Makeups],0)>4,4,nz([MakeUps],0)) AS MakeUpCredit,
    IIf([MakeUpCredit]+nz([MeetingsAttended])>=[Required],"Credit","NoCredit") AS CreditedMonth,
    qryAllDatesAndMembers.FullName, qryAllDatesAndMembers.LastName,
    qryAllDatesAndMembers.PreferredName, LastPerfectAttendance
    FROM (qryAllDatesAndMembers LEFT JOIN qryCountMakeUps ON
    (qryAllDatesAndMembers.MonthYear=qryCountMakeUps.M eetingMonth) AND
    (qryAllDatesAndMembers.MemberID=qryCountMakeUps.Me mberID)) LEFT JOIN
    qryCountMeetingsAttended ON (qryAllDatesAndMembers.MonthYear=
    qryCountMeetingsAttended.MeetingMonth) AND (qryAllDatesAndMembers.MemberID=
    qryCountMeetingsAttended.MemberID)
    ORDER BY qryAllDatesAndMembers.MemberID, qryAllDatesAndMembers.MonthYear DESC ,
    qryAllDatesAndMembers.LastName, qryAllDatesAndMembers.PreferredName;[/codebox]

    [codebox]SELECT qryAllDatesAndMembers.MemberID, qryAllDatesAndMembers.MonthYear,
    qryAllDatesAndMembers.Required, Nz([MeetingsAttended],0) AS MeetingsAttendedCount,
    nz([Makeups],0) AS MakeupCount, IIf(nz([Makeups],0)>4,4,nz([MakeUps],0)) AS MakeUpCredit,
    IIf([MakeUpCredit]+nz([MeetingsAttended])>=[Required],"Credit","NoCredit") AS CreditedMonth,
    qryAllDatesAndMembers.FullName, qryAllDatesAndMembers.LastName,
    qryAllDatesAndMembers.PreferredName, qryAllDatesAndMembers.LastPerfectAttendance
    FROM qryCountMeetingsAttendedARCHIVE RIGHT JOIN (qryCountMakeUpsARCHIVE RIGHT JOIN
    qryAllDatesAndMembers ON (qryCountMakeUpsARCHIVE.MeetingMonth =
    qryAllDatesAndMembers.MonthYear) AND (qryCountMakeUpsARCHIVE.MemberID =
    qryAllDatesAndMembers.MemberID)) ON (qryCountMeetingsAttendedARCHIVE.MeetingMonth =
    qryAllDatesAndMembers.MonthYear) AND (qryCountMeetingsAttendedARCHIVE.MemberID =
    qryAllDatesAndMembers.MemberID)
    ORDER BY qryAllDatesAndMembers.MemberID, qryAllDatesAndMembers.MonthYear DESC ,
    qryAllDatesAndMembers.LastName, qryAllDatesAndMembers.PreferredName;[/codebox]

    A straight Union query of these 2 Select queries gives the correct results but it's a confusing picture on a report because there are 2 lines...one line representing the data from October 2 years ago to the present, the second line representing the data prior to October of 2 years ago.

    I have tried various methods to combine this data into one line but without success.

    I would appreciate any suggestions as to an approach.

    Thanks!

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What would you like the end result to look like?

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    If you want a single record, you can simply join the two queries in a third query using the MemberID field. You may want to use a right join to the archived query, as you may have records in the current data that don't exist in the archived data. However that is going to result in a very long record if you want to put comparable fields next to each other. Not sure if that will help the report view of the data, as you have a number of fields.
    Wendell

  4. #4
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='784703' date='15-Jul-2009 13:54']What would you like the end result to look like?[/quote]
    There are 84 members. I would like a total of Regular Meetings and a total of Makeup Activities for each month...all on the same row in the report.

    Tom

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='784704' date='15-Jul-2009 13:55']If you want a single record, you can simply join the two queries in a third query using the MemberID field. You may want to use a right join to the archived query, as you may have records in the current data that don't exist in the archived data. However that is going to result in a very long record if you want to put comparable fields next to each other. Not sure if that will help the report view of the data, as you have a number of fields.[/quote]
    Wendell
    Yes, I have tried that, but I get 2 rows for each record - one representing the current data and one representing the archived data.
    See my reply to Hans.

    Tom

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    As Wendell wrote, you should create a third query based on the two queries that you mention. Join the two queries on MemberID and add the fields that you need from each to the query grid.
    This is *not* a union query - it is a regular select query that can be created and edited in design view.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wendell and Hans
    Turns out that I have to join not only the MemberID field but the MonthYear field as well to make it work.

    Tom

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    OK, glad you were able to solve it.

Posting Permissions

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