Query trouble

[Eulos18]

Using Access 2007.
I have a large amount of info based on crop trial information. The database
consists of a number of factors including the variety name, grower, town
location, irrigation method and year plus others. I have set up these factors
into their own tables so they may accessed from a combo box within a form
when setting up each trial.

The problem i have is when I go to run a query, the parameter does not
recognise the actual name of the factor i am searching for, only the
corresponding ID number in the table. This becomes difficult, especially when
I am dealing with a large number of varieties. How can I get the parameters
to recognise the name and not the ID number.

 

[Allen Browne]

You might consider creating a search form where you can give the users
combos for searching. The combos can have 2 columns (for the number and the
text), with the first column zero-width so they actually show the text.

For a downloadable example of such a form, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Another possiblity would be to use the actual text as the primary key of the
lookup field. This is not a good idea for columns where you have many
thousands of entries (like growers), but works well for smaller tables (like
irrigation method.) If the Text is the primary key, then the matching field
in your main table has the text as well, and so there's no problem entering
the text.

Another approach is to use a query that has the various lookup tables as
well as the main table. You now have the text fields from the lookup tables
in your query, and so you can use them in the search. You may need to use
outer joins for this to work for you. More info about that:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html