Link to home
Start Free TrialLog in
Avatar of thao-nhi
thao-nhi

asked on

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
Avatar of Joseph Krausz
Joseph Krausz

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
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

@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.
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial