Data consolidation

[PVSyemya]

Hi,

I'm new to Access 2007 and have a couple of questions regarding the
construction of a DB. I've inherited a bunch of files both hardcopy and
electronic that I'm trying to consolidate into a single inventory DB The
electronic files are mostly excel spreadsheets. I've created a record set
that contains several tables that make up a master inventory list. What I
mean by this is that the Access import wizard for excel suggested that I
create a separate table for items that were repeated frequently. For instance
there are only 4 location that the hardware can be in so in the master
inventory list there is a lookup column that points to one of the four
locations for each item.

At this time the master list contains about 80% of the items we are tracking
but there are holes in the data. For instance a serial number field may be
missing for one record and another record may be missing data in the
quantities field. The e-files and hardcopies that I have supposedly contain
all the missing data as well as hardware items that are currently not in the
DB.

The excel files I mentioned are essentially forms with meta data at the top
of each spread sheet (i.e. date, address, point of contact, etc..) basically
things that I don't want in the DB.

So my question is how can I import the excel files and capture only the data
regarding the hardware itself, no metadata? I've been playing around with
TransferSpreadsheet macro but since the number of hardware items listed below
the metadata varies I end up with a lot of blank records. I've tried a delete
query with the following criteria: ="" but it finds nothing even though there
are several blank records in the table.

The next question I have is how to merge the excel data into the inventory
list such that it updates records based on a part number or description and
if there is no match with either of those criteria make a new record based on
teh imported information?


Thanks in advance
B

 

[Arvin Meyer [MVP]]

As for the second question I'm not sure I'm following you. With the excel
files I have there will always be a part number or description. I'm
importing this into a table called Imported using the VBA code above
(hopefully) and I need to compare this table against the Master Inventory
list. If a part number ( or description) from Imported exists in the
Master
Inventory I need to fill in any blank fields for that particular record.
I
can't replace a record because the data in the Master table is suppose to
be
the latest and greatest and the excel file may have old data. I know it's
complicated but I'd prefer to replace blank fields if a match is found
between the Imported and Master tables. If the part number or description
from Imported does not match a record in Master Inventory then I need to
create a new record in Master using the data from Imported. Your statement
about Appending the remaining records makes sense and I believe that I
know
how to do that with query criteria somewhat like the following:

You may have to run an update query, 1 field at a time, testing for null as
a criteria before doing the update. Depending upon how many fields you have,
this could be simple, or quite time consuming.