Access 2016 Using Top in a Union Query

Question

I am trying to create a query that will output the top 6 results based on a union of two other queries. The two queries are as follows:

SELECT GameData.GameID,
    GameData.DivisionID,
    GameData.SeasonID,
    GameData.HomeTeamID AS TeamID,
    GameData.GameDate,
    IIf([FullTimeResult] = 'H', 1, 0) AS W,
    IIf([FullTimeResult] = 'D', 1, 0) AS D,
    IIf([FullTimeResult] = 'A', 1, 0) AS L,
    IIf([HalfTimeResult] = 'H', 1, 0) AS WHT,
    IIf([HalfTimeResult] = 'D', 1, 0) AS DHT,
    IIf([HalfTimeResult] = 'A', 1, 0) AS LHT,
    GameData.FullTimeHomeGoals AS GS,
    GameData.FullTimeAwayGoals AS GC,
    IIf([FullTimeResult] = 'H', 3, IIf([FullTimeResult] = 'D', 1, 0)) AS P,
    GameData.HalfTimeHomeGoals AS GSHT,
    GameData.HalfTimeAwayGoals AS GCHT,
    GameData.HomeShots AS Sh,
    GameData.AwayShots AS ShA,
    GameData.HomeShotsOnTarget AS ShOnT,
    GameData.AwayShotsOnTarget AS ShAOnT,
    GameData.HomeFouls AS FM,
    GameData.AwayFouls AS FA,
    GameData.HomeCorners AS C,
    GameData.AwayCorners AS CA,
    GameData.HomeYellowCards AS YC,
    GameData.AwayYellowCards AS YCA,
    GameData.HomeRedCards AS RC,
    GameData.AwayRedCards AS RCA
FROM TeamsDivSea
INNER JOIN GameData ON TeamsDivSea.TeamID = GameData.HomeTeamID
WHERE (
        (
            (GameData.GameID) IN (
                SELECT TOP 3 GameID
                FROM GameData AS Dupe
                WHERE Dupe.HomeTeamID = GameData.HomeTeamID
                ORDER BY Dupe.GameDate DESC,
                    Dupe.GameID DESC
                )
            )
        );

SELECT GameData.GameID,
    GameData.DivisionID,
    GameData.SeasonID,
    GameData.AwayTeamID AS TeamID,
    GameData.GameDate,
    IIf([FullTimeResult] = 'A', 1, 0) AS W,
    IIf([FullTimeResult] = 'D', 1, 0) AS D,
    IIf([FullTimeResult] = 'H', 1, 0) AS L,
    IIf([HalfTimeResult] = 'A', 1, 0) AS WHT,
    IIf([HalfTimeResult] = 'D', 1, 0) AS DHT,
    IIf([HalfTimeResult] = 'H', 1, 0) AS LHT,
    GameData.FullTimeAwayGoals AS GS,
    GameData.FullTimeHomeGoals AS GC,
    IIf([FullTimeResult] = 'A', 3, IIf([FullTimeResult] = 'D', 1, 0)) AS P,
    GameData.HalfTimeAwayGoals AS GSHT,
    GameData.HalfTimeHomeGoals AS GCHT,
    GameData.AwayShots AS Sh,
    GameData.HomeShots AS ShA,
    GameData.AwayShotsOnTarget AS ShOnT,
    GameData.HomeShotsOnTarget AS ShAOnT,
    GameData.AwayFouls AS FM,
    GameData.HomeFouls AS FA,
    GameData.AwayCorners AS C,
    GameData.HomeCorners AS CA,
    GameData.AwayYellowCards AS YC,
    GameData.HomeYellowCards AS YCA,
    GameData.AwayRedCards AS RC,
    GameData.HomeRedCards AS RCA
FROM TeamsDivSea
INNER JOIN GameData ON TeamsDivSea.TeamID = GameData.AwayTeamID
WHERE (
        (
            (GameData.GameID) IN (
                SELECT TOP 3 GameID
                FROM GameData AS Dupe
                WHERE Dupe.AwayTeamID = GameData.AwayTeamID
                ORDER BY Dupe.GameDate DESC,
                    Dupe.GameID DESC
                )
            )
        );

Is it possible to create a union query only using one SELECT TOP statement so only the top 6 are output from the joined results?

Many thanks

Answer 1

This should work - basically UNION your two queries and select the top 6.

SELECT TOP 6 GameID, DivisionID, SeasonID, TeamID, GameDate, W,D,L,WHT,DHT,LHT,GS,GC,P,GSHT,GCHT,Sh,shA, shOnT, ShAOnt, FM,FA,C,CA,YC,YCA,RC,RCA
FROM    (
    SELECT GameData.GameID,
        GameData.DivisionID,
        GameData.SeasonID,
        GameData.HomeTeamID AS TeamID,
        GameData.GameDate,
        IIf([FullTimeResult] = 'H', 1, 0) AS W,
        IIf([FullTimeResult] = 'D', 1, 0) AS D,
        IIf([FullTimeResult] = 'A', 1, 0) AS L,
        IIf([HalfTimeResult] = 'H', 1, 0) AS WHT,
        IIf([HalfTimeResult] = 'D', 1, 0) AS DHT,
        IIf([HalfTimeResult] = 'A', 1, 0) AS LHT,
        GameData.FullTimeHomeGoals AS GS,
        GameData.FullTimeAwayGoals AS GC,
        IIf([FullTimeResult] = 'H', 3, IIf([FullTimeResult] = 'D', 1, 0)) AS P,
        GameData.HalfTimeHomeGoals AS GSHT,
        GameData.HalfTimeAwayGoals AS GCHT,
        GameData.HomeShots AS Sh,
        GameData.AwayShots AS ShA,
        GameData.HomeShotsOnTarget AS ShOnT,
        GameData.AwayShotsOnTarget AS ShAOnT,
        GameData.HomeFouls AS FM,
        GameData.AwayFouls AS FA,
        GameData.HomeCorners AS C,
        GameData.AwayCorners AS CA,
        GameData.HomeYellowCards AS YC,
        GameData.AwayYellowCards AS YCA,
        GameData.HomeRedCards AS RC,
        GameData.AwayRedCards AS RCA
    FROM TeamsDivSea
    INNER JOIN GameData ON TeamsDivSea.TeamID = GameData.HomeTeamID
    WHERE (
            (
                (GameData.GameID) IN (
                    SELECT TOP 3 GameID
                    FROM GameData AS Dupe
                    WHERE Dupe.HomeTeamID = GameData.HomeTeamID
                    ORDER BY Dupe.GameDate DESC,
                        Dupe.GameID DESC
                    )
                )
            )

    UNION ALL SELECT GameData.GameID,
        GameData.DivisionID,
        GameData.SeasonID,
        GameData.AwayTeamID AS TeamID,
        GameData.GameDate,
        IIf([FullTimeResult] = 'A', 1, 0),
        IIf([FullTimeResult] = 'D', 1, 0),
        IIf([FullTimeResult] = 'H', 1, 0),
        IIf([HalfTimeResult] = 'A', 1, 0),
        IIf([HalfTimeResult] = 'D', 1, 0),
        IIf([HalfTimeResult] = 'H', 1, 0),
        GameData.FullTimeAwayGoals,
        GameData.FullTimeHomeGoals,
        IIf([FullTimeResult] = 'A', 3, IIf([FullTimeResult] = 'D', 1, 0)),
        GameData.HalfTimeAwayGoals,
        GameData.HalfTimeHomeGoals,
        GameData.AwayShots,
        GameData.HomeShots,
        GameData.AwayShotsOnTarget,
        GameData.HomeShotsOnTarget,
        GameData.AwayFouls,
        GameData.HomeFouls,
        GameData.AwayCorners,
        GameData.HomeCorners,
        GameData.AwayYellowCards,
        GameData.HomeYellowCards,
        GameData.AwayRedCards,
        GameData.HomeRedCards
    FROM TeamsDivSea
    INNER JOIN GameData ON TeamsDivSea.TeamID = GameData.AwayTeamID
    WHERE (
            (
                (GameData.GameID) IN (
                    SELECT TOP 3 GameID
                    FROM GameData AS Dupe
                    WHERE Dupe.AwayTeamID = GameData.AwayTeamID
                    ORDER BY Dupe.GameDate DESC,
                        Dupe.GameID DESC
                    )
                )
            )
        )