combining data from 2 queries

[THWatson]

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

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.MeetingMonth) AND (qryAllDatesAndMembers.MemberID=qryCountMakeUps.MemberID)) LEFT JOIN qryCountMeetingsAttended ON (qryAllDatesAndMembers.MonthYear=qryCountMeetingsAttended.MeetingMonth) AND (qryAllDatesAndMembers.MemberID=qryCountMeetingsAttended.MemberID)
ORDER BY qryAllDatesAndMembers.MemberID, qryAllDatesAndMembers.MonthYear DESC , qryAllDatesAndMembers.LastName, qryAllDatesAndMembers.PreferredName;

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;

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

 

[PHV]

So, you want an aggregate query based on the union query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

Yes.

Tom

 

[PHV]

So, just do it !
Where is the problem ?

 

[THWatson]

PHV
Turns out that doing an aggregate on the Union query doesn't work. It produces the duplicate rows.

What works is creating a 3rd Select query and joining not only the MemberID fields but the MonthYear fields as well. Then it works.

Tom