Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Complex Addition Query

Discussion in 'Information Technology' started by Wes Henry, Nov 4, 2005.

  1. Wes Henry

    Wes Henry
    Expand Collapse
    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:

    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
     
  2. Loading...

    Similar Threads Forum Date
    God, Truth, Love, Goodness, Beauty & Human complexes Blogs Oct 22, 2015
    India Pro-Khalistan slogans raised in Golden Temple complex Breaking News Jun 6, 2012
    India Final Deadline Set for Khalsa Complex Breaking News Jul 16, 2011
    Heritage Khalsa heritage complex to be dedicated to nation in Aug 2011 History of Sikhism Dec 2, 2010
    India India cannot help but be complex Breaking News Oct 2, 2010

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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>)


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "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
     
  4. Wes Henry

    Wes Henry
    Expand Collapse
    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.



    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>)
    >
    >
     
  5. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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
    http://I.Am/DougSteele
    (no e-mails, please!)



    "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