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

 

[Pat Hartman]

You need to import the lookup table into Access. Then import the main data set. In Access, you use queries to join the two tables (use a left join from the main table to the lookup, just in case) to get the lookup value.

 

[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

 

[Pat Hartman]

If the data is organized as a table with a single header row followed immediately by the data rows, you can use the TransferSpreadsheet Method/Action to import the data with a single VBA statement. If the two sets of data are on the same tab it becomes more complicated but it can be done easily enough if you make them each named ranges.

Once the two sets of data have been imported, you can create a query that joins them to show the lookup data. Using a form, you would have a query that selects the main table and one of the controls (the lookup) would be a combobox that will "lookup" the reference value.

The people using the database don't need to know anything about how Access works. That's your job. You make forms and reports and all they do is push buttons and enter data.