Access 2010 Combining Two Tables WIth Similiar But Different Records..

Hi - I have two Access tables with same fields but for different time periods.  I want to combine all records from each table so I can compare values.  But I need all records from each table and not just where they match on part number.  I can get there by workaround that requires a little manual intervention, but I am looking for an automated way.  How can I accomplish this with an Access query or queries?  Example tables and result are attached in spreadsheet below.  Thanks.  - Reilly
C--Users-E221037-Desktop-EE-Question.xls
LVL 7
tomfarrarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonCommented:
Select part,sum(2013) as [Yr 2013], sum(2014) as [Yr 2014], [Yr 2014]-[Yr 2013] as [Difference] from
 (Select Part,value as 2013,0 as 2014 from table1
union all
select part,0 as 2013,value as 2014 from table2)
0
mbizupCommented:
Give this a try...

SELECT ID, Year2013, Year2014, Year2014 - Year2013 AS Difference
FROM
(SELECT t1.Part AS ID, t1.Value AS Year2013, t2.Value as Year2014
FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.Part = t2.Part)
UNION
(SELECT t2.Part AS ID, t1.Value AS Year2013, t2.Value as Year2014
FROM Table2 t2 LEFT JOIN Table1 t1 ON t1.Part = t2.Part)
ORDER BY ID

Open in new window

0
PatHartmanCommented:
Have you tried a union query?  To create the union, the select clauses must select the columns in the same order and the columns must be of the same data type.

Select txt1, txt2, date3, num4 From tbl1
Union Select txta, txtb, datec, numd From tblA;

This will give you all the rows from tbl1 stacked on top of all the  rows from tblA.

The Access QBE cannot visually show a union so you'll have to build it in SQL view.  You can include the SQL for the queries or you can select all from the queries.

Select tbl1.*
Union Select tblA.*;
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

tomfarrarAuthor Commented:
Pat - Yes, tried the union and it worked fine, except year 2013 and 2014 records are different records and if you look at the results I am shooting for (see spreadsheet) the union does not accommodate that presentation (at least as I see it).
0
SimonCommented:
Sorry, hadn't tested my first post...
Select part,sum(v2013) as [Yr 2013], sum(v2014) as [Yr 2014], [Yr 2014]-[Yr 2013] as [Difference] from
 (Select Part,[value] as v2013,0 as [v2014] from table1
union all
select part,0 as 2013,[value] as [v2014] from table2) as tbl
group by part

Open in new window


This matches the required results:
part	Yr 2013	Yr 2014	Difference
1	89	0	-89
2	91	62	-29
3	56	57	1
4	83	0	-83
5	53	39	-14
6	33	0	-33
7	44	72	28
8	63	29	-34
9	0	112	112
10	0	48	48
11	0	113	113

Open in new window


Note that I've inserted zero values for the 'other' year in both halves of the union subquery.
0
tomfarrarAuthor Commented:
Hi Simon - I do not write SQL from scratch very well, but work with the query grid drag and drop in Access, and I am not sure what you are doing in the "Select" statements.  Is this doable in the grid?  The data I provided is sample as I didn't feel I could share the entire record as the data is not mine.  There are more fields that accompany the values for 2013 and 2014, but I think once I get the logic of the SQL provided I can get there.  

Working with your query too, mbizup.  Thank you both.
0
SimonCommented:
Hi Tom,

The union query doesn't display in the grid view. you'd need to do that bit in the SQL view, though you can generate the SQL for each SELECT statement for the union query in the grid view and then copy'n'paste it into the SQL view with "UNION ALL" between each.

The key design element is to add zero values for the year(s) that aren't included in the table you are building the query on. If you're doing it in the grid view, you enter the column value as "YearAliasName:0" without the double quotes.

You can build the union query and save it and then use that as the source for the second 'outer' query that groups and sums the values.

If you like, I can build the query with whatever column names you have (within reason) without you sharing actual data - just list the column names from each table.
0
tomfarrarAuthor Commented:
Hi Simon - I have a general idea of what you are saying except I am not grasping the "YearAliasName:0" just yet.  The period compared are actually "Jan 2015" and "March 2015" and that is noted in the attached query I copied/pasted together as a union query.  As I stated earlier I have run this query but get two records (lines), one for Jan 2015 and one for March 2015.  I need to bring these two lines together to compare values side-by-side in one record (line) if that makes sense.  

Your sample output looked good if that works for the query I have attached.  Please let me know how to proceed, and thanks in advance.  - Tom Reilly
C--Users-E221037-Desktop-EE-Query.docx
0
tomfarrarAuthor Commented:
Oops...  Just to complicate the issue there are a few of the fields that need to be side-by-side also in one line.  The line is defined by the Branch Plant Number and Part Number.  These fields make the record unique, but some of the remaining fields other than values (like Reserve Aging Bucket can be different for the two periods and need to be reported side-byside and not in two lines.  Does that make sense?

I was wondering if there was a way to create a union query of the combined Branch Plant Number, Part Number and Period, using this to capture the data with an outer join of the two tables.  But I have not got this to work.
0
SimonCommented:
That's a lot of fields to be included in your side by side comparison. It gets a bit unwieldy when there as multiple sets of comparisons...

For each column that you want to sum side by side, you'd need to have a dummy column to represent the corresponding column in the other half of the union query.
1. In first select within union, add a dummy column with value 0 and alias of column title+"FC" (for forecast)"
2. In the second select, add dummy columns in the position that the real data occupied in the 1st select list and real data in the column positions that were zero value aliases in the 1st select"
e.g.
SELECT 
[Branch Plant Number], [Part Number],
[OH Qty], [OH Qty FC]:0,
[Inventory Value USD],[Inventory Value USD FC]:0, 
UnpostedINVAmount, UnpostedINVAmount_FC:0, 
AdjustedInventoryVaue, AdjustedInventoryVaue_FC:0, 
AdustedReserveValueUSDCase2,AdustedReserveValueUSDCase2_FC:0
FROM [December 2014 Sum];
UNION ALL
SELECT
[Branch Plant Number], [Part Number],
0,[OH Qty],
0,[Inventory Value USD],
0,UnpostedINVAmount,
0,AdjustedInventoryVaue, 
0,AdustedReserveValueUSDCase2
FROM [March 2015 Forecast];

Open in new window


Once you have the union query as above, the outer query sums each value column and aliases it to produce your final column titles (alias can't be exactly the same as the column name), and then subtracts the actual from the forecast to give the difference. Putting it together you get this:
SELECT
[Branch Plant Number], [Part Number],
OH_Qty_Act:sum([OH Qty]), OH_Qty_FC: sum([OH Qty FC]), OH_Qty_Diff: OH_Qty_FC - OH_Qty_Act,
Inv_Value_Act: sum([Inventory Value USD]), Inv_Value_FC: sum([Inventory Value USD FC]), Inv_Value_Diff: Inv_Value_FC - Inv_Value_Act,
Unposted_Act: sum(UnpostedINVAmount), Unposted_FC: sum(UnpostedINVAmount_FC), Unposted_Diff: Unposted_FC - Unposted_Act, 
AdjInv_Act: sum(AdjustedInventoryVaue), AdjInv_FC: sum(AdjustedInventoryVaue_FC), AdjInv_Diff: AdjInv_FC - AdjInv_Act, 
AdjResVal_Act: sum(AdustedReserveValueUSDCase2), AdjresVal_FC: sum(AdustedReserveValueUSDCase2_FC), AdjResVal_Diff: AdjResVal_FC - AdjResVal_Act

FROM
(
SELECT 
[Branch Plant Number], [Part Number],
[OH Qty], [OH Qty FC]:0,
[Inventory Value USD],[Inventory Value USD FC]:0, 
UnpostedINVAmount, UnpostedINVAmount_FC:0, 
AdjustedInventoryVaue, AdjustedInventoryVaue_FC:0, 
AdustedReserveValueUSDCase2,AdustedReserveValueUSDCase2_FC:0
FROM [December 2014 Sum];
UNION ALL
SELECT
[Branch Plant Number], [Part Number],
0,[OH Qty],
0,[Inventory Value USD],
0,UnpostedINVAmount,
0,AdjustedInventoryVaue, 
0,AdustedReserveValueUSDCase2
FROM [March 2015 Forecast]
) as tbl
group by [Branch Plant Number], [Part Number]

Open in new window


Above not tested. Miracle if I've written it all without a single typo... If you can provide one or two rows of representative data for the tables (pasted to Excel and any private data changed), I will check that it works properly.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tomfarrarAuthor Commented:
Hi Simon Thanks for the response.  Trying to run the query gives me the attached error.  Tom
C--Users-E221037-Desktop-Error.PNG
0
SimonCommented:
Curses !*%# - I used query grid syntax for aliases instead of SQL... Hold on a few mins.
0
SimonCommented:
Please try this... I think I've corrected it all. Rather painful working with all the field names with spaces in.

SELECT
[Branch Plant Number], [Part Number],
sum([OH Qty]) as OH_Qty_Act, sum([OH Qty FC]) as OH_Qty_FC, OH_Qty_FC - OH_Qty_Act as OH_Qty_Diff,
sum([Inventory Value USD]) as Inv_Value_Act,  sum([Inventory Value USD FC]) as Inv_Value_FC,  Inv_Value_FC - Inv_Value_Act as Inv_Value_Diff,
sum(UnpostedINVAmount) as Unposted_Act,  sum(UnpostedINVAmount_FC) as Unposted_FC,  Unposted_FC - Unposted_Act as Unposted_Diff, 
sum(AdjustedInventoryVaue) as AdjInv_Act, sum(AdjustedInventoryVaue_FC) as AdjInv_FC, AdjInv_FC - AdjInv_Act as AdjInv_Diff, 
sum(AdustedReserveValueUSDCase2) as AdjResVal_Act, sum(AdustedReserveValueUSDCase2_FC) as AdjresVal_FC, AdjResVal_FC - AdjResVal_Act as AdjResVal_Diff

FROM
(
SELECT 
[Branch Plant Number], [Part Number],
[OH Qty],0 as  [OH Qty FC],
[Inventory Value USD],0 as [Inventory Value USD FC], 
UnpostedINVAmount, 0 as UnpostedINVAmount_FC, 
AdjustedInventoryVaue, 0 as AdjustedInventoryVaue_FC, 
AdustedReserveValueUSDCase2,0 as AdustedReserveValueUSDCase2_FC
FROM [December 2014 Sum]
UNION ALL
SELECT
[Branch Plant Number], [Part Number],
0,[OH Qty],
0,[Inventory Value USD],
0,UnpostedINVAmount,
0,AdjustedInventoryVaue, 
0,AdustedReserveValueUSDCase2
FROM [March 2015 Forecast]
) as tbl
group by [Branch Plant Number], [Part Number]

Open in new window

0
tomfarrarAuthor Commented:
Yes, that works, and I have captured way more time of yours than I planned on.  My apology on that.  The size of the data, and the number of fields are out of my hands.  What you have given me is a great start to building a final product.  Thank you very much for your work.  I will study the query closely to grasp what is being done so as to build on it from here.  - Tom
0
tomfarrarAuthor Commented:
Thanks, Simon.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.