Complex Addition Query

ExpandCollapse

Guest

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:

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.

"Wes Henry" <wphenry@gmail.com> wrote in message
news:%23Et0oSJ4FHA.1864@TK2MSFTNGP12.phx.gbl...
> 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

ExpandCollapse

Guest

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.

Guest

"Wes Henry" <wphenry@gmail.com> wrote in message
news:uIZtv$U4FHA.3740@TK2MSFTNGP12.phx.gbl...
> 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 wrote:
>> 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>)
>>

Share This Page

About the Author: Rabinder Singh Bhamra was born in 1940 and raised in the city of Amritsar in Punjab, India into an industrial family. He grew up with an interest in the fine arts and finished his college education at Khalsa College, Amritsar with a Bachelor of the Arts. Later, the...

About Us

Our community has been around for many years and pride ourselves on offering unbiased, critical discussion among people of all different backgrounds. We are working every day to make sure our community is one of the best.

Like us on Facebook

Support SPN

The management works very hard to make sure the community is running the best software, best designs, and all the other bells and whistles. We'd really appreciate your support!