Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Posts
    145

    Unanswered: Union Query and Summation

    Hello,
    I have two tables that have prices in them. I want to Sum() the prices from both tables according to an ID. I can get all the prices I need in one column for an ID by using a UNION, but how do I Sum() that column?

    Thanks.

  2. #2
    Join Date
    Jan 2004
    Posts
    145
    Note:

    I'm pretty sury I can do this with two separate queries, but if I could do it in one that would be nice.
    Thanks again.

  3. #3
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91
    If u have:

    Table A
    ID
    Price

    Table B
    ID
    Price

    Create a query A_B, as following:

    ID
    TableA.Price
    TableB.Price
    Soma: TableA.Price + TableB.Price

    In fact, u wouldn´t have to put TableA.Price and Table.Price. But it´s easier to know wether results are corrrect.
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

  4. #4
    Join Date
    Jan 2004
    Posts
    145
    I did do it with two queries. I UNIONed the Prices and then used another query to sum the UNION query. I still would like to know if it can be done in one querey.
    Thanks.

  5. #5
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91
    But there is only one query, i.e., query A_B
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    SELECT SUM(ThisTable.Price + ThatTable.Price) AS TotPrice FROM THisTable INNER JOIN ThatTable ON ThisTable.ProductID=ThatTable.ProductID;

    Wouldn't work?

  7. #7
    Join Date
    Jan 2004
    Posts
    145
    Mix,
    I think I tried what you said. The problem was say I only have one price from table A and three from table B. I only want the sum of the four prices, but for each one in B I get one in A so I end up getting the price from A included two extra times in the sum.

  8. #8
    Join Date
    Jan 2004
    Posts
    145
    Mix,
    While I was writing my two query post you had submitted your first post so I didn't see it until after my post.

  9. #9
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91
    Gwgeller,

    Sorry, but why do u have price of the same product all over your database?
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

  10. #10
    Join Date
    Jan 2004
    Posts
    145
    Mix,
    I don't have duplicate product prices, here is exactly what I want to do. I have a registration price, which there is only one of. Then there are tour prices which a person can have multiple of. The registration options/prices and the tours options/prices are in different tables. I want to total all the prices: One registration price and possibly multiple tour prices. I've done it using two queries, but like I said I am wondering if it is possible in one.
    Thanks.

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by gwgeller
    Mix,
    I think I tried what you said. The problem was say I only have one price from table A and three from table B. I only want the sum of the four prices, but for each one in B I get one in A so I end up getting the price from A included two extra times in the sum.
    Use a subselect.

    SELECT ThisTable.ID, SUM(ThisTable.Price + (SELECT SUM(ThatTable.Price) WHERE ThatTable.ID = ThisTable.ID)) AS TotPrice FROM ThisTable ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •