Extracting lowest price from 31 downloaded Excel vendor price lists

[larrynew]

First, a quick thanks to those on this forum that helped me last July get started switching from Excel to Access. Progress has been made and I'm now tracking inventory quantities daily in my Access file that's linked to 31 Excel spreadsheets. All I know I've learned from reading this forum and my trusty Access 2003 for Dummies.

I'm now working on the pricing part of the puzzle. I download 31 Excel spreadsheets (different than the inventory spreadsheets above) for each warehouse of my vendor. I don't control the spreadsheets so I have to work with what I get. Each spreadsheet has the usual fields for item_id, description, weight, cube, price, etc. The item_ids are all the same but the prices vary and not each warehouse carries all the same products.

Is it possible to find the lowest price for each item? It was easy to link the inventory quantity spreadsheets to my Access db and do a query to get a total for each product. Please point me in the best direction. Do I use a crosstab query?

Thanks,
Larry

 

[raskew]

Hi -

Can you post several of these spreadsheets?

Bob

 

[larrynew]

Bob,

Here are 3 of the files. I only included the first 800 or so items of each file to keep the file sizes small.

Thanks,
Larry

 

[pbaldy]

Bob appears to have gone off duty. I think this will be easiest if all the data is in one table. If you can't append the spreadsheets into one table, you can use a UNION query to pull them together:

SELECT Item, Price, Location
FROM FirstOne
UNION ALL
SELECT Item, Price, Location
FROM SecondOne
...

From there it's fairly simple to get the lowest price and location. This may help on that:

http://www.baldyweb.com/LastValue.htm

 

[raskew]

Damn, Paul -

You're just too quick! I wasn't even off duty, but rather attempting to resolve a problem from another forum.

Best wishes - Bob

 

[pbaldy]

Sorry Bob; your light was off, and based on the time I figured it was quitting time for you! Add any thoughts you may have. I can't think of an easier way to deal with 31 tables.

 

[larrynew]

Paul & Bob,

Great, works like a champ! Thanks! I'm starting to understand the whole Normalization of data thing vs. spreadsheet thinking.

After playing with UNION ALL, I scrapped my ugly method of getting the sum of inventory from the other 31 spreadsheets and switched to UNION ALL. Much faster and easier.

Thanks again,
Larry

 

[pbaldy]

Glad it worked for you Larry. I would still look at adding all the records to one table if possible. The UNION query is a great tool, but if your number of tables changes then you have to change the query. Having the data in a table would make it more flexible.

 

[larrynew]

Paul,

I don't have any control over the downloaded spreadsheets and they change so often that manupilating them would be much too time consuming.

My next chore will be finding the MIN price but only from the locations that have the item in-stock.

Thanks again,
Larry

 

[pbaldy]

Presuming there's something in the data to signify that, you should be able to add a WHERE clause to exclude the out-of-stock items (presumably in each section of the UNION query).