UNION qry dont work as expected.

[rncarterjm]

I am new to MS Access, I am working on creating a soccer DB but I am stuck on league standing query. The query in question is designed to return the summarized points, scores, needed to complete the league standing but it is returning the TeamID # instead of the Team Names. Please advise.

[June7]

Have to include the teams table in query by joining on the PK/FK team ID fields. This will make related team info such as the name available.

[pbaldy]

Hard to say without knowing your table structure. Sounds like you need to join another table into the UNION query to get the names.

[rncarterjm]

here are the qry

--qryScoresHome
SELECT Table1.Home AS Team, Points([HomeGoal],[AwayGoal]) AS Point, Table1.HomeGoal AS Scored, Table1.AwayGoal AS Against, [Scored]-[Against] AS Agg
FROM Table1;

--qryScoresAway
---qryStandings
SELECT qryTotal.Team, Sum(qryTotal.Point) AS SumOfPoint, Sum(qryTotal.Scored) AS SumOfScored, Sum(qryTotal.Against) AS SumOfAgainst, Sum(qryTotal.Agg) AS SumOfAgg
FROM qryTotal
GROUP BY qryTotal.Team
ORDER BY Sum(qryTotal.Point) DESC , Sum(qryTotal.Agg) DESC , Sum(qryTotal.Scored) DESC;

---qryTotal
---UNION qry
select * from qryScoresAway
UNION select * from qryScoresHome;

[orange]

Just wondering if you had seen this data model
http://www.databasedev.co.uk/footbal...ata_model.html

[rncarterjm]

database attached...

[rncarterjm]

.mdb format attached

[June7]

Either join tblTeams into each of the SELECT queries or join it to the UNION.

SELECT test.*, tblTeams.[Team Name]
FROM tblTeams INNER JOIN test ON tblTeams.TeamID = test.Team;

[rncarterjm]

June7, your suggestion didn't work

[June7]

It does for me. What happens for you?

[rncarterjm]

It does for me. What happens for you?

I got it to work, i had a syntax error.