DLookup in Query

[mfrey40]

Hi all,

I'm a brand new Access user and I'm having a lot of trouble understanding how to correctly program a DLookup expression in a query. I'm entering everything from the user interface. Sorry in advance if this info is already on the web/forums, like I said I'm new and just getting my feet wet.



Basically, I've got a number of tables in my sandbox database, including:
'z_Play Data'
'States'

'States' contains a list of states with primary keys numbered 1-51 and a list of state abbreviations in the second column.

'z_Play Data' contains a "DL State" field, with the entries listing the 'State' keys (1-51, each record has only 1 key). I'm trying to create a DLookup in a query that takes the state number for a particular record on the 'z_Play Data' table and looks it up on the 'State' table to return the correct state abbreviation.

I've come up with the following so far:

DLookUp("option","States","option_id=8")

In this formula, "option" is the primary key field on the "States" table. "option_id" is the field that contains all the state abbreviations. When I enter this expression in the query, it returns "DE" which is correct for State #8.

What do I need to change to get the formula to return the abbreviation for the State ID specified in the "z_Play Data" table?

Thanks in advance,

Matt

[pbaldy]

That would be the highly inefficient method. What you want to do is join the two tables in the query. Add both tables to the design grid. If a join is not created automatically (a line between the option_id field in each table), you can create one by clicking/dragging. That enables you to return fields from both tables in your query.

[mfrey40]

Wow that was easy. Thank you so much!

[pbaldy]

Happy to help, and welcome to the site!