Importing from Excel with lookup values?

[eTom]

I'm not sure if this is the right section or if the subject is worded correctly, but I am building a small database to automate the process of producing sales reports for our sales staff using data from our customers (distributors). They provide us with Excel spreadsheets with detailed sales data for our brands at THEIR customers (retail stores).

The problem is that many stores receive from two distributors, and each distributor of course has different "customer numbers" for the store. I've built the database with the following:

tblStores (containing the list of stores)
StoreID
Distributor1StoreID
Distributor2StoreID
Distributor3StoreID

tblSalesData (containing the monthly sales per store, by brand)
StoreID
Brand1Sales
Brand2Sales
...
etc

When the distributor provides the spreadsheet, they use their Distributor1StoreID (or 2 or 3, depending on the distributor). I want to import it to tblSalesData but would need to lookup the StoreID from the tblStores during the import, using Distributor1StoreID, etc.

Is that even possible or am I wasting my time?

Thanks in advance,

Eric

 

[eTom]

Hmmm... I think that is a little above my understanding and I wasn't sure how to automate it. The people using the database need it as easy and one-click as possible.

I ended up creating a VBA script that opens a spreadsheet and imports it row-by-row into the table, assuming it doesn't detect any faulty data.

Here's to hoping it ends up working in the long run!

Thanks again,

Eric