How to limit items in a pick list by a criterion in another field

[ArnieT]

I want to build a database for people's names.
The fields are these:
First name
Middle name
Last name
DOB
State of birth
County of birth
City of birth

The problem is with the last three items.I want to make a pick list for the state, county, and the city. No problem with the state. Just created a table with one text field and filled it up with states. Here is the Constructor view of the table.



Then I created a table for state counties. Here is its Constructor view.




Here, the county field is a simple text type, but the state field is of the lookup type.


The reason for this is that when entering a county in the table, I want to pick up its state from the list of states in the combo box (the states in it being sourced from the tblStates table) as is shown here:





As a result, my table tblStates ended up looking like this:




That's not a problem, and I would say it's even a plus. I haven't figured out yet the way this relation works, though.
But it's not important as far as my problem is concerned. At least, I don't think it is.The problem starts with the people's table. Here is what it looks like:





To be exact, all counties for all states are available for picking, instead of only counties for the selected state, which is Alabama in this case.
The constructor view for the birthCounty field of this table is this:
(The forum won't let attach more than 5 images, so I am including the rest as URLs)

https://my.pcloud.com/publink/show?c...eQCizJwj3yYzvy


The birthState field type is also of the lookup type from the tblStates table. It works without any problems, as this table has no connected tables.

So, my question is, how can I get only the counties for the selected state to show in the pick list for the birthCounty field?

(Here, I included both columns in the pickup list for demonstration purposes only, so as to make it easier to see that counties for all states are in the pick list.)

I believe there is a solution to this problem. I think I did a similar project long time ago (about 15 years ago), and I was able to filter out the pick list, limiting it only to the criteria in another field. At least I think I did. But it was so long ago that now I don't remember how I did it.

And here is what my tblPeople table winded up looking like:

https://my.pcloud.com/publink/show?c...xinryp5uURAgTy



And here are the final relationships:


https://my.pcloud.com/publink/show?c...E8onT5zBtVIozX


Funny, there is no relationship between the tblStates table and the tblCounties table, even though in the latter I am picking states from the former.

[orange]

I recommend that you read this post regarding naming conventions and lookup fields.

[June7]

What you describe is known as cascading or dependent combobox. Must use a form with some coding. Common topic. Search forum or web. MS even offers a template db to demonstrate. I found it in: File>New>Calculator

[davegri]

Here's something:
People-v1.zip
Demonstrates how to use comboboxes to select and show your data on a form, while actually storing the Foreign keys in the tables. Standard practice for lookups.
After selecting a state, only counties in that state show in the county dropdown.

[ArnieT]

Deleted it. Duplicate. Don't know how to delete altogether.

[ArnieT]

What you describe is known as cascading or dependent combobox. Must use a form with some coding. Common topic. Search forum or web. MS even offers a template db to demonstrate. I found it in: File>New>Calculator

Yes, looks like forms is the way to go. I was already pointed in that direction in another topic.
Thank you for seconding this.

P.S. As for searching the web and the forum, it's like digging for diamonds in diamond pits - one has to shovel over tens of tons of dirt just to find a carat of diamond. I was hoping for a fast solution here

[ArnieT]

I found it in: File>New>Calculator

Thank you for shoving me in the right direction. This looks like exactly what I need.

But I am still wandering if there is a way of doing this with tables. Because, actually, I wanted to create the DB in MySQL, but because of my meager knowledge of that DB decided to practice first in ACCESS. But, as far as I know, MySQL does not offer a crutch in the form of forms. So, how would this problem be approached in MySQL?

But this is just a rhetorical question, because for the time being I will stick with ACCESS and hopefully, buy the time I know how to build the database I need in Access, I will know enough to build it in MySQL too.

[June7]

No, cannot be done in tables, of any db platform.

Must use another application as interface.

Access is one of many that can connect to MySQL tables and serve as user interface.

[ArnieT]

No, cannot be done in tables, of any db platform.

Must use another application as interface.

Access is one of many that can connect to MySQL tables and serve as user interface.

I see. Thank you. I really appreciate your help on this matter.