I'm sure there's a how-to out there, I just didn't see it -- could someone point me to where to find information on writing queries to act like a lookup field? I have a field in a table that's a number, and I'd like the query to pull information from another table to report it as the corresponding label instead of the ID. I'm pretty sure I have to use a join, but when I set the relationship, suddenly the query starts reporting nothing at all, so I'm clearly doing it wrong.

The tables look like this --
Table Interns - College (# value)
Table College - # is primary key, then label in separate field

Hi,

Hope you're not setting up the lookup at the table level. It is better to do that at the form level. The query might look something like this:

SELECT ID, LabelField
FROM CollegeTable

Then, set the Column Widths property of the dropdown to 0".

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Nope, I've learned my lesson! That's why I want to try and do the join thing at the query level. Thanks for the response!

Hi,

If you're using the query as a "lookup" for a combobox, I don't see the need for the JOIN statement. Is this for a cascading combobox?

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

I'm probably using the wrong terminology... and as it turns out, I think I need a little more guidance. If there's a wiki page for this, feel free to direct me there instead... I'm just used to using lookups in tables, and so this is my first time going about it the right way. I'm attaching a screenshot of what I tried... any thoughts?

Also -- the error that I get when I try and view the results of the query is "Type mismatch in expression."

This post has been edited by lntrullin: Mar 2 2012, 03:42 PM

Hi,

I still wouldn't do the lookup at the query level. We usually recommend that you don't let your users have direct access to tables and queries. The safest way to protect your data is to use forms for all data interactions.

Again, a lookup is usually a reference to "all" the records in one table. So, using the JOIN is usually not necessary.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)