Merging 2 tables

[Guest]

Hi,

I've posted this already today and had assistance from
(e-mail address removed) but it seems he has gone offline.

I am in a little hurry so I'm hoping by re-posting it someone else may pick
it up and assist. I've also had a bit more of a think about it and I'm going
to ask the question in a simpler way.

Here is the union query I have to date:

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


This gives you an idea of the field names and table names. What I want to
do is create a new table with ALL of table 'Tbl_Merged_Data_New' in it (as
this is new data' and all of table 'Tbl_Merged_Data_Orig' THAT DOESN'T APPEAR
(is unique) in Tbl_Merged_Data_New.

Andrew 250806

 

[pietlinden]

Andrew,
this is simple.
first off, get rid of the weird symbols in your field names. Replace
spaces with underscores or no space. Otherwise, they could come back
to bite you later.

that doesn't appear...
if you UNION two tables together, the duplicates are automatically
removed for you. If you really want the duplicates, you have to use
UNION ALL.

So what's the problem? Create the Union query, turn it into a
maketable query, and you're good to go.

Have you tried this yet? Run the union query and see what you get.

 

[Gary Walter]

I've posted this already today and had assistance from
(e-mail address removed) but it seems he has gone offline.

I am in a little hurry so I'm hoping by re-posting it someone else may pick
it up and assist. I've also had a bit more of a think about it and I'm going
to ask the question in a simpler way.

Here is the union query I have to date:

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


This gives you an idea of the field names and table names. What I want to
do is create a new table with ALL of table 'Tbl_Merged_Data_New' in it (as
this is new data' and all of table 'Tbl_Merged_Data_Orig' THAT DOESN'T APPEAR
(is unique) in Tbl_Merged_Data_New.

I believe the problem is that UNION acts like DISTINCT, i.e.,
you get distinct over *all the fields.* It appears to me you want
"distinct" over only [Vendor No], Year, and Period.

I imagine easiest way would be to just make a copy of
"new" table, then append data from "orig" via a
query that weeds out dups...

qryUnDupsFromOrig

SELECT
o.[Vendor No],
o.[Vendor Name],
o.[Business No],
o.[Year],
o.Period,
o.[Sales @ Cost]
FROM
Tbl_Merged_Data_Orig As o
LEFT JOIN
Tbl_Merged_Data_New As n
ON
o.[Vendor No] = n.[Vendor No]
AND
o.[Year] = n.[Year]
AND
o.Period = o.Period
WHERE
n.[Vendor No] IS NULL

 

[John Spencer]

Andrew,
See my reply in your earlier thread. If it doesn't work or gives you the
wrong results, post back with an explanation of the problem.
For example, I get a syntax error or the results are wrong - missing
records, duplicate records, too few or too many records, etc.