Merge Tables?

[Duncs]

I'm not sure if it's merging tables, but here goes...

I have two tables as follows:

Table 1
CustName
Serial One
Date Registered

Table 2
CustName
Serial Two
Date Registered

What I want to do is create a new table as follows:

Table 3
CustName
Serial One
Date Registered One
Serial Two
Date Registered Two

Some customers may appear on Table 1 & Table 2, some may appear only
in Table 1 and some only in Table 2. So, I can see the following
possible record entries in Table 3:

CustName Serial One Date Reg One Serial Two Date Reg Two
Jones A123456 13/01/10 B123456
10/02/10
Smith A523652 27/01/10
Brown
D52135A 05/02/10

Can someone tell me how to do this, as it's driving me mad!!

TIA

Duncs

 

[John Spencer]

You can do this using a series of queries. The first query will get a list of
CustName that exist in either table.

SELECT CustName
FROM [Table 1]
UNION
SELECT CustName
FROM [Table 2]

Now using that saved query.
SELECT qUnionQuery.CustName
, [Table 1].[Serial One]
, [Table 1].[Date Registered] as DateOne
, [Table 2].[Serial Two]
, [Table 2].[Date Registered] as DateOne
FROM (qUnionQuery LEFT JOIN [Table 1]
ON qUnionQuery.CustName = [Table 1].CustName)
LEFT JOIN [Table 2]
ON qUnionQuery.CustName = [Table 2].CustName

IF you don't know how to work in the SQL window (not query design window) post
back for instructions. You will have to build the Union query in the SQL view
as it is not supported in the query design view.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Mrs. Ugh]

Duncs-
I don't think you really want to merge tables, but to have a query to create
the table you showed with the data from the two tables. I would actually
create a third table with the following structure:
tblCustomers
field1: CustID (autonumber, primary key)
field2: CustName

Then your Table1 and Table2 should refer to CustID as a foreign key (instead
of having the same name in both tables). This avoids problems with
mis-spellings, similar names, etc.. You can use queries to do this; you don't
have to retype.

The SQL for the query should be something like:
SELECT tblCustomers.CustID AS tblCustomers_CustID, tblCustomers.CustName,
tblSerial1.Serial1, tblSerial1.DateSerial1, tblSerial2.Serial2,
tblSerial2.DateSerial2
FROM (tblCustomers LEFT JOIN tblSerial2 ON tblCustomers.CustID =
tblSerial2.CustID) LEFT JOIN tblSerial1 ON tblCustomers.CustID =
tblSerial1.CustID;

Jill

 

[Duncs]

Jill,

The problem with this is, both tables may or may not have the names in
them. So, Table1 may have the name Smith, with CustID 10 but, Table2
may have the name Jones at CustID 10, with Smith at CustID 47.

Or, am I missing something simple in what you are suggesting?

Duncs

 

[Duncs]

Cheers John. That works great.

Duncs

You can do this using a series of queries.  The first query will get a list of
  CustName that exist in either table.

SELECT CustName
FROM [Table 1]
UNION
SELECT CustName
FROM [Table 2]

Now using that saved query.
SELECT qUnionQuery.CustName
, [Table 1].[Serial One]
, [Table 1].[Date Registered] as DateOne
, [Table 2].[Serial Two]
, [Table 2].[Date Registered] as DateOne
FROM (qUnionQuery LEFT JOIN [Table 1]
   ON qUnionQuery.CustName = [Table 1].CustName)
LEFT JOIN [Table 2]
   ON qUnionQuery.CustName = [Table 2].CustName

IF you don't know how to work in the SQL window (not query design window)post
back for instructions.  You will have to build the Union query in the SQL view
as it is not supported in the query design view.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I'm not sure if it's merging tables, but here goes...

I have two tables as follows:

Table 1
CustName
Serial One
Date Registered

Table 2
CustName
Serial Two
Date Registered

What I want to do is create a new table as follows:

Table 3
CustName
Serial One
Date Registered One
Serial Two
Date Registered Two

Some customers may appear on Table 1 & Table 2, some may appear only
in Table 1 and some only in Table 2.  So, I can see the following
possible record entries in Table 3:

CustName     Serial One    Date Reg One   Serial Two    Date Reg Two
Jones            A123456      13/01/10            B123456
10/02/10
Smith            A523652      27/01/10
Brown
D52135A      05/02/10

Can someone tell me how to do this, as it's driving me mad!!  

Duncs- Hide quoted text -

- Show quoted text -

 

[Mrs. Ugh]

If you already have a bunch of forms, reports, etc. built on your design, it
may not be worth it to change, but here goes...
You didn't list CustID as a field in your tables, so I am a bit
confused. If you have both the customer name and a customer ID in Table1/2,
you would have to do the following. If you just have the name, add a CustID
field to Table1/2 and then follow below.
You have to create a new table (tblCustomers) with new IDs, compiling
the data from both Table 1 and Table 2, without duplicates. I would 1) create
the new (empty table), 2) use an append query to append all of the names from
Table 1 into the new table (the assumes there are no duplicates in Table1
customers), 3) make another append query that shows the Customer Names from
Table2 that are not matched in Table1 and appends them to the new table.
Once you have your Customers table, make a select query that includes
Table1 and tblCustomers, joined by CustomerName (NOT ID). Show the CustID
from Table1 and the CustID from tblCustomers as [NewID]. Create an update
query based on the new select query. Show CustID (which is from Table1) and
in the Update To row, type [NewID]. Run the update query and repeat for
Table2.
I then check the data by creating a select query joining tblCustomers
and Table1/2 using CustID. Show the Customer Name from both tblCustomers and
Table1/2. Make sure the names are the same. Then you can delete the Customer
Name field from Table1 and Table2. You can also delete all of the queries you
made to do this. To view the "old" Table1/2, create a select query with
tblCustomers and Table1/2 joined by CustID. That way you can see the
customer's name not just the ID number.

Jill

 

[Duncs]

John,

Many thanks for this, and sorry for the delay in posting back. Your
suggestion, aas I would expect, worked a treat!

Thanks for your help!

Duncs

You can do this using a series of queries.  The first query will get a list of
  CustName that exist in either table.

SELECT CustName
FROM [Table 1]
UNION
SELECT CustName
FROM [Table 2]

Now using that saved query.
SELECT qUnionQuery.CustName
, [Table 1].[Serial One]
, [Table 1].[Date Registered] as DateOne
, [Table 2].[Serial Two]
, [Table 2].[Date Registered] as DateOne
FROM (qUnionQuery LEFT JOIN [Table 1]
   ON qUnionQuery.CustName = [Table 1].CustName)
LEFT JOIN [Table 2]
   ON qUnionQuery.CustName = [Table 2].CustName

IF you don't know how to work in the SQL window (not query design window)post
back for instructions.  You will have to build the Union query in the SQL view
as it is not supported in the query design view.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I'm not sure if it's merging tables, but here goes...

I have two tables as follows:

Table 1
CustName
Serial One
Date Registered

Table 2
CustName
Serial Two
Date Registered

What I want to do is create a new table as follows:

Table 3
CustName
Serial One
Date Registered One
Serial Two
Date Registered Two

Some customers may appear on Table 1 & Table 2, some may appear only
in Table 1 and some only in Table 2.  So, I can see the following
possible record entries in Table 3:

CustName     Serial One    Date Reg One   Serial Two    Date Reg Two
Jones            A123456      13/01/10            B123456
10/02/10
Smith            A523652      27/01/10
Brown
D52135A      05/02/10

Can someone tell me how to do this, as it's driving me mad!!  

Duncs- Hide quoted text -

- Show quoted text -

 

[De Jager]

John,

Many thanks for this, and sorry for the delay in posting back. Your
suggestion, aas I would expect, worked a treat!

Thanks for your help!

Duncs

You can do this using a series of queries. The first query will get a list
of
CustName that exist in either table.

SELECT CustName
FROM [Table 1]
UNION
SELECT CustName
FROM [Table 2]

Now using that saved query.
SELECT qUnionQuery.CustName
, [Table 1].[Serial One]
, [Table 1].[Date Registered] as DateOne
, [Table 2].[Serial Two]
, [Table 2].[Date Registered] as DateOne
FROM (qUnionQuery LEFT JOIN [Table 1]
ON qUnionQuery.CustName = [Table 1].CustName)
LEFT JOIN [Table 2]
ON qUnionQuery.CustName = [Table 2].CustName

IF you don't know how to work in the SQL window (not query design window)
post
back for instructions. You will have to build the Union query in the SQL
view
as it is not supported in the query design view.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I'm not sure if it's merging tables, but here goes...

I have two tables as follows:

Table 1
CustName
Serial One
Date Registered

Table 2
CustName
Serial Two
Date Registered

What I want to do is create a new table as follows:

Table 3
CustName
Serial One
Date Registered One
Serial Two
Date Registered Two

Some customers may appear on Table 1 & Table 2, some may appear only
in Table 1 and some only in Table 2. So, I can see the following
possible record entries in Table 3:

CustName Serial One Date Reg One Serial Two Date Reg Two
Jones A123456 13/01/10 B123456
10/02/10
Smith A523652 27/01/10
Brown
D52135A 05/02/10

Can someone tell me how to do this, as it's driving me mad!!

Duncs- Hide quoted text -

- Show quoted text -