Newbie needs help: how to fill in new table with any unchanged info from existing table

[Paul Simon]

I'm an inexperienced "newbie" to Access and construct new databases
very seldom. So I just haven't a clue how to do this.

We have an in-house Access customer database. Periodically,
information is submitted to us from outside sources with changed or
added data. This information is submitted to us in an Access database
with the exact same structure as our in-house database. However, only
the records which have something changed or added are included and
only the changed or added information is indicated. In other words,
fields whose data is unchanged are left blank - only the fields whose
information has been altered are filled in.

I must now take that database of changed or added information and fill
in all the blank fields with existing data from our in-house database.

For simplification, let's use an example of a database with Name,
State, Phone# and Marital Status


In-House Database:
------------------
Name State Phone Married
---- ----- ----- -------
Joe Smith CT yes
Paul Simon CT 555-1212
Bob Wilcox NY 555-2121 no
Harry James NY 555-5555 yes
Ralph Edwards CT 555-4321 no
Barbara Jean NY 555-1919 yes
Iona Karr CT 555-8531 yes


Database of Changes as Submitted:
---------------------------------
Name State Phone Married
---- ----- ----- -------
Joe Smith 555-0033
Paul Simon no
Bob Wilcox yes


Database of Changes as Completed by me, filling in unchanged info:
------------------------------------------------------------------
Name State Phone Married
---- ----- ----- -------
Joe Smith CT 555-0033 yes
Paul Simon CT 555-1212 no
Bob Wilcox NY 555-2121 yes


Can this all be done from within Access rather than exporting to Excel
to do elaborate Vlookup formulas? (There are about 60 fields in the
actual database.)

 

[KARL]

Use update queries with IsNull as criteria. Save the
queries and have a macro run them.

 

[John Vinson]

We have an in-house Access customer database. Periodically,
information is submitted to us from outside sources with changed or
added data. This information is submitted to us in an Access database
with the exact same structure as our in-house database. However, only
the records which have something changed or added are included and
only the changed or added information is indicated. In other words,
fields whose data is unchanged are left blank - only the fields whose
information has been altered are filled in.

Hm. This is NOT how I'd set things up to do this. For one thing, this
approach makes it impossible to blank out a field which contains data!

I must now take that database of changed or added information and fill
in all the blank fields with existing data from our in-house database.

An Update query will work to update existing records. You will need to
open the "target" database, and use File... Get External Data... Link
to link to the updates database. Create a Query joining the table that
you want to update to the linked table; change it to an Update query;
and update each field to

IIF([Customers].[fieldname] = [Updates].[fieldname],
[Customers].[fieldname], [Updates].[fieldname])

This will be VERY VERY SLOW since it must call a function on every
single row! You may want to investigate Replication as an alternative
approach: there's a good overview at http://www.trigeminal.com.

 

[Paul Simon]

Karl and John,

My thanks to both of you for your time and help - I appreciate it very
much.

John, your revelation about not being able to blank out a field which
contains data has brought to light a major flaw in our system that we
hadn't thought about. Thanks very much for bringing that to my
attention.

I can see that aside from the suggestions from both of you, we're also
going to have to review our basic procedures on how changed or added
information is sent to us.

Again, many thanks,
Paul

We have an in-house Access customer database. Periodically,
information is submitted to us from outside sources with changed or
added data. This information is submitted to us in an Access database
with the exact same structure as our in-house database. However, only
the records which have something changed or added are included and
only the changed or added information is indicated. In other words,
fields whose data is unchanged are left blank - only the fields whose
information has been altered are filled in.

Hm. This is NOT how I'd set things up to do this. For one thing, this
approach makes it impossible to blank out a field which contains data!

I must now take that database of changed or added information and fill
in all the blank fields with existing data from our in-house database.

An Update query will work to update existing records. You will need to
open the "target" database, and use File... Get External Data... Link
to link to the updates database. Create a Query joining the table that
you want to update to the linked table; change it to an Update query;
and update each field to

IIF([Customers].[fieldname] = [Updates].[fieldname],
[Customers].[fieldname], [Updates].[fieldname])

This will be VERY VERY SLOW since it must call a function on every
single row! You may want to investigate Replication as an alternative
approach: there's a good overview at http://www.trigeminal.com.