Combining two query results

[barry_kellett99]

I am really struggling with this, but I need to combine these two queries.
They count the number of goals scored and conceded in a fixture between two teams to give the games played, games won lost and drawn and Goals for and against and points tally.

However, I can only get it to work for the Hometeam.
The second query gives the results for the Away team, but I need to combine the two together to give one league table with all the results and points etc combined.

Hope you can help

This displays the home team results and calculates everything correctly.

SELECT tbl_Fixtures.TEAMID, TBL_TEAMS.NAME, 
COUNT(*) AS GAMESPLAYED, SUM(IIf((HOMEGOALS>AWAYGOALS),1,0)) AS GAMEWON,
SUM(IIf((HOMEGOALS=AWAYGOALS),1,0)) AS GAMEDRAWN,
SUM(IIf((HOMEGOALS<AWAYGOALS),1,0)) AS GAMELOST,
SUM(HOMEGOALS) AS GOALSFOR, SUM(AWAYGOALS) AS GOALAGAINST,
SUM((IIf((HOMEGOALS>AWAYGOALS),3,0))+(IIf((HOMEGOALS=AWAYGOALS),1,0))) AS POINTS
FROM tbl_Fixtures, TBL_TEAMS
WHERE tbl_Fixtures.TEAMID=TBL_TEAMS.TEAMID
GROUP BY tbl_Fixtures.TeamID, TBL_TEAMS.name;

This displays the Away team results and calculates everything correctly.

SELECT tbl_Fixtures.opponentID, TBL_TEAMS.NAME, 
COUNT(*) AS GAMESPLAYED,
SUM(IIf((AWAYGOALS>HOMEGOALS),1,0)) AS GAMEWON,
SUM(IIf((AWAYGOALS=HOMEGOALS),1,0)) AS GAMEDRAWN,
SUM(IIf((AWAYGOALS<HOMEGOALS),1,0)) AS GAMELOST,
SUM(AWAYGOALS) AS GOALSFOR, SUM(HOMEGOALS) AS GOALAGAINST,
SUM((IIf((AWAYGOALS>HOMEGOALS),3,0))+(IIf((AWAYGOALS=HOMEGOALS),1,0))) AS POINTS
FROM tbl_Fixtures, TBL_TEAMS
WHERE tbl_Fixtures.opponentID=TBL_TEAMS.TEAMID
GROUP BY tbl_Fixtures.opponentID, TBL_TEAMS.name;

Now I just have to figure out how to combine the two, which is proving more difficult than I thought it would be.
My SQL isn't the best

 

[Dennisk]

to combine queries, use a UNION query

 

[barry_kellett99]

to combine queries, use a UNION query

Thanks Dennisk - I've been trying that, but it gives me a row with the teams home results and then below that a row with the same teams away results.

I need to combine them into one single row with the sum of the home and away records

 

[Dennisk]

Barry,

If you can link the TeamID to the OpponentID then you can use the two recordsources in one query. Hence if a TeamID can be an OppenentID then you may be able to get the result you require.