Combining Two Aggregation Queries (1 Viewer)

robsmith

New member
Local time
Today, 15:45
Joined
Feb 17, 2020
Messages
23
Hi guys,

I am trying to combine two queries aggregation queries in Access but I am struggling with the syntax.

I want the final query to return the following fields: venue, League, batsmanId, ballsFacedByGrLg and ballsFacedbyLg.

Can anyone point me in the right direction?

Thanks

SQL:
SELECT Matches.venue, Matches.League, ScorecardBatting.batsmanId, SUM(ballsFaced) AS ballsFacedByGrLg
    FROM Matches INNER JOIN ScorecardBatting ON Matches.matchId = ScorecardBatting.matchId
    GROUP BY Matches.venue, Matches.League, ScorecardBatting.batsmanId;

SQL:
SELECT Matches.League, ScorecardBatting.batsmanId, SUM(ballsFaced) AS ballsFacedByLg
    FROM Matches INNER JOIN
         ScorecardBatting
         ON Matches.matchId = ScorecardBatting.matchId
    GROUP BY Matches.League, ScorecardBatting.batsmanId;
 

namliam

The Mailman - AWF VIP
Local time
Today, 16:45
Joined
Aug 11, 2003
Messages
11,691
Simply create new query, adding the two above stored queries, joining them and selecting the data you want?
 

robsmith

New member
Local time
Today, 15:45
Joined
Feb 17, 2020
Messages
23
Thanks Namliam. I was thinking there may be a way to combine them into one to reduce the number of queries but I'll do that instead,
 

arnelgp

error reading drive A:
Local time
Today, 23:45
Joined
May 7, 2009
Messages
11,130
you basically have same query there, only the venue is added to the first.
 

robsmith

New member
Local time
Today, 15:45
Joined
Feb 17, 2020
Messages
23
Hi arnelgp, yes I wanted to sum ballsFaced based on venue, batsmanId and League then sum ballsFaced just on batsmanId and League. I want to then divide the first sum by the second sum and this was the only way I could think of doing it.
 

namliam

The Mailman - AWF VIP
Local time
Today, 16:45
Joined
Aug 11, 2003
Messages
11,691
You could do something if the venue were split into columns instead of rows...
Code:
SELECT Matches.League, ScorecardBatting.batsmanId
     , SUM(ballsFaced) AS ballsFacedByLg
, SUM(iif(venue = "X"; ballsFaced;0)) AS ballsFacedByLgVenueX
, SUM(iif(venue = "Y"; ballsFaced;0)) AS ballsFacedByLgVenueY
, SUM(iif(venue = "Z"; ballsFaced;0)) AS ballsFacedByLgVenueZ
    FROM Matches INNER JOIN
         ScorecardBatting
         ON Matches.matchId = ScorecardBatting.matchId
    GROUP BY Matches.League, ScorecardBatting.batsmanId;

Also if you want to force one query from above you can do so... but it requires some "higher level" query work, reduces number of queries but increases overhead and maintenance.
Right now you should have something like:
Code:
Select 
From  query1
left join query2 on ....

Now "simply" get the sql (sql1) from query1 and insert in this query, rince repeat for query2 (sql2)
Code:
Select 
From  (sql1) query1
left join (sql2) query2 on ....
Now you have 1 query instead of 3, but you have created a maintenance problem and headache for the future!
Having a proper naming convention for your tables (which leaves something to be desired here) and queries...
i.e.
qryName
qryName_detail1
qryName_detail2
you can easily see in your object window that these queries belong together somehow, this also increases your debugging capabilities as the objects exist as seperate entities in your db. Also these seperate objects can be ab- or re-used in other parts and be sure to return the same values.
 

robsmith

New member
Local time
Today, 15:45
Joined
Feb 17, 2020
Messages
23
Thanks Namliam, I see your point. It's my first database and the names aren't great. Will access automatically update query names used in other queries if I change them? Thanks again.
 

Users who are viewing this thread

Top Bottom