UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Combine Two Tables And Sum, Access 2016    
 
   
jukos
post Nov 20 2019, 09:55 AM
Post#1



Posts: 40
Joined: 31-May 16



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!


Attached File(s)
Attached File  combine_sum_tables.JPG ( 41K )Number of downloads: 0
 
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 10:02 AM
Post#2


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
jukos
post Nov 20 2019, 10:39 AM
Post#3



Posts: 40
Joined: 31-May 16



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.
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 10:50 AM
Post#4


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
jukos
post Nov 20 2019, 12:17 PM
Post#5



Posts: 40
Joined: 31-May 16



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.
Attached File(s)
Attached File  qbe_combine_sum.JPG ( 26.23K )Number of downloads: 0
 
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 12:21 PM
Post#6


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
jukos
post Nov 20 2019, 12:39 PM
Post#7



Posts: 40
Joined: 31-May 16



Thats a big help! This is slick, thank you so much!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th December 2019 - 06:21 AM