Merge data into database

[Darhl Thomason]

My company just restructured our regions & districts. I have both of these
fields in my main table as RegionID and DistrictID. Both of these relate
back to other tables that contain the RegionName and DistrictName. i.e.
tblStoreData has

I have a spreadsheet that has the new/updated information in it and I want
to import/merge that into my database so I don't have to manually change all
of the data. I have tried to run the Get External Data wizard, but that
fails because of Primary Key violations. This makes sense to me, but I
don't know the best way to do this data merge.

Any and all suggestions would be highly appreciated.

Thanks!

Darhl

 

[Jeff Boyce]

Darhl

It sounds like you may need to take two steps, rather than one.

The first step would be to either link to or import the "raw" new data.
This should be able to happen without regard for primary key.

The second step would be to use one or more queries to parse the data from
the raw imported form into your more permanent table structure (which I
assume is well-normalized).

Regards

Jeff Boyce
<Office/Access MVP>

 

[Darhl Thomason]

Thanks Jeff,

The first part is easy! The second part I've never done before. My main
table structure is well normalized. If I could have some guidance on how to
do the updates I would be forever in your debt ;-)

Darhl

 

[Jeff Boyce]

Darhl

Generically, what you'll be doing is mapping which of the data elements in
your import file need to go to which fields in which tables in your
database.

Note that you and I both said "normalized", but we may not be using the same
definition.

If your "map" is that you have a single table in Access with all the fields
in your import, you'll probably use a single query to do cleanup before
either appending (Append query) or updating (Update query).

If your map includes some import fields going to one Access table, some
going to another, and still others going to a third ..., you'll need a query
for each of those.

Basically, what you are doing is grabbing the pieces you need from the
import data and stuffing them into their (respective) fields in your Access
tables.

Does that get you closer?

Regards

Jeff Boyce
<Office/Access MVP>

 

[Darhl Thomason]

I think our definition of normalized is probably the same, or at least
similar. My db has multiple tables with IDs pointing from the main table to
the sub tables. Example, main table is tblStoreData, among other fields,
has RegionID, DistrictID, etc. These are numeric values that relate back to
tblRegion which has the fields RegionID and RegionName and tblDistrict which
has the fields DistrictID and DistrictName.

The data I'm updating has my main table primary key, plus numeric values for
RegionID and DistrictID. Our Regions and Districts changed, and I got the
spreadsheet from the Admin Assistant and I don't want to have to update them
by hand in my db, so I created a new table (per your suggestion) with the
new data, now all I have to do is merge it into the main table to update the
values.

I'll give it a shot, I think I understand what you want me to do. I'll play
with it on my backup/test copy of the db and see how it goes before I update
my production db.

Thanks for your help!

Darhl

 

[Darhl Thomason]

Thanks Jeff,

I appreciate your help. I was able to get my database updated.

Darhl