Access adding data from multiple tables

Question

I have multiple data tables and I want to add together some values.

Table1 
Item      units
xx        1
yy        2   
zz        3

Table2
Item      Units
xx        1
yy        2
zz        3

Table3
Item      Units
xx        1
yy        2
zz        3

What I need returned is basicly Totals

so

xx = 3
yy = 4
zz = 6

This has to be done with multiple tables. I am attempting to do this in Access and I have no idea how to start.

Edit:

How would I also get it to display an associated value?

For ex:

 Table1 
 Item       units       Associated Value
 xx         1           randomval1
 yy         2           randomval2

 Table2 
 Item       units       Associated Value
 xx         1           randomval1
 yy         2           randomval2

so then the resulting output should be

 xx randomval1 = 2

Is this do able?

Anyone can help with the modified question?

Boinger
– Boinger

2013-11-23 22:39:44 +00:00
Commented Nov 23, 2013 at 22:39 — Boinger
– Boinger, Commented Nov 23, 2013 at 22:39

Guilhem Hoffmann · Accepted Answer · 2013-11-20 09:57:25Z

2

you have to create a SQL Request which will join your data and make a sum afterwards

SELECT Item, SUM(Units)
FROM (
    SELECT Item, Units 
    FROM Table1
    UNION ALL
    SELECT Item, Units 
    FROM Table2
    UNION ALL
    SELECT Item, Units 
    FROM Table3
) 
GROUP BY Item

answered Nov 20, 2013 at 9:57

Guilhem Hoffmann

9625 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Filipe Silva Over a year ago

+1. I posted a similar answer, but yours is cleaner.

Boinger Over a year ago

Thanks for that. That worked great. One more question how do I only display the value of another column... Like Title per say.

Guilhem Hoffmann Over a year ago

Title ? is it part of your tables? explain more what your are expecting

Boinger Over a year ago

Yes there is a Title field as part of my tables. for ex Item Title Units And I just want to have title Displayed along with the Item

Collectives™ on Stack Overflow

Access adding data from multiple tables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related