I have a table tbl_Fixtures made up of:

fixture_id
team1_id
team2_id
...
...
...

A team id can appear in either team1 or team2 (depends on how the algorithm to compute who plays who calculates it).

I want to know how many games a team plays, so I wrote 4 queries:

qryTeam1Games:
SELECT tbl_Fixtures.team1_id AS team_id, Count(tbl_Fixtures.fixture_id) AS games
FROM tbl_Fixtures
GROUP BY tbl_Fixtures.team1_id;

qryTeams2Games:
SELECT tbl_Fixtures.team2_id AS team_id, Count(tbl_Fixtures.fixture_id) AS games
FROM tbl_Fixtures
GROUP BY tbl_Fixtures.team1_id;

qryAllGames:
SELECT *
FROM qryTeam1Games
UNION
SELECT *
FROM qryTeam2games;


qryNumGamesByTeam:
SELECT qryAllGames.team_id, Sum(qryAllGames.games) AS SumOfgames
FROM qryAllGames
GROUP BY qryAllGames.team_id;


Unfortunately, if the same team_id and games combination appears in both qryTeams1Games and qryTeams2Games, it doesn't bring 2 records into qryAllGames and the summed number of games is only half of what it should be.

Is this a case where a union query shouldn't be used? Is that how it is supposed to work?

A work around isn't a problem but this did have me stumped for a bit.

UNION will by default eliminate duplicates. Try changing

UNION

to

UNION ALL

Excellent - cheers for your advice.

Pete.