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" <Jo**@nospam.infovis.co.ukwrote in message
news:eo**************@TK2MSFTNGP06.phx.gbl...

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

[John]

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

Regards

"Jeff Boyce" <no******@nonsense.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

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" <Jo**@nospam.infovis.co.ukwrote in message
news:eo**************@TK2MSFTNGP06.phx.gbl...

>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

[John Vinson]

On Wed, 27 Dec 2006 03:18:03 -0000, "John" <Jo**@nospam.infovis.co.uk>
wrote:

>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/ex...ributors2.html for contact info.
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:eo**************@TK2MSFTNGP06.phx.gbl...

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

[Tom van Stiphout]

On Tue, 26 Dec 2006 22:55:40 -0800, "'69 Camaro"
<Fo**************************@Spameater.orgZERO_SP AMwrote:

I fully agree with the advice already given: tables per year are a bad
idea, and not needed thanks to the power of indexing.
A good-willing amateur programmer at one of our clients took this
approach as well, and ended up having to adjust tables every year, and
the queries that were built upon it, and the forms and reports that
were built upon those. The system ended up so unwieldy that we had to
completely rewrite it. Now they have much fewer database objects, and
a button for "annual roll-over" that does some end-of-year
calculations and data maintenance.

Assuming you persists with the current design, perhaps it can be done
in two queries: a union all followed by a transform.
In the first, I would add a column indicating the source table:
(assuming all columns the same in the 3 tables)
select *, 2004 as TheYear from TableC
union all
select *, 2005 as TheYear from TableB
union all
select *, 2006 as TheYear from TableA

Then in the transform I would use the crosstab query wizard and put
TheYear as the row heading and the ID as the column heading.

-Tom.

>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/ex...ributors2.html for contact info.
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:eo**************@TK2MSFTNGP06.phx.gbl...

>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

[Gary Walter]

"John" wrote:

>
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");