OK, I give up...

[Nightowl4933]

...but I was really trying to do this myself!

I have two tables, structurally the same and with a lot of duplicated data - basically, I manually 'fixed' different data in both of them and I'm having difficulty merging them into a 'consolidated table to include both updates.

So, in detail:
The PremID field in tblOriginal is spot on, and I want to append all the data from tblUpdate, but not the records where the PremID is the same. Fore example, if there is a PremID value in tblOriginal of ABC12345, then I wouldn't import a record with theat same PremID from tblAppend. I do, however, want to import all the records from tblAppend that don't have a PremID. There are currently over 120 more records in tblAppend - and they're the ones I want!

Thanks.

 

[plog]

Make a backup of your database, just in case, then do the following in the original:

Create a new query in design view.
Add your 2 tables
link them via PremID fields
Left click that linking line and change the join properties to the one that shows all records from tblAppend and just matches from tblOriginal.
Bring down all the fields from tblAppend that you want to put into tblOriginal
Bring down the PremID field from tblOriginal
Uncheck the Show box under tblOriginal.PremID
Underneath that, in the criteria section, put 'Is Null'
Make the query an append query and run it

 

[Nightowl4933]

Hi plog,

I did that and pretty sure I followed your instructions, but I get an error when trying to run it.

The error is:
The INSERT INTO statement contains the following unknown field name: '[Premises Reference]'. Make sure you have typed the name correctly, and try the operation again.

I've attached a picture, just in case you see something I've missed?

Thanks.

 

[Nightowl4933]

And here's the SQL:

INSERT INTO Original ( [Premises Reference] )
SELECT Append.*, Original.[Premises Reference]
FROM Append LEFT JOIN Original ON Append.[Premises Reference] = Original.[Premises Reference]
WHERE (((Original.[Premises Reference]) Is Null));

Pete

PS: [Premises Reference] is PremID

 

[plog]

Underneath your second column (Premises Reference), remove its Append To: line ([Premises Reference])

You only want to apply criteria to that field, not append it.

 

[plog]

Here's the correct SQL:

INSERT INTO Original ( [Premises Reference] )
SELECT Append.*
FROM Append LEFT JOIN Original ON Append.[Premises Reference] = Original.[Premises Reference]
WHERE (((Original.[Premises Reference]) Is Null));

 

[Nightowl4933]

OK, got that thanks.

I did get another error (screenshot attached), so I'll have to look at the tables again, because of the conversion error!

Thanks,

Pete

 

[plog]

Sounds like you might have duplicates within tblAppend on a unique field in tblOriginal. And/or you have Null values in tblAppend where the corresponding fields in tbloriginal can't be null.

Can you upload your database with those 2 tables in it?

 

[Nightowl4933]

Thanks, plog. All sorted.

I did have duplicates within the tables, so allowing this enabled the merge to work properly.

Pete