Import new records and update existing records

  • Thread starter Thread starter Santa-D
  • Start date Start date
S

Santa-D

I have a table that contains significant information regarding a large
number of assets (Vehicles). I get a series of external data from two
suppliers and rather that running a delete query and transfer
spreadsheet macro I'm wanting to modify the existing data and change it

to the new data, if there is no existing data import as new and if
there are records with no new data then change the reorder status to 9.


I import a report from the Fleet Management Company which doesn't
include a list of sold vehicles, but I do get a list from the Finance
Company which identifies which vehicles are sold but that may be just
as hard as If there is no existing record in table B and it exists in
table A then vehicle must be sold and convert status symbol to 9.


Is there a way that I can do this? I was thinking of running an update

query but I couldn't quite figure it out. Any suggestions would be
gratefully accepted.
 
The answer to this depends on a lot of things that are specific to your
database. You could probably use a series of both Update and Append queries
to do this, but when I've done it, I've used DAO programming code. It
allows me to march through the import data, evaluate each record, then
process accordingly.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "UpdateImportSeekFind.mdb" which illustrates how to do this.
It's not exactly what you want to do, but it does illustrate the process.
See if you can puzzle it out and feel free to ask me questions about it.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Is the TRNCOD field required?
When your dealing with 1000+ records I'm not sure if they need to be
updated or added so the TRNCOD field in the updated spreadsheets
wouldn't be available? Is it possible to bypass this check?

If the record in table1 matches the record in table2 then update else
addnew?
I don't want to delete any unmatched records instead I want to change
the field reorder_status to 9 for any unmatched items.

I think I should be able to do it, I can understand the logic but how
would I run the seek again to see if there are any records in Shoes
that isn't included in the table importShoes?
 
TRNCOD is not required. This sample was built from an actual update I had
to do several years ago and that was just one of the fields that told the
program what sort of update was required. Your update logic may be
different. It is impossible to make an import sample truly generic, so you
have to generalize. Sounds like you've got a handle on it.

I'm not following your last question. Can you clarify?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
I've got three type of records (New, Existing, Old)
Old records are those that already exist in the MAIN table but are not
in the IMPORT table. These records are to be changed to "SOLD" status
or status 9
New records are those that do not exist in the MAIN table but exist in
the IMPORT table thus import the new records.
Existing records are those that exist in both the MAIN table and the
IMPORT table thus the record should only be updated.

Once I have imported the new records and updated existing records what
is the next step to find any old records or vehicles need to be
regarded as sold?
 
Back
Top