Drop down list in field, trouble sorting in query

[Guest]

Hi,

I have a table where I am using a drop down list for my State field. The
drop down list is actually another table that I referenced when setting up
this field (I have a State/Province table, which contains all of the states
in the US and also provinces in Canada).

I have a query where I am looking for certain records from my table within a
certain state (such as all records with a state listing of Iowa). My query
returns no records, however, even though I have many records with the state
of Iowa listed.

Is this happening because I referenced another table for my drop down list
as opposed to setting up my drop down list directly within the original
table? How can I fix this?

Thank you,
Dawn Furlong

 

[SirPoonga]

The problem could be because of a couple of different things

1) does the spelling of the state in your state/province lookup table
match what is in your other table?
2) bound or unbound form?
3) what actions are you taking after a dropdown is selected? How are
you filtering the data?

 

[Larry Daugherty]

You probably stored the primary key of the Iowa record for each thing that
was in Iowa. Your recovery query has to track the way you put things into
the database. You may need to link two tables in your lookup:
State/Province being where you start. When you Choose Iowa you want to
actually return its key which is what's linked into the table to the right.

HTH

 

[Larry Daugherty]

The penny just dropped. Are you using a Lookup field in your table? If you
are, that's the problem. Go back and design your table properly and do not
use lookup fields in your tables. You are experiencing one of the
consequences of using them.

HTH

 

[Guest]

I do have the State field in my accounts table set up as a lookup field. I
didn't type all of the states directly into the table to be used as drop down
choices, but instead told it to look up the values listed in another table.

I was not aware that using a look up field in a table was not appropriate.
Why would this choice be offered? The reason I wanted a look up field was
that I wanted to make data entry easier and I needed to control the data
being input (as in, I need the state name typed out, not abbreviated, and
using a drop down ensures this, as well as ensures proper spelling). Can you
give me more information on this?

I did end up solving my problem. I went back to my query and added my
State/Province lookup table in design view. I then chose the State field
from my look up table to show up in my query, as opposed to choosing the
state field from my accounts table. I am now able to indicate a particular
state in my criteria for the state field, and proper results are displayed.

Thank you to all who offered me advice.

Dawn

 

[Larry Daugherty]

Hi Dawn,

Lookup Fields break the rules of normalization and will continually lead
users into the mire. They do look appealing and labor saving at the first
levels. My advice is to never use them. I have inferred that the MS Access
development manager of some time back decided that lots of users were going
to just use Access like an Excel spreadsheet and would be mucking with their
data directly in the tables.

On the other hand, using a table for a list to be displayed in a combobox on
your form is an excellent idea.

I'm glad you've got your present symptoms under control. What you've done
to compensate for the error is about the way you'd need to re-design your
schema to avoid future problems.

HTH

 

[Guest]

Hi Larry,

Thank you for the additional information.

Hate to sound dumb, but when you say, "What you've done

to compensate for the error is about the way you'd need to re-design your
schema to avoid future problems," what exactly is it I should do to redesign my table? I would say I am closer to an intermediate user (no programming knowledge, am learning SQL), but am unfortunately a little slow sometimes in immediately understanding since there is so much to learn.

Anything I can do to avoid future problems down the road I would really
rather know about.

Thanks,
Dawn

 

[Larry Daugherty]

Hi Dawn,

Go back and design your tables without the Lookup Fields. Then build your
application on your well designed tables. Microsoft had their own motives
and reasoning in providing Lookup fields and they DO look like a labor
savings. In providing them they've sandbagged countless Access developers.

I can't warn you against all of the troubles you might find as you continue
your development. Buy books and try to understand why the authors took the
paths they did in creating their examples. Lurk these Access newsgroups as
a matter of habit. That's the best way to see what troubles people get into
and how others help them overcome them.

HTH
--
-Larry-
--

Hi Larry,

Thank you for the additional information.

Hate to sound dumb, but when you say, "What you've done

redesign my table? I would say I am closer to an intermediate user (no
programming knowledge, am learning SQL), but am unfortunately a little slow
sometimes in immediately understanding since there is so much to learn.