Lookup Queries...

[ajayb]

Hi all,

In an Excel worksheet I have the following data:

4000 3000 2000 1000
400 170 130 90 50
300 160 120 80 40
200 150 110 70 30
100 140 100 60 20

The first row and first columns are measurements the rest of the data is
currency. I use this formula =INDEX($B$2:$F$6, MATCH(C10,$B$2:$B$6,-1),
MATCH(C9,$B$2:$F$2,-1)) so that when I enter 2 measurements I get the correct
price.

Is it possible to do the same in an Access form or query?

Thanks

Andy

 

[Stefan Hoffmann]

hi Andy,

In an Excel worksheet I have the following data:

4000 3000 2000 1000
400 170 130 90 50
300 160 120 80 40
200 150 110 70 30
100 140 100 60 20
The first row and first columns are measurements the rest of the data is
currency. I use this formula =INDEX($B$2:$F$6, MATCH(C10,$B$2:$B$6,-1),
MATCH(C9,$B$2:$F$2,-1)) so that when I enter 2 measurements I get the correct
price.

Is it possible to do the same in an Access form or query?

Yes, you need an appropriate table layout:

[ID], autoincrement
[Column], Long Integer
[Row], Long Integer
[Value], Long Integer

Primary key on [Column] and [Row].


You can now use

DLookup("[Value]", _
"[yourTableName]", _
"[Column] = " & ColVal & " AND [Row] = " & RowVal)


mfG
--> stefan <--

 

[ajayb]

hi Andy,

In an Excel worksheet I have the following data:

4000 3000 2000 1000
400 170 130 90 50
300 160 120 80 40
200 150 110 70 30
100 140 100 60 20
The first row and first columns are measurements the rest of the data is
currency. I use this formula =INDEX($B$2:$F$6, MATCH(C10,$B$2:$B$6,-1),
MATCH(C9,$B$2:$F$2,-1)) so that when I enter 2 measurements I get the correct
price.

Is it possible to do the same in an Access form or query?

Yes, you need an appropriate table layout:

[ID], autoincrement
[Column], Long Integer
[Row], Long Integer
[Value], Long Integer

Primary key on [Column] and [Row].


You can now use

DLookup("[Value]", _
"[yourTableName]", _
"[Column] = " & ColVal & " AND [Row] = " & RowVal)


mfG
--> stefan <--

Hi Stefan,

That was great, thanks for that.

I have a little addition though. Is it possible to amend it so that if I
enter measurements other than those in the row and columns it finds the next
price.

eg if I enter 4000 and 260 it returns 160
if I enter 2000 and 125 it returns 70

Thanks

Andy

 

[Stefan Hoffmann]

hi Andy,

I have a little addition though. Is it possible to amend it so that if I
enter measurements other than those in the row and columns it finds the next
price.

eg if I enter 4000 and 260 it returns 160
if I enter 2000 and 125 it returns 70

Yes, DMin(), e.g

DMin("[Column]", _
"[yourTableName]", _
"[Column] >= " & ColVal)

this gives you

DLookup("[Value]", _
"[yourTableName]", _
"[Column] = " & DMin("[Column]", _
"[yourTableName]", _
"[Column] >= " & ColVal) & _
" AND [Row] = " & DMin("[Row]", _
"[yourTableName]", _
"[Row] >= " & RowVal))

Evalute it, as i'm not quite sure if it works for all cases.


mfG
--> stefan <--

 

[ajayb]

Thanks Stefan that has actually worked perfectly!

Andy

hi Andy,

I have a little addition though. Is it possible to amend it so that if I
enter measurements other than those in the row and columns it finds the next
price.

eg if I enter 4000 and 260 it returns 160
if I enter 2000 and 125 it returns 70

Yes, DMin(), e.g

DMin("[Column]", _
"[yourTableName]", _
"[Column] >= " & ColVal)

this gives you

DLookup("[Value]", _
"[yourTableName]", _
"[Column] = " & DMin("[Column]", _
"[yourTableName]", _
"[Column] >= " & ColVal) & _
" AND [Row] = " & DMin("[Row]", _
"[yourTableName]", _
"[Row] >= " & RowVal))

Evalute it, as i'm not quite sure if it works for all cases.


mfG
--> stefan <--