Results 1 to 4 of 4
  1. #1
    suverman is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    15

    Consolidation codes from different table.

    Say I have table 1, Table 2 with common fields "Level_Code" and Amount.

    "Level_Code" can be 1,2,3

    Each "Level_Code" has amounts assigned to it.

    I'd like to query the database so that it returns

    1 XXXX
    2 XXXX


    3 XXXX


    That is, adds all the amount under the codes and display it.


    My attempt was the following statements.

    SELECT Level_Code, sum(Amount)
    FROM Table 1
    GROUP BY Level_Code
    UNION SELECT Level_Code, sum(Amount)
    FROM Table 2
    GROUP BY Level_Code


    The output is like this.

    1 XXX
    1 XXX
    2 XX
    2 XXX
    3 XX
    3 XXX

    It seems to show the table Level_Codes saperately. How can i query to make it add up Amount for each code and show only
    1 XXX
    2 XXX
    3 XXX

    Thank you

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Run an aggregate query on your second query.

    Code:
    Select Level_Code, sum(Amount)
    from Query2
    Group By Level_Code
    Alan

  3. #3
    suverman is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    15
    I assume one has to name the query something for using it in the query 2. Can I get an example how to go about it? Thank you.

  4. #4
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    My bad, the name that I used for your first query was query2. Where I used query2 in SQL statement, you should use the name of your first query.

    Alan

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Zip Codes
    By Laurie B. in forum Access
    Replies: 6
    Last Post: 02-25-2011, 02:38 PM
  2. Visual Mapping Using Zip Codes?
    By kalisti in forum Reports
    Replies: 1
    Last Post: 01-03-2011, 07:26 PM
  3. Finding subsequent codes
    By Rixxe in forum Queries
    Replies: 8
    Last Post: 09-15-2010, 02:44 AM
  4. Need help with code logic/consolidation
    By bg18461 in forum Programming
    Replies: 1
    Last Post: 03-31-2010, 04:19 PM
  5. Consolidation DB
    By dimitrz in forum Database Design
    Replies: 7
    Last Post: 03-23-2010, 12:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums