Need to Merge 6 Similar Tables

[CJS]

Hi Everyone,



I am new to Access and need assistance with a Query.

I have 6 tables.

TABLE 1: Customer Number, Customer Name, Vendor Number, Vendor Name, Fiscal Year, Sales
TABLE 2: Customer Number, Customer Name, Vendor Number, Vendor Name, Fiscal Year, Sales
TABLE 3: Customer Number, Customer Name, Vendor Number, Vendor Name, Fiscal Year, Sales
TABLE 4: Customer Number, Customer Name, Vendor Number, Vendor Name, Fiscal Year, Sales
TABLE 5: Customer Number, Customer Name, Vendor Number, Vendor Name, Fiscal Year, Sales
TABLE 6: Customer Number, Customer Name, Vendor Number, Vendor Name, Fiscal Year, Sales

Each table contain approximately 1 million rows.
Table 1 contains all sales data from 2009 (fiscal year is 2009 for each row), table 2 contains sales data from 2010 (fiscal year is 2010 for each row), table 3 contains sales data from 2011 (fiscal year is 2011 for each row),table 4 contains sales data from 2012 (fiscal year is 2012 for each row), table 5 contains sales data from 2013 (fiscal year is 2013 for each row), table 6 contains sales data from 2014 (fiscal year is 2014 for each row)

I want to essentially create 1 table...
Customer Number, Customer Name, Vendor Number, Vendor Name, 2009 Sales,2010 Sales, 2011 Sales, 2012 Sales,2013 Sales, 2014 Sales

How do I make this happen???

I am familiar with the iif function and establishing relationships. I can't seem to get what I want though... Any assistance would be appreciated!

[JeffChr]

you need to run append queries to a pre-designed table containing the fields you mentioned above for your "1 table". Create a table, and then design and run 6 different append queries. The only difference between the append queries will be the destination for the Sales field.

[June7]

That resulting table is not a normalized structure. Should be one field with sales data and another field with the year.

Customer info should not be duplicated. Should be a Customers table and then save only CustomerID into the Sales table.

A UNION query can replicate the combined table structure.

SELECT [Customer Number], [Customer Name], [Vendor Number], [Vendor Name], [Fiscal Year], Sales FROM Table1
UNION SELECT [Customer Number], [Customer Name], [Vendor Number], [Vendor Name], [Fiscal Year], Sales FROM Table2
UNION SELECT [Customer Number], [Customer Name], [Vendor Number], [Vendor Name], [Fiscal Year], Sales FROM Table3
UNION SELECT [Customer Number], [Customer Name], [Vendor Number], [Vendor Name], [Fiscal Year], Sales FROM Table4
UNION SELECT [Customer Number], [Customer Name], [Vendor Number], [Vendor Name], [Fiscal Year], Sales FROM Table5
UNION SELECT [Customer Number], [Customer Name], [Vendor Number], [Vendor Name], [Fiscal Year], Sales FROM Table6;

There is no wizard or builder for UNION query, must type in SQL view of query designer.

Use that query in other queries or as source to populate combined table.

[CJ_London]

Not sure if creating a new table or using a union query, which would be quicker, but to follow on having got your one table (Jeffchr) or unionised view (June7) with 6 million rows, use a crosstab query to create the view you want. I would also suggest that it would be more efficient if this table/union query does not contain the customer name and vendor name. Instead, create another table for each of these to contain customer number and customer name - use a select distinct in your update query to avoid duplicates and have a primary key on customer number to avoid adding the same customer more than once from each of your original tables. You can then link back to these for the name.

[CJS]

Ultimately I'm trying to get my larger all-encompassing table created so that I can export the whole thing into a Pivot Table in Excel.

JeffChr: Append Query suggestion leads to an Error Statement. Thank You for the suggestion.
June7: Not familiar with SQL so the UNION Query is likely beyond my skill set. Appreciate the insight and it's good to know the logic based behind that method.
Ajax: Unfortunately since I'm trying to get this over into excel ultimately, your suggestion, although likely a great option for most in the access world, it doesn't meet my final application.

I really appreciate all you taking the time to assist!

[CJ_London]

good luck with your project

[June7]

What is exact error message? Post the append query sql statement for analysis.

Did you try opening query designer in SQL View and copy/paste the UNION query? Replace with actual table names.

Why Excel pivot table? Access 2010 has pivot view of tables and forms.