Summing Values from 2 Tables

[bryan0]

I’m a relatively new Access user and this is my first post on the forum. I’m familiar with basic SQL (I think) and I have done a lot of searching, reading and trial and error and cannot seem to get this to work…

I have 2 tables, each containing spend data. One is for 2011 and one for 2012. Some vendors are common in each year, and some are used in one year but not the other. I want to design a query that will show me 3 columns: Vendor Name, 2012 Spend and 2011 Spend. If the vendor was used in 2011 but not in 2012, I don’t care about it.



My tables are much more complex, but basically are structured like such:

2012_SPEND Table:

Vendor_Name	Spend_Amount
VendorA	5
VendorB	7
VendorC	10
VendorA	3
VendorC	12

2012_SPEND Table:

Vendor_Name	Spend_Amount
VendorA	16
VendorD	6
VendorC	12
VendorA	9

I want the query result to be:

Vendor_Name	2012 Spend	2011 Spend
VendorC	22	12
VendorA	8	25
VendorB	7	0

Any help would be tremendously appreciated!

[June7]

Why do you have two tables? Are the fields identical in both tables? You will create a new table for each year? Are you using forms for data entry/edit? A form for each year? Should be one table with a field for YearSpend then only one form for data entry/edit.

As is, options are:

1. UNION query which essentially gets the normalized structure tables should be then crosstab query
However, building stable reports based on crosstab query is not easy because crosstabs, especially when pivoting on date criteria, can be so dynamic.

2. have a table of all vendors and join the two Spend tables to the vendors table or since you only care about vendors in 2012, just join the 2011 table to the 2012 - join type "Include all records from 2012 ..." and make this an aggregate (GROUP BY) query or use report Grouping & Sorting with aggregate calcs in footers.
This query will also have to be modified each year.

So, either way, next year you will have to modify report.

[ssanfu]

As June7 said, you are going to have a lot of headaches....

Google "normalization". Read everything. then read it again... and again. (I am still reading about it)

You really need to normalize you structure before you do anything else.