Merging two tables

[Andrew]

Hi,

I want to merge two tables (Tbl_1 & Tbl_2) into a new Table (Tbl_3) but it
is not quite as easy as that (for me anyway).

For both of the tables the field names are identical. Here is a sample of
the layout:

Year Period [Vendor No] [Vendor Name] Sales SOH SOO

There are 3 fields I need to compare - Year, Period and Vendor No. If the 3
fields are the same in both tables then use the entire record from Tbl_2.
Where the fields in either table are unique they need to be used.

This may be simpliefied by saying use ALL of tbl_2 and add anything unique
from Tbl_1.

I'd like to do this in SQL.

Thanks in advance


Andrew
250806.

 

[pietlinden]

sounds like a plain old union query would work.

SELECT Field1, Field2,...
FROM table1
UNION
SELECT FieldA As Field1, FieldB As Field2...
FROM table2
ORDER BY Field1, Field2
WHERE...

I only threw in the AS parts in case you have field names that are
different. You can just alias one so the union will work.

Except you have to type in the SQL for the Union. And one other
thing... UNION queries are _not_ updateable.

 

[Andrew]

Thanks for your help.

I've used your reply to get m to the following but I'm getting an error
'Syntax
Error (Missing Operator).......' in the WHERE clause. Any ideas?

SELECT [Vendor No],[Vendor Name],[Business No],Year,Period,[Sales @ Cost]
FROM Tbl_Merged_Data_Orig
UNION SELECT [Vendor No],[Vendor Name],[Business No],Year,Period,[Sales @
Cost]
FROM Tbl_Merged_Data_New
ORDER BY [Vendor No]
WHERE Tbl_Merged_Data_Orig.[Vendor No]=Tbl_Merged_Data_New.[Vendor No]

 

[pietlinden]

Sure, you don't need a join clause in a Union query. Remove it and
you'll be good to go.

 

[Andrew]

Hi,

Thanks again.


Close but no cigar. if there are duplicate records I want to use the data
from Tbl_Merged_Data_New ONLY. At the moment it is creating duplicate
records.

A reminder, It is to match [Vendor No], Year, Period.



SELECT [Vendor No],[Vendor Name],[Business No],Year,Period,[Sales @ Cost]
FROM Tbl_Merged_Data_Orig
UNION SELECT [Vendor No],[Vendor Name],[Business No],Year,Period,[Sales @
Cost]
FROM Tbl_Merged_Data_New
ORDER BY [Vendor No];

 

[pietlinden]

Hi,

Thanks again.


Close but no cigar.

You're only allowed to be a smartass if you're paying for support.
Otherwise, you'll get a dialtone. if you were so smart, you'd have
solved this one already.

if there are duplicate records I want to use the data

from Tbl_Merged_Data_New ONLY. At the moment it is creating duplicate
records.

Access doesn't really care where the records come from in a UNION
query. Don't believe me? Try running an update on a union query.
They're not updateable.If you're using a union query, you'll probably have to use the find
unmatched query wizard and then union that result with the other table.
the part I'm not really clear on is the difference between two
matching records. On my planet, they're identical. So I don't
understand why a union query doesn't work.

A reminder, It is to match [Vendor No], Year, Period.

not sure I really care.

 

[Andrew]

Hi (e-mail address removed),

Firstly let me assure you I wasn't trying to be a smart arse.

Your assistance, while greatly appreciated didn't work. I wasn't trying to
be rude but I just wanted to spell out better my requirements to get the
required outcome. If you thought I was being rude saying you had gone
offline it was because I hadn't had a reply back for a few hours and I
thought you'd gone to bed (I'm in Australia and it is 3:40pm here).

I am relatively new to Access and the coding of it. I have posted quite a
few questions on this site and am very greatful for the help I've received -
including yours.






--
Andrew

Hi,

Thanks again.


Close but no cigar.

You're only allowed to be a smartass if you're paying for support.
Otherwise, you'll get a dialtone. if you were so smart, you'd have
solved this one already.

if there are duplicate records I want to use the data

from Tbl_Merged_Data_New ONLY. At the moment it is creating duplicate
records.

Access doesn't really care where the records come from in a UNION
query. Don't believe me? Try running an update on a union query.
They're not updateable.If you're using a union query, you'll probably have to use the find
unmatched query wizard and then union that result with the other table.
the part I'm not really clear on is the difference between two
matching records. On my planet, they're identical. So I don't
understand why a union query doesn't work.

A reminder, It is to match [Vendor No], Year, Period.

not sure I really care.

 

[John Spencer]

Try the following revision to the union query.

SELECT [Vendor No],[Vendor Name],[Business No],[Year],Period,[Sales @ Cost]
FROM Tbl_Merged_Data_Orig
UNION
SELECT [Vendor No],[Vendor Name],[Business No],[Year],Period,[Sales @ Cost]
FROM Tbl_Merged_Data_New LEFT JOIN Tbl_Merged_Data_Orig
ON Tbl_Merged_Data_New.[Vendor No] = Tbl_Merged_Data_Orig.[Vendor No]
AND Tbl_Merged_Data_New.[Year] = Tbl_Merged_Data_Orig.[Year]
AND Tbl_Merged_Data_New.[Period] = Tbl_Merged_Data_Orig.[Period]
WHERE Tbl_Merged_Data_Orig.[Vendor No] is Null

Thanks for your help.

I've used your reply to get m to the following but I'm getting an error
'Syntax
Error (Missing Operator).......' in the WHERE clause. Any ideas?

SELECT [Vendor No],[Vendor Name],[Business No],Year,Period,[Sales @ Cost]
FROM Tbl_Merged_Data_Orig
UNION SELECT [Vendor No],[Vendor Name],[Business No],Year,Period,[Sales @
Cost]
FROM Tbl_Merged_Data_New
ORDER BY [Vendor No]
WHERE Tbl_Merged_Data_Orig.[Vendor No]=Tbl_Merged_Data_New.[Vendor No]

--
Andrew

sounds like a plain old union query would work.

SELECT Field1, Field2,...
FROM table1
UNION
SELECT FieldA As Field1, FieldB As Field2...
FROM table2
ORDER BY Field1, Field2
WHERE...

I only threw in the AS parts in case you have field names that are
different. You can just alias one so the union will work.

Except you have to type in the SQL for the Union. And one other
thing... UNION queries are _not_ updateable.