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
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
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
@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. 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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