VLookup Alternative

[rufus05]

All,

I am trying to do something similar to a VLOOKUP from Excel.

I have a table where latittudes are the column names and longitudes
are the row name (1st column).

I have a latitude and longitude and find the cell where they intersect
in the table. In Excel this is easy thanks to the VLOOKUP but I can't
find anything like that in Access.

I am using 2003.

Example:

0 30 31 32 33 34...
81
82
83 562
84...

In the example chart above I want to find the 562 where the 32 and 83
intersect.

Thank you for your help!

 

[S Davis]

All,

I am trying to do something similar to a VLOOKUP from Excel.

I have a table where latittudes are the column names and longitudes
are the row name (1st column).

I have a latitude and longitude and find the cell where they intersect
in the table. In Excel this is easy thanks to the VLOOKUP but I can't
find anything like that in Access.

I am using 2003.

Example:

0 30 31 32 33 34...
81
82
83 562
84...

In the example chart above I want to find the 562 where the 32 and 83
intersect.

Thank you for your help!

Thats a good question. Im curious about this too.

I know Access2007 has vlookup's that are able to be built into a
single datapoint (!)... hugely powerful.

But as far as Access 03, Im not sure. Im willing to bet it will be
less a function and more how you set up your tables. Perhaps a
crosstab as well. (guessing)

 

[John W. Vinson]

All,

I am trying to do something similar to a VLOOKUP from Excel.

I have a table where latittudes are the column names and longitudes
are the row name (1st column).

That's a good spreadsheet design - but it is NOT a properly structured
relational database table. Storing data in fieldnames *IS WRONG* and will
cause you no end of trouble.

If you want to do this operation in Access, you would do much better to use a
table with three fields: Longitude (83), Latitude (32), Value (562). (What
you're doing in a fjord on the northern tip of Greenland has my curiosity up
I'll admit!)

To do this using your "spreadsheet" table will require code to determine the
fieldname, and then DLookUp to find the row:

strLat = <determine the desired latitude>

I have a latitude and longitude and find the cell where they intersect
in the table. In Excel this is easy thanks to the VLOOKUP but I can't
find anything like that in Access.

I am using 2003.

Example:

0 30 31 32 33 34...
81
82
83 562
84...

In the example chart above I want to find the 562 where the 32 and 83
intersect.

Thank you for your help!

John W. Vinson [MVP]

 

[rufus05]

Thats a good question. Im curious about this too.

I know Access2007 has vlookup's that are able to be built into a
single datapoint (!)... hugely powerful.

But as far as Access 03, Im not sure. Im willing to bet it will be
less a function and more how you set up your tables. Perhaps a
crosstab as well. (guessing)- Hide quoted text -

- Show quoted text -

Thanks for the help, but I'm not very good with Access. Do you have
any more specifics about how this might be done or even what a
crosstab will give me?

Thanks