Two dimensional SQL table lookup (1 Viewer)

Hello, I have been struggling to find a solution to a problem for an access database application I am working on.
I have an imported table from excel which I need to query in two dimensions. I have attached an image as a sample of the table structure.
The Hpa column contains a series of values and the rest of the column headings are temperature values 1 to 12.
From a form, the user will input the Hpa value and a temperature value which are then assigned to two public variables p & t.

I need to find a way to reference the value in the field that is intersected by the horizontal and vertical matrix and assign it to a third public variable r.
For example, with an Hpa value of 1012 and a temperature value of 7, the result should return a value of 6536.

Interestingly, this is fairly straightforward in excel by using the Index Match functions but I am stumped in how to do similar in Access 2016.

Any help would be greatly appreciated, thank you.

You need to restructure your data with fields for HPA, Temperature and Result. Each combination of HPA and Temperature needs to be a separate record.

Hi Max. I agree with Galaxiom about your table structure, but you should also be able to use DLookup() to get your value. For example,

Hello Galaxiom and theDBguy,

Thank you both for your solutions. I was hoping not to have to restructure the table but find a nifty function to get the information I needed.
However, I have taken your advice and restructured the 5132 records :banghead:!
Now I have just used a simple recordset interation to find the value using an SQL string.

It was a good lesson to learn to treat Access tables as very different objects from an Excel table.

Thanks again for your help

MaxTorque said:

Hello Galaxiom and theDBguy,

Thank you both for your solutions. I was hoping not to have to restructure the table but find a nifty function to get the information I needed.
However, I have taken your advice and restructured the 5132 records :banghead:!
Now I have just used a simple recordset interation to find the value using an SQL string.

It was a good lesson to learn to treat Access tables as very different objects from an Excel table.

Thanks again for your help

Click to expand...

Hi. Glad to hear you got it sorted out. Good luck with your project.

It was a good lesson to learn to treat Access tables as very different objects from an Excel table.

Click to expand...

Just a little bit of positive reinforcement, MaxT - the two utilities are radically different and it is a true paradigm shift when you finally recognize the difference. But it is important to learn that difference - and you have done so. That is a GOOD thing.

Here is the difference in simple terms. Every cell of an Excel spreadsheet is its own entity. If you want to interact with another cell, you use a cross-cell function. Nothing about Excel (the utility overall) will automatically treat cells according to some global rule. Everything you do affects individual cells even though the utility has a way to allow you to do the same thing to ranges of cells.

With Access, even if there is no data in the table, it can be defined and you can know what it will do with the data once it has it. Access defines data ahead of time. Excel defines data when you load it to a cell, and you can change your mind at any time.

It's a tough lesson but valuable.