CODEWITHSUNDEEP
databasems-access
Wouter
Wouter

Reputation: 173

Joining different queries into 1 query in MS Access

For the past few days I've been trying everything that I know and everything that I could find on the internet. But I can't seem to figure it out and this turns me banana's.

I have 3 different MS Access Queries.

QUERY 1

SELECT First([Master Data Results 2013 - 2016].[Winner]) AS Player, Count([Master Data Results 2013 - 2016].[Winner]) AS Won
FROM [Master Data Results 2013 - 2016]
GROUP BY [Master Data Results 2013 - 2016].[Winner]
HAVING (((Count([Master Data Results 2013 - 2016].[Winner]))>1));

This query counts every tennis match won by player for a periode of 3 years

QUERY 2

SELECT First([Master Data Results 2013 - 2016].[Loser]) AS Player, Count([Master Data Results 2013 - 2016].[Loser]) AS Lost
FROM [Master Data Results 2013 - 2016]
GROUP BY [Master Data Results 2013 - 2016].[Loser]
HAVING (((Count([Master Data Results 2013 - 2016].[Loser]))>1));

This query counte everyt tennis match lost by player for a periode of 3 years

QUERY 3

INSERT INTO Master ( Lost )
SELECT Winner.*, Loser.Lost
FROM Loser INNER JOIN Winner ON Loser.Player = Winner.Player;

I have created a "Master" table. In this table I want to put the combined results of QUERY1 and QUERY 2.

The Master table looks like this.

enter image description here

So I want al the players that are in QUERY 1 in the Players column in the Master table. The numbers counted as a result of QUERY 1 in the Won column in the Master table and the numbers counted as a result of QUERY 2 in the Lost column in the Master table.

When I run each query seperatly than it works,but I want to put all this into 1 query for ease of use.

Upvotes: 0

Views: 32

Answers (1)

Sergey S.
Sergey S.

Reputation: 6336

You can use UNION query for combining results of Query 1 and 2, column [Lost] in query 1 = 0, column [Won] in query 2 = 0 and then aggregate this query, using Sum on [Lost] and [Won]. Something like this:

INSERT INTO Master (Player, Won, Lost)
SELECT Player, Sum(Won), Sum(Lost) FROM (
    SELECT First([Master Data Results 2013 - 2016].[Winner]) AS Player,
    Count([Master Data Results 2013 - 2016].[Winner]) AS Won,
    0 AS Lost
    FROM [Master Data Results 2013 - 2016]
    GROUP BY [Master Data Results 2013 - 2016].[Winner]
    HAVING (((Count([Master Data Results 2013 - 2016].[Winner])) > 1))

    UNION

    SELECT First([Master Data Results 2013 - 2016].[Loser]) AS Player,
    0 as Won,
    Count([Master Data Results 2013 - 2016].[Loser]) AS Lost
    FROM [Master Data Results 2013 - 2016]
    GROUP BY [Master Data Results 2013 - 2016].[Loser]
    HAVING (((Count([Master Data Results 2013 - 2016].[Loser])) > 1))
)
GROUP BY Player

Upvotes: 1

Related Questions