Merging two tables together (more difficult than it sounds)

  • Thread starter Thread starter MaxGay via AccessMonster.com
  • Start date Start date
M

MaxGay via AccessMonster.com

I have two tables table07 and table08 one has data for 2007 and the other
2008. What I need to do is to take the information from both tables that
matches and put it into a big table for a report. The problem is that some of
the data is only for 2007 and some only for 2008 but the end user wants to
see all of the data with the report. For example
table07 has the following fields
FundedProgram
Fund
CommitmentItem
Budget
07CostB udget

table08 has the following fields
FundedProgram
Fund
CommitmentItem
Budget
08CostB udget

Fund in the two tables is the fiscal year 07 or 08 respectively (I didn't
design this I inherited it)

What I need done is to have all the information on a report for both 07 and
08 (i.e. FundedProgram, CommitmentItem, and Budget) and for each of the
corresponding years (07 and 08) there respective CostBudget fields and only
have them show up corresponding to the 07 or 08 data.

Let me know if you need clarification
 
As you probably realize, this design goes against a good relational database.
For your report however, you should create a table which more reflects that
design by appending the data into a temporary table (use it just for this
report). What I would do is not separate the years. I would have a
CostBudget field but then add a field for the year. Append one table at a
time with the correct year going into the data itself.

Hope this helps,
Jackie
 
You've already heard the advice about the table structure but even with a
normalized structure, you would have the same problem. You need to simulate
a "full outer join" which Access does not support. To do this, you need a
union query with two sub queries, one doing a left join and the other doing
a right join. Use Union All since there will be duplicates to eliminate.
Here's a graphic of what a full outer join will do for you.

Tbl07:
A
B
C
D
E
F
Tbl08:
a
d
f
g
h

Left Join:
A a
B
C
D d
E
F f
Right Join:
A a
D d
F f
g
h

Union All:
A a
B
C
D d
E
F f
g
h

The Union All eliminates the duplicates between the two outer join sets and
will return rows with data from only one of the two years as well as rows
with data in both years.
 
Could you supply me with a small snippet of code? The one I am using is
giving me more multiples of the records.

Thank you

Pat said:
You've already heard the advice about the table structure but even with a
normalized structure, you would have the same problem. You need to simulate
a "full outer join" which Access does not support. To do this, you need a
union query with two sub queries, one doing a left join and the other doing
a right join. Use Union All since there will be duplicates to eliminate.
Here's a graphic of what a full outer join will do for you.

Tbl07:
A
B
C
D
E
F
Tbl08:
a
d
f
g
h

Left Join:
A a
B
C
D d
E
F f
Right Join:
A a
D d
F f
g
h

Union All:
A a
B
C
D d
E
F f
g
h

The Union All eliminates the duplicates between the two outer join sets and
will return rows with data from only one of the two years as well as rows
with data in both years.
I have two tables table07 and table08 one has data for 2007 and the other
2008. What I need to do is to take the information from both tables that
Click to expand...
[quoted text clipped - 27 lines]
Let me know if you need clarification
Click to expand...
Click to expand...
 
Select tblA.fld1, tblB,fld1,........additional fields
FROM tblA LEFT JOIN tblB ON tblA.fld1 = tblB.fld1
Union All Select tblA.fld1, tblB,fld1
FROM tblA RIGHT JOIN tblB ON tblA.fld1 = tblB.fld1;

I prefer to actually make individual queries and save them as querydefs so
that they are easier to test and then I union them in a final query: You
can also create the separate queries, test them, and then cut and paste the
SQL string into the union query.

Select * from qryA
Union All Select * from qryB
Union All Select * from qryC
.........;

There isn't a specific limit to the number of selects within a union but I
have run into issues with some complicated ones. To get around the problem,
I created intermediary unions so union 1, unioned queries 1-10 and union 2
unioned queries 11-20 and final union unioned union1 and union2.

MaxGay via AccessMonster.com said:
Could you supply me with a small snippet of code? The one I am using is
giving me more multiples of the records.

Thank you

Pat said:
You've already heard the advice about the table structure but even with a
normalized structure, you would have the same problem. You need to
simulate
a "full outer join" which Access does not support. To do this, you need a
union query with two sub queries, one doing a left join and the other
doing
a right join. Use Union All since there will be duplicates to eliminate.
Here's a graphic of what a full outer join will do for you.

Tbl07:
A
B
C
D
E
F
Tbl08:
a
d
f
g
h

Left Join:
A a
B
C
D d
E
F f
Right Join:
A a
D d
F f
g
h

Union All:
A a
B
C
D d
E
F f
g
h

The Union All eliminates the duplicates between the two outer join sets
and
will return rows with data from only one of the two years as well as rows
with data in both years.
I have two tables table07 and table08 one has data for 2007 and the other
2008. What I need to do is to take the information from both tables that
Click to expand...
[quoted text clipped - 27 lines]
Let me know if you need clarification
Click to expand...
Click to expand...
Click to expand...
 
Thanks Pat

Worked out perfectly

Have a great weekend

Pat said:
Select tblA.fld1, tblB,fld1,........additional fields
FROM tblA LEFT JOIN tblB ON tblA.fld1 = tblB.fld1
Union All Select tblA.fld1, tblB,fld1
FROM tblA RIGHT JOIN tblB ON tblA.fld1 = tblB.fld1;

I prefer to actually make individual queries and save them as querydefs so
that they are easier to test and then I union them in a final query: You
can also create the separate queries, test them, and then cut and paste the
SQL string into the union query.

Select * from qryA
Union All Select * from qryB
Union All Select * from qryC
........;

There isn't a specific limit to the number of selects within a union but I
have run into issues with some complicated ones. To get around the problem,
I created intermediary unions so union 1, unioned queries 1-10 and union 2
unioned queries 11-20 and final union unioned union1 and union2.
Could you supply me with a small snippet of code? The one I am using is
giving me more multiples of the records.
Click to expand...
[quoted text clipped - 58 lines]
Click to expand...
 
Back
Top