How to design an Access Column then Row Reference Table -Currently in Excel

[Kevin Broecker]

First of all thanks for taking a look at this question. Fundamentally I may be looking at this all wrong but here is what I am trying to accomplish. Currently I receive a physical liquid product from a customer that is analyzed for temperature of the liquid and % ethanol concentration. Using those two points of data I use VLookup and Match to find a volume correction factor in an excel table. This is the formula in Excel

=IF(H592>0,((VLOOKUP(H592,'Temperature correction table'!$A$20:$AQ$60,MATCH('2016'!I592,'Temperature correction table'!$B$19:$AQ$19)+1)*'2016'!J592)+IF('2016'!H59 2=0,'2016'!J592)),J592)

I want to be able to replicate/automate this process within access but I can not figure out how to build the table. My existing excel spreadsheet is designed with Temp Deg F in the column headers starting at 18F and increasing 1 Degree up to 100F. The row headers at %Ethanol starting at 0 and concluding at 100 in 5 % increments (although it could be every 1% if that makes it easier to build).

I should mention before the obvious is noted, yep I really am using 2003. It a licensed version on Citrix Server and the entire company's SAP type system is built on this version. Feel free to poke fun at it. We all do.

Thanks again.



Kevin

[pbaldy]

In a database you'd have a table with 3 columns: temperature, ethanol and the correction value.

[Kevin Broecker]

Paul,

Thanks for replying. Do you know if there is a simple way to bring the data in to Access from Excel based on how I described the existing Excel spreadsheet?

Kevin

[pbaldy]

Presuming you can link to the Excel file, I'd run a series of append queries, appending a column each time. You could also create a UNION query, but not worth it to me for a one-time thing.

[Kevin Broecker]

Thanks Paul.

[pbaldy]

No problem Kevin, and welcome to the site by the way! Post back if you get stuck.