Merging three tables

[John]

Hi

I have three tables with a common id with which they can be linked. I need
to merge them in a way that the resultant table has all records from three
tables. Below is what sort of result I am expecting;

Table 1
ID Value1
1 A1

Table 2
ID Value2
1 A2
2 B2

Table 1
ID Value3
2 B3
3 C3

Result expected;

ID Value1 Value2 Value3
1 A1 A2 <blank>
2 <blank> B2 B3
3 <blank> <blank> C3

Columns are blank where no records match for that table.

How can I go about doing this?

Thanks

Regards

 

[Jeff Boyce]

It appears you are trying to display data in something like a cross-tab
format. Have you looked at cross-tab queries and reports?

Why is some data in table1, some in table2, etc.? Is there a reason the
data should be separated? Should not be separated?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John]

The data is for three separate years and for performance reasons we keep
them separate.

Regards

 

[John Vinson]

The data is for three separate years and for performance reasons we keep
them separate.

DEMONSTRATED, actual performance reasons?

Or an assumption that "that many records can't be handled by Access"?

With an indexed year (or, better, Date/Time) field and proper query
design, Access should be able to do fine with hundreds of thousands or
low millions of records per year. And you wouldn't have the difficulty
of putting the three years back together - while a query on a large
(properly indexed) single table can be reasonable, a UNION query will
always be much slower (a UNION ALL query is better but still not as
good as the single table).

John W. Vinson[MVP]

 

['69 Camaro]

Hi, John.

I have three tables with a common id with which they can be linked. I need to
merge them .. . .
How can I go about doing this?

One way to do it is by building five queries. In this example, the three tables
are named TableC, TableD, and TableE.

Create the first query and name it qryUnionAllIDs:

SELECT ID
FROM TableC
UNION
SELECT ID
FROM TableD
UNION
SELECT ID
FROM TableE
ORDER BY ID;

Create the second query and name it qryAllTableC:

SELECT qryUnionAllIDs.ID, TableC.Value1
FROM qryUnionAllIDs LEFT JOIN TableC
ON qryUnionAllIDs.ID = TableC.ID;

Create the third query and name it qryAllTableD:

SELECT qryUnionAllIDs.ID, TableD.Value2
FROM qryUnionAllIDs LEFT JOIN TableD
ON qryUnionAllIDs.ID = TableD.ID;

Create the fourth query and name it qryAllTableE:

SELECT qryUnionAllIDs.ID, TableE.Value3
FROM qryUnionAllIDs LEFT JOIN TableE
ON qryUnionAllIDs.ID = TableE.ID;

Create the fifth query and run it to create tblValuesFromTablesCDE:

SELECT * INTO tblValuesFromTablesCDE
FROM (SELECT qryAllTableC.ID,
qryAllTableC.Value1, qryAllTableD.Value2,
qryAllTableE.Value3
FROM (qryAllTableC INNER JOIN qryAllTableD
ON qryAllTableC.ID = qryAllTableD.ID)
INNER JOIN qryAllTableE
ON qryAllTableD.ID = qryAllTableE.ID);

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Gary Walter]

I have three tables with a common id with which they can be linked. I need
to merge them in a way that the resultant table has all records from three
tables. Below is what sort of result I am expecting;

Table 1
ID Value1
1 A1

Table 2
ID Value2
1 A2
2 B2

Table 1
ID Value3
2 B3
3 C3

Result expected;

ID Value1 Value2 Value3
1 A1 A2 <blank>
2 <blank> B2 B3
3 <blank> <blank> C3

Columns are blank where no records match for that table.

In addition to all the sage advice you have already
received, here may be another way...

(untested)

qryUnion

SELECT
ID,
Value1 As theVal,
1 As theTable
FROM
Table1
UNION ALL
SELECT
ID,
Value2,
2
FROM
Table2
UNION ALL
SELECT
ID,
Value3,
3
FROM
Table3;

qryxtabFinal

TRANSFORM First(theVal)
SELECT
q.ID
FROM qryUnion AS q
GROUP BY
q.ID
PIVOT "Value" & [theTable]
In
("Value1",
"Value2",
"Value3");