Lookup Queries...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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?
Click to expand...
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 <--
 
Stefan Hoffmann said:
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?
Click to expand...
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 <--
Click to expand...
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
 
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
Click to expand...
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 <--
 
Thanks Stefan that has actually worked perfectly!

Andy

Stefan Hoffmann said:
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
Click to expand...
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 <--
Click to expand...
 
Back
Top