How do I update existing records within a database?

[OverMyHead]

To track progress on a job I export information from one program and then
import it into Access.

In Access I have a field called “Job ID†as the unique key and it is set to
not allow duplicates. So, when I import the records if a record with that
unique key exists it will not import anything from that record.

People on my team are going through each record and making changes to the
records in Access.

Also, throughout the day pieces of information will change with any given
record in the program where the information originates.

What I would like to do is be able to import new records and update existing
records without replacing them in Access.

Does this make any sense? If so, how do I do it? I thought about update and
append queries but neither seem to do what I need them to.

 

[Jeff Boyce]

Are you saying that you have JobIDs on your non-Access data? If so,
importing that "raw" data, as is, into Access could be your first step.

Then you could consider using an update query that updates your Access table
values to those showing in the "raw" (import) data, joining on JobID (i.e.,
update the Access records when the import data JobID matches).

You'd still need to use an append query to get the "new" records added to
Access.

--

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[OverMyHead]

Thank you for your help Jeff! I think I understand what you mean by combining
the data.

However, what is the difference between just importing the data and letting
Acess kick out the duplicates and an append query?

 

[Jeff Boyce]

In Access, "importing the data" means making a copy of the data in Access.
Importing doesn't, by itself, update anything already in Access.

While it may be possible to import data and append it to an existing Access
table, this still won't help with the updating part, and very often what
would be imported is not particularly well-normalized. Access is optimized
for well-normalized data, so you're better off either linking to or
importing "raw data", then using queries to 'parse' the raw data into your
more permanent (and well-normalized) Access table structure (that handles
the append part). Then you use update queries to update those records you
already have in Access.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP