DATABASE pulling values from Spreadsheet

[Anon]

I know just enough to be dangerous.

We have our business system set up to pull database queries out of the
system, say for example, raw material part numbers, description and average
cost or last cost as well as avg usage.

I would like to set up an Access database to contain recipies (plastic
blends for different applications)

The current cost tool that sales has requires historical cost data from the
system in order to cost products. Small runs tend to inflate cost
information (lots of down time, poor yields)

The database would have each ingredient, amount, cost and would look up the
values from one of these queries (spreadsheet) that updates with actual cost
information from the system each time the file is opened up (refresh upon
opening)
Thats my problem- how to populate a field in Access table with a value
looked up in an external spreadsheet. (lookup by raw material part number- a
six digit number)

Thanks
Paul

 

[John W. Vinson]

Thats my problem- how to populate a field in Access table with a value
looked up in an external spreadsheet.

File... Get External Data... Link to link to the spreadsheet; create a query
Joining the linked spreadsheet to your query by the six-digit (text) ID.

I'd really suggest moving to Access as the storage repository for your data.
Excel's a great spreadsheet, but a spreadsheet is not a database!

 

[Anon]

Thanks.

Our mfg data is in foxpro, and the spreadsheet we have is the tool for
costing, and my charter is to take someone else's spreadsheet, reverse
engineer and upgrade. My first task is as listed- to figure out costs for
which historical does not exist. Best way forward is an Access db for
recipes and then I can compare theoretical to actual (where actual exists)
and figure batch size effect upon costs).

The DB is the best option for "recipes", and we will use an Acces query
into Excel (with refresh) to pull the data into the costing spreadsheet via
"vlookup".... (I assume Access has an equivalent for 'get external data'.

Thanks again!
Paul

 

[John W. Vinson]

The DB is the best option for "recipes", and we will use an Acces query
into Excel (with refresh) to pull the data into the costing spreadsheet via
"vlookup".... (I assume Access has an equivalent for 'get external data'.

Access can communicate with Excel in a variety of ways; see the VBA help for
"TransferSpreadsheet" for one. You can also use VBA code to actually run Excel
and directly connect to spreadsheets, or you can use VBA macros in Excel to
hook into an Access table... that's outside my area of skill though!