Lookup columns

[richard flack]

I have a table (Cellar) with a lookup column (Region) that queries another table (Regions). I would like the query to select only those rows which match on another field (Country).

In Access 2003 there was a way to do this on a form with an expression of the form

Regions.Country = [Forms]![EditCellar]![Country]

- EditCellar was the name of the form.

Id like to do away with the form (and am now in Access 2007). Is there a way to do this directly in the lookup column in the Cellar table definition?


Ive tried several variations but get get "Type mismatch in expression"
eg:
SELECT Regions.Region, Regions.Country, Cellar.Country FROM Regions, Cellar WHERE Regions.Country=Cellar.Country;

[June7]

Need JOIN clause instead of WHERE to join Regions and Cellar on Country fields. Use the query designer to create join link between the tables. If you set up these Relationships, query designer will automatically set up the join when building.

[boblarson]

Just so you are aware. You should NOT be using lookups directly in the tables. You really should use them only on FORMS. Read this to see why:

http://www.mvps.org/access/lookupfields.htm

[June7]

Good catch, Bob, and I agree. I NEVER set lookup in tables. As a developer, I want to see the real value when I look at tables. Users should not work directly with tables. Only benefit from setting in table is that when you drag this field from field list when creating form, the property settings will apply; this is also true for reports. I just set these properties in control on form when I want them.

[richard flack]

Thanks to both of you.
I (think I) understand some of the concerns Bob expresses.
In this case I am the project owner, project manager, designer, analyst, code and user lol.
So Im trying to keep it simple and was hoping to avoid using a form.

One thing I want to be clear on ...
If I DO use a form, does the table still store just a pointer into the regions table as opposed to the value of the Regions.Region field at the time of input? If the region entry gets updated, I would want the Cellar table to reflect that. There is nothing worse than having "stale" data in column.

Im a real neophyte at this, but it seems to me that sorts or selects failing if the table uses lookup column is a deficiency in Access not 'following through' on the ID to use the ultimate value.
Is there an "ID" or pointer data type for a field?

[June7]

Forms can be bound or unbound. Bound means they have a RecordSource. A RecordSource can be a table or a query that joins tables/queries. Then data controls (textbox, combobox, listbox) can be bound or unbound. Bound means they have a ControlSource that is a field of the RecordSource. Any entry/edit in the data control is passed directly to the bound field. Yes, the key (pointer ID) would be saved in the data table. Use queries that join the data table with the lookup table so as to retrieve the related information. This allows sort/select on any field of each table as long as it is retrieved in the query.

[richard flack]

Please bear with me, Im still struggling with this.
First regarding reasons not to do this in the table, the question of for example searches, surely that can be overcome by making the bound column the text value rather than the ID.

Im not quite sure about the JOIN, Ill try it. Presumably Ill need a SELECT DISTINCT otherwise Ill get multiple repetitions?

[richard flack]

Oh also, Im not sure of the syntax in the query for referring to the current record (as opposed to the whole table), we using the country field on the current record to restrict the region lookup.

[June7]

The Bound Column is the value saved to table. If the text field is the unique key that tables link on, then yes that will work. But if linking is done on other fields (such as Autonumber and number datatype fields), then setting the text value as Bound Column will fail.

When I view tables I prefer to see the real value I am working with, not the lookup alias. Even if I am the only user, I will set up forms to work with data.

In the query designer, you need to set the join link between the two tables. Then drag fields to the design grid. Access Help has guidelines on how to create queries in the designer and wizards.