Importing fields from separate excel sheets to fill in info in dba

[Sean Frazier]

I've tried importing entire excel sheets, linking sheets, setting up
relationships, and lookup columns, but nothing seems to work out the way I
want it to.

I have an Access database set up where I track information on repair status.
I track each repair individually by requisition number. I need to import
columns from two other excel sheets into my access database from my vendors
that also track my repairs by requisition number.

How can I import the columns from the excel sheets such as received date,
ship date, and contract number into my database while having it fill in the
blank fields of my datasheet that correlates to the same requisition numbers?

I'd greatly appreciate any pointers.

 

[Phil Smith]

I would probably keep seperate tables for each sheet being imported,
then generate/update a master table based on the information from those
other tables.

Ensure that the requisition # is unique to every repair, and identical
character for character between your master datasheet table and the
vendor's import worksheets.

Import each spreadsheet into it's own table, (using a Macro) then run
update or appends to move that information over into your master table
based on the requisition #, filling in dates etc. If you are tyring to
go directly from Excel into your final table, you are going to have
issues. Get all of the Data into access, then let access do your
updates and moves.

If you will have multiple entries for a single repair, especially from
multiple vendors, come up with a system to distinguish between them.
Note which vendor and date for each part of the repair.

I don't know exactly what you are trying to so, it is certainly doable,
but you will benefit greatly from careful consideration as you design
the tables.


Phil

 

[Sean Frazier]

Many thanks. I've established two separate tables for my vendors. How do I
then establish the requisition number column as the unique ID for each table,
and then import the data from the two vendor tables to the blank areas of
their matching requisition number?

 

[Phil Smith]

Not sure what you mean. As I see it, you would have three tables. 1 for
each of your two vendors and their data, which should mirror the data in
the original spreadsheets,and one for the repairs.

If each of the two vendor tables do not use the same format for the
requisition #, then you have a problem you need to fix. Your Repair
table will have a Requisition # which should match the same format as
the others. As I understand it, you already have this Data, (these are
the repairs being worked on,) and are just updating that data with data
from the vendors.

You would have an update query for each vendor file, that would match
the requisition # from Vendor file to the same in the Repair table,
updating dates, costs, and whatever info is in the repair table. etc.

This assumes one repair table record, and one vendor table record in one
vendor table only, for each requisition #. Otherwise, you will want to
work with a table of vednor repairs, so that more then one vendor repair
record could be attached to any particular requisition #.

Question: Are these two vendor spreadsheets identical in structure?
If so, you could simply append them into a single table, and use queries
to generate the data without actually having to update your repair table.

For instance, a typical invoicing scheme would have two tables: An
invoice header table which would have customer and shipping information,
and an invoice detail which would have each line item for the invoice.

If you may have multiple repair entries for any one requisition, I would
have a master repair table, and a detail repair table. I would populate
this detail table fror your two vendor spreadsheets.

Make Sense?