duplicate record id in append query - how to get around this?

[action]

I have a query that looks at sports teams that are scheduled to play today and members that are attached to these teams so I can print team lists. The Calendar has away and home teams in each record so I need to run two seperate queries one for away and one for home teams.(I have tried to create a single one but it is just to complex either for me or access, i'm not sure which)

Two queries are a make table and an append query. It all works well until I introduce a gameID field from the orginal calendar. It appears that the make table query sets the gameid as the keyfield or autonumber so that the append query rejects the write because the duplicates.

Note: the make table may place upto 10 member records in the table's main id using the gameid which is the same (cause they are in the same team) but when the append run it rejects the same id's that are already present.

Advice welcomed.

 

[Fuga]

Are you sure you need to have tables for the team lists? It sounds to me like you should use only queries.

Fuga.

 

[action]

I see what your saying, the tables are temp so yes I could just use another query. Will try.

 

[action]

no that doesn't help the main problem

I have converted this into queries instead of temp tables (which works fine) and then tried to merge the two results via another query but come up against the major problem.

Below is the SQL the "home team" query runs, the away team is simply the same except that the link is to the "away teams". I can not see how to then merge the two together so I can print a single report. Note: The dutyreport is the calendar.



SELECT "GD" & dutyreport.ID & "X" AS [GDX Code], DutyReport.DateTime, "T" & teams.ID & "X" AS [TX Code], Teams.Name, Max("M" & Members.ID & "X") AS MXCode, Members.Surname, Members.FirstName, "M" & Teams.CaptainID & "X" AS MXCaptian, Members.PhoneHome, Members.PhoneBusiness, Members.Fax, Members.Address1, Members.Address2, Members.Address3, Members.Insurance, Members.DateOfBirth, Members.email, Members.MobilePhone
FROM ((Team_Member INNER JOIN (AdminCard INNER JOIN Members ON AdminCard.MemberID = Members.ID) ON Team_Member.MemberID = Members.ID) INNER JOIN Teams ON Team_Member.TeamID = Teams.ID) INNER JOIN DutyReport ON Teams.ID = DutyReport.HomeTeamID
GROUP BY "GD" & dutyreport.ID & "X", DutyReport.DateTime, "T" & teams.ID & "X", Teams.Name, Members.Surname, Members.FirstName, "M" & Teams.CaptainID & "X", Members.PhoneHome, Members.PhoneBusiness, Members.Fax, Members.Address1, Members.Address2, Members.Address3, Members.Insurance, Members.DateOfBirth, Members.email, Members.MobilePhone
HAVING (((DutyReport.DateTime) Between Date() And Date()+1));