Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    22

    Unanswered: Join data of 2 tables

    Hi,

    There are 2 tables Tab1 & Tab2 with the below data:
    Code:
    Tab1:
    Name	Cost
    A1	5
    B2	1
    A1	8
    C3	5
    A1	7
    A1	8
    A1	5
    B2	3
    B2	3
    C3	1
    
    Tab2:
    Name	Cost
    A1	15
    B2	11
    A1	18
    C3	15
    A1	17
    A1	18
    A1	15
    B2	13
    B2	13
    C3	11

    I need to get Sum of Cost from both tables for a Name, i.e. the output should be as below:
    Code:
    Name	SumCost
    A1	116
    B2	44
    C3	32
    For A1 = 116:
    "Sum of Cost from tab1 - 33" + "Sum of Cost from tab1 - 83"

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I might design 2 summing queries to sum the data in each table (after I rename "Name" to a non-reserved fieldname!!).

    Ideally, I would make the summing queries append queries to append the data to a temp type table (after I first delete the values in the temp table). Then I'd have another query open that sums the values in the temp table. This would be done via vba code (such as in the OnClick event of a button) and the code might look like this (utilizing the queries I design)...

    ie.
    docmd.openquery "DeleteTempTableData"
    docmd.openquery "AppendTab1SummedToTempTable"
    docmd.openquery "AppendTab2SummedToTempTable"
    docmd.openquery "SummedTempTableData"
    Last edited by pkstormy; 02-13-10 at 22:14.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2010
    Posts
    22
    Hi,

    It would be helpful if you could provide me more detail on obtaining this.

    Thanks.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT Name, SUM(Cost) AS SumCost
    FROM
             (SELECT Name, Cost
              FROM Tab1
              UNION ALL
              SELECT Name, Cost
              FROM Tab2) AS bofadem
    GROUP BY Name
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2010
    Posts
    22
    Thanks a lot!!!

    I thought it would be complex... That's excellent.

    Thanks, once again!

Posting Permissions

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