Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Queries _ Combine Two Tables And Sum

Posted by: jukos Nov 20 2019, 09:55 AM

I am trying to take two tables with the same two fields. the first field is called bin, second num. I would like to query these tables such that the output is a field bin with all the rows in table 1 and 2 but shown as a unique value. The corresponding num field would show the sum of any matching fields in bin and the values for each unique bin entry. I am attaching a drawing to help explain this. I am now well versed in SQL, mostly use qbe so it it could be explained with that in mind that would be awesome! Thanks for your help!



 

Posted by: cheekybuddha Nov 20 2019, 10:02 AM

I really hope this is a temporary table setup!!!

CODE
SELECT
  Bin,
  SUM(Num) AS SumNum
FROM (
  SELECT
    Bin,
    Num
  FROM Table1
  UNION ALL
  SELECT
    Bin,
    Num
  FROM Table2
)
GROUP BY
  Bin
ORDER BY
  Bin
;


In the QBE, switch to SQL view and paste the SQL above.

Then you can switch to Design View and see how it displays it.

hth,

d

Posted by: jukos Nov 20 2019, 10:39 AM

Wow, thats a thing of beauty! Is there a way to make this manipulatable in qbe or is qbe just too basic to handle this? By manipulatable I mean being able to see the input tables in the qbe box.

Thanks so much for this.

Posted by: cheekybuddha Nov 20 2019, 10:50 AM

What happens when you try to return to Design View in the QBE?

Posted by: jukos Nov 20 2019, 12:17 PM

I get this. Its working great but I was hoping I could see the input tables to see how it works and can be modified in QBE.

 

Posted by: cheekybuddha Nov 20 2019, 12:21 PM

OK,

To see it in the QBE will be a 2 step process.

First, create a query (eg qryBinUnion)

CODE
  SELECT
    Bin,
    Num
  FROM Table1
  UNION ALL
  SELECT
    Bin,
    Num
  FROM Table2;


Then, in a new query (eg qryBinSums)
CODE
SELECT
  Bin,
  SUM(Num) AS SumNum
FROM qryBinUnion
GROUP BY
  Bin
ORDER BY
  Bin
;


Then you can switch each to Design View from SQL View and see how the QBE handles it.

(Sorry, I never use the QBE!!)

hth,

d

Posted by: jukos Nov 20 2019, 12:39 PM

Thats a big help! This is slick, thank you so much!