Update table with new records from Excel file (1 Viewer)

[tmyers]

I am trying to maintain my customer list (tblCustomers) and adding in new accounts from an excel file exported from our main DB. I imported the file into a temp table, but for the life of me can't remember how to append only new records from the temp table to the permanent table. Can one of you wonderful people offer guidance?

For the record, for various reasons I am unable to use the excel file as a linked table and just overwrite it when an update is needed.

 

[tmyers]

Remembered how to do it. Made an unmatched query then used that query to append the new records to the table. Guess I just needed a few more drinks of coffee!

 

[arnelgp]

a single typo error can cause unmatch query to include it even when the account already exists.

 

[tmyers]

That is fair. I am basing the unmatched on account numbers which are main DB generates and are unique and are not reused.

 

[tmyers]

To slightly add to this, how can I update a column with data from another column in the same table?

With the same data I just updated to add in new accounts, I also added in an account lookup column, this column will almost always contain the same number as the account #, but in a couple instances (like 50 out of nearly 2k) the number will be different.
I already set those 50 to what they need to be now I just need to mass update column LookupAccount to equal Account# where LookupAccount is either 0 or null. How would that query work? I have never updated a table using itself and I really don't want to spend the next 2 hours manually entering nearly 2 thousand account numbers

 

[Gasman]

Well you just said the criteria yourself?

 

[arnelgp]

Update [yourTableName] Set [LookupAccount] = [Account#] Where Nz([LookupAccount], 0) = 0;

 

[tmyers]

In the query editor would I just set the field then in the update to make it say, tblCustomer.Account# and criteria ISNull OR 0?

Edit:
Ah, so it is that simple

 

[arnelgp]

copy and paste the query i post in SQL view.
then edit the tablename and the fieldnames to the correct one.

Nz([LookupAccount], 0) = 0, means same thing (Is Null or 0).

 

[Gasman]

In the query editor would I just set the field then in the update to make it say, tblCustomer.Account# and criteria ISNull OR 0?

Edit:
Ah, so it is that simple

I tend to make it a Select query first, to make sure I am updating what I think I should, then change to update.
I also take a copy of the table first, just in case.