Complex Addition Query

[Wes Henry]

Hi,

I have a query that I am trying to run that will add the sums of
different records in multiple columns. I'm sure that this doesn't make
sense so here is what I am trying to do. Let's say that it's like this:

Team 1 Team 2 Team 1 Points Team 2 Points
Arizona Denver 31 27
Arizona California 4 15
Dallas Arizona 13 18
New York Chicago 23 19

So, what I am trying to do is for every record where, for example,
Arizona is listed in either the [Team 1] or [Team 2] columns that it
will take it's respective score [Team 1 Points] or [Team 2 Points] and
add it up to make one total: 54 in this example. I know that I can do
one query that adds up the totals of one column [Team 1] which would
equal to 35. If I did this twice and then had a third query which added
the first two queries together then I could get the ultimate result of
what I am looking for (Hope this makes sense!)

I am hoping that there is an expression that I can use to only have one
query get the ultimate result that I am looking for. Any help would be
much appreciated.

Thanks,
Wes Henry

 

[Douglas J. Steele]

Create a UNION query that gets rid of the Team 1/Team 2 split:

SELECT [Team 1] AS Team, [Team 1 Points] AS TeamPoints
FROM MyTable
UNION
SELECT [Team 2] AS Team, [Team 2 Points] AS TeamPoints
FROM MyTable

Save that query as, say, qryUnion

Create a second query that gives you the sum per team:

SELECT Team, Sum(TeamPoints) AS TotalPoints
FROM qryUnion
GROUP BY Team

In Access 2000 and newer, you can actually get away with only one query:

SELECT Team, Sum(TeamPoints) AS TotalPoints
FROM
(
SELECT [Team 1] AS Team, [Team 1 Points] AS TeamPoints
FROM MyTable
UNION
SELECT [Team 2] AS Team, [Team 2 Points] AS TeamPoints
FROM MyTable
) AS Subselect
GROUP BY Team

(Of course, given the numbers you provided, it's going to say Arizona has 53
points, not 54! <g>)

 

[Wes Henry]

Douglas,

The second group of code that you gave me worked great. Thanks for the
help. Now since I haven't ever done SQL coding before this, can I just
keep adding that same set of lines for each additional set of columns
that I also want calculated in the same query, like rbi's or passes or
etc? If I can then would I just add the code right below the lines that
I just added and just change up the variable names like team, and
teampoints and totalpoints? Thanks again for the help.

 

[Douglas J. Steele]

If I understand your question correctly (i.e.: you've got two sets of rbi or
two sets of passes or the like), then yes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

The second group of code that you gave me worked great. Thanks for the
help. Now since I haven't ever done SQL coding before this, can I just
keep adding that same set of lines for each additional set of columns that
I also want calculated in the same query, like rbi's or passes or etc? If
I can then would I just add the code right below the lines that I just
added and just change up the variable names like team, and teampoints and
totalpoints? Thanks again for the help.

Create a UNION query that gets rid of the Team 1/Team 2 split:

SELECT [Team 1] AS Team, [Team 1 Points] AS TeamPoints
FROM MyTable
UNION
SELECT [Team 2] AS Team, [Team 2 Points] AS TeamPoints
FROM MyTable

Save that query as, say, qryUnion

Create a second query that gives you the sum per team:

SELECT Team, Sum(TeamPoints) AS TotalPoints
FROM qryUnion
GROUP BY Team

In Access 2000 and newer, you can actually get away with only one query:

SELECT Team, Sum(TeamPoints) AS TotalPoints
FROM
(
SELECT [Team 1] AS Team, [Team 1 Points] AS TeamPoints
FROM MyTable
UNION
SELECT [Team 2] AS Team, [Team 2 Points] AS TeamPoints
FROM MyTable
) AS Subselect
GROUP BY Team

(Of course, given the numbers you provided, it's going to say Arizona has
53 points, not 54! <g>)