Looking up value in table?

[Brand-X]

Hey I'm looking to see if there is a function similar to vlookup from excel in access? I have a query set where I'd like it to look up [value a] on [table b] and return the result from the same row as [value a] in [table b] but from a different column (hmm, I think that was poorly said, maybe I'll give a quick example)

1 a
2 b
3 c

lets assume the above is [table b] so if [value a] was 2 I'd like it to return b in my function, or if it were 3 it'd return c

sorry if this is over or under complicated, I'm completely self taught so I'm not exactly up with what's basic and what's complicated

-Brandon

 

[RV]

Why?
I suspect you've got a specific reason...

RV

 

[Brand-X]

Is that a "No it can't be done?"


-Brandon

 

[Jon K]

The function is DLookup().

 

[Pat Hartman]

Create a query that joins tableA and tableB. Choose the columns that you need from both tables. The following query joins the Order table with the customer table to obtain the customer name.

Select O.OrderID, O.CustId, C.CustName
From Orders as O Inner Join Customer as C on O.CustID = C.CustID;

 

[Brand-X]

Hmm, well I've taken a look at the DLookup stuff on the net *makes guesture of plane going over own head*, and as for the 2nd option presented here, I can't say I really understand it.

*Sigh* I might just have to go back to Excel and see if I can do it there, Excel I'm pretty good with, and stuff like this is extremely easy there.

-Brandon

 

[Jon K]

I agree with you. The syntax of the domain aggregate functions such as DSum(), DLookup(), DCount(),DMax() can be a pain, because the criteria requires the building of a text string.

But sometimes these functions can be very useful. Take a look at:-
http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=102501#post102501
There DCount() is used to rank the records while DLookup() is used to find the field value of the previous record.