interesting query issue...

[Guest]

Here’s my best shot to explain this phenomenon:

We have a table of registrants.

We have a table of interests (Drop down combo box/lookup table that feeds
five fields on the table of registrants).

The interests appear correctly (as text) in the table of registrants in each
of the five fields (Interest 1, Interest 2, etc.).

But, when I use a query to narrow the list to a specific interest, it
doesn’t return *all* the registrants with that interest (even though the
correct interest is in the correct field for each of the registrants). For
example, I put “Rome†as the criteria for a Field named “Interest 4â€. The
one person, Stevie, with “Rome†as her interest in Interest 4, does *not*
appear (no one appears).

Those missing registrants *do* appear, however, in the query when I change
the criteria from “Rome†to “44â€. Surprisingly, “44†doesn’t appear in the
query display though—rather, “Rome†appears in it. In my example, Stevie
does show up with “Rome†(as text) in Interest 4. This method does not
return all the registrants, however; only the missing registrants.

“Rome†appears to be the 44th entry made on the drop down list. (Not
necessarily the 44th option listed alphabetically).

The list is too big for me to know who might be missing with so many people
and so many options. What is causing this, and how do we fix it?

Thank you sooooo much!

Lisa

 

[Jason Lepack]

Now you know why in the Access World, Lookup Fields are akin to the
Devil.

Is the table of interests actually a table that you created?

If the answer to the above question is "yes" then all you need to do is
add the interests table to you query, link the two fields, and instead
of putting your criteria in "Interest 4" you would put it in the field
that has the text from the interest table.

Now we get into the more sticky situation of your table design. Let's
say that you spent all this time building this database and created the
query that you're wokring on now, and then someone decided, "hey, we
want the people to be able to enter 5 interests!!" and then next week
it's decided that 5 isn't enough so "let's add another!!", finally
someone says, let's let them choose as many as they want.

Your design cannot handle that. Simply adding another column and
modifying every query that you've created on that table is bad for just
adding one interest.

Your query, as you will find, will be tough and as noted above, tedious
to change further down the road.

I propose that before you get too far you look at this design:

tblRegistrant:
registrantID - AutoNumber - Primary Key
firstName - Text
lastName - Text
etc.

tblInterests:
interestID - AutoNumber - PK
InterestName - Text

DON'T MAKE ANY OF THE BELOW FIELDS LOOKUP FIELDS!!
tblRegInterests:
regIntID - AutoNumber - PK
registrantID - Number - Foreign Key (linked to tblRegistrant)
interestID - Number - FK

Then you go to Tools->Relationships and link the PK's with th eFK's.

You could create your main form with tblRegistrant as the recordset,
then you could use a subform with tblRegInterests as the recordset that
is linked to the mainform by registrantID.

On the subform you could have a combo box to choose the Interests.

HTH,
Jason Lepack