How to add values between 2 tables in Access 2010

I'd like to know how to add values from 2 tables in the same database where linked fields match.

Table A            
Id      Name      qty
2304      ABC      12
2305      BCD      2


Table B            
Id      Name      qty
2304      ABC      3
2305      BCD      22


End result
Table A            
Id      Name      qty
2304      ABC      15
2305      BCD      24

Thanks
thao-nhiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joseph KrauszCEOCommented:
For this to work you can just create a union query with a summery field, make sure you are giving the same name, alias for all the fields
Select sum(sumField) as summery, other fields.... From tableA group by (all fields included besides the summery field)
Union all
Select sum(sumField) as summery, other fields.... From tableB  group by (all fields included besides the summery field)
Make sure the field sort,count,names are the same.

Regards

Joseph
0
Russell FoxDatabase DeveloperCommented:
Here's the SQL per your table structure above and @yidel718's comment. Create a query and use this format in SQL View (sorry, no idea how to do it via the Access builders):
SELECT TABLE1.id, TABLE1.name, (TABLE1.qty + TABLE2.qty + TABLE3.qty) AS GrandTotal
FROM TABLE1
	JOIN TABLE2
		ON TABLE2.id = TABLE1.id
		AND TABLE2.name = TABLE1.name
	JOIN TABLE3
		ON TABLE3.id = TABLE1.id
		AND TABLE3.name = TABLE1.name

Open in new window

1
Joseph KrauszCEOCommented:
@Russel. It's interesting how things can be solved in several ways.... I'm really wondering what would be the appropriate way to choose.
1. A union query (as I proposed) which didn't require any joins as the grouping will do the aggregation
2. Or a joined query with grouping

Regards.

Joseph.
1
PatHartmanCommented:
There is a difference between the two methods.  
1. Inner join - only tables with matching rows will be returned (this is what was requested)
1a. Full outer join (implemented in Access by unioning a left join query and a right join query) - this will produce the same results as #2 but is more complicated.
2. Union ALL - the "all" is important since by default the union will eliminate "duplicate" rows so they will be eliminated by the time you run the aggregation query against the union query.  The union will include ALL rows from both tables whether or not there is a key match - that is the difference.

PS - Union queries do not require that columns in the separate queries have identical column names.  What they DO require is that columns have the same definition and be selected in the SAME order.  So if the first column in qry1 is CustName, then the first column in ALL the queries must be a text data type and to be logically coherent, contain customer name data.  If you made a mistake and selected Addr1 instead of customer name in the second select query, the union query would still run since both are text data types but the resultset wouldn't make sense.  Some of the rows would have a name in column 1 and others would have an address.  If the second column is OrderAmt, then the second column in all the queries must be the same data type (probably currency) and contain  an order amount.  Think of unions as queries that concatenate one set of data at the end of another set.  So in order to stack set b at the end of set a and have the result make sense, the columns all need to align.  The column names of the first query will be used as the column names of the resultset.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.