vlookup from excel equivalent function in access

[bartmet]

I am trying to lookup a value in a table that has lengths in the first
columns and withdths across the field names and a value at the intersections
of those. this is a table for time it takes to drill a peice of sheetmetal
with a certain diameter hole in a certain thicknes metal. it has me boggled.
I can do it in excel with vlookup() function.

 

[Gina Whipp]

I THINK you wand DLookUp()
DLookUp("FieldYouWantToFind","Table/QueryWhereTheDataIs","FieldInTable=FieldOnForm")

That end part is different whether you are dealing with text or number
field.

 

[bartmet]

I have tried to use the dlookup and that seems to work if the lookup field is
static but I want ot be able to vary the field(or select a called for
field)called for ithin the record selected

 

[Duane Hookom]

I expect your issue is taking a spreadsheet attitude into a relational
database. If your "FieldYouWantToFind" is dynamic then consider normalizing
your table to where your where condition would include values for length and
width or whatever.

It would help if we knew your table structure with field names.

 

[bartmet]

this should show up ok if you copy and paste the following into excel. I need
to look up the time to drill a hole based on diameter of the hole and
thicknessof the steel if the hole is 0.5 inches in diameter and 5/8 inch
thick the time is 1.25 minutes from this sample sheet.

"column diameter(in)/row headers
depth(eigth inch increments/values(minutes)" 1 2 3 4 5 6 7 8
0.125 0.25 0.25 0.25 0.25 0.5 0.5 0.5 0.5
0.25 0.25 0.25 0.25 0.5 0.5 0.5 0.75 0.75
0.375 0.75 0.75 0.75 1 1 1 1 1.25
0.5 1 1 1 1.25 1.25 1.25 1.25 1.25
0.625 1.25 1.25 1.25 1.625 1.5 1.5 1.5 1.625
0.75 1.525 1.525 1.525 1.975 1.775 1.775 1.75 1.9
0.875 1.8 1.8 1.8 2.325 2.05 2.05 2 2.175
1 2.075 2.075 2.075 2.675 2.325 2.325 2.25 2.45
1.125 2.35 2.35 2.35 3.025 2.6 2.6 2.5 2.725
1.25 2.625 2.625 2.625 3.375 2.875 2.875 2.75 3
1.375 2.9 2.9 2.9 3.725 3.15 3.15 3 3.275

 

[Duane Hookom]

You have committed spreadsheet with your table. I assume your 1 2 3...are
fields? These should be data values stored in a field. It's hard to tell but
it looks like you should have about 100 records from the information you
provided.

 

[bartmet]

no there are only 8 records and the 1,2,3 are field names like I said if you
copy the array I put in there and paste it into excell it should come out as
a table.

 

[Duane Hookom]

Like I have said at least twice, you should be normalizing this spreadsheet
into at table where 1,2,3... are values in a field rather than fields in a
table.

 

[bartmet]

thanks for your patience with me I guess the term normalizing threw me. what
do you mean by that i looked it up in Access and got nothing. I have a dozen
of the type of tables to do.

 

[Duane Hookom]

Jeff Conrad has some excellent links to various web resources regarding
normalizing
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101