What you are looking for is a conditional value list.
Maybe you should do a search with that term.
Can it be done using a single table or will I need to create tables for streets, door numbers etc and relationships to create the conditional value lists?
How is your original data set up?
If you use only one table, won't you be repeating a lot of data?
You would have to repeat the street name for every number won't you?
In that case I think it would be better to use different tables.
But if you want you could maybe also use multiple table occurrences on the same table to create relationships.
I however don't know enough about your table to give a certain answer.
The data was from an excel spreadsheet and yes it repeats lots of data, bit of a pain to be honest but that was the only way I could get it, essentially the table is:
id|title|firstname|surname|address1|address2|address3 -6|telephone|email|various fields like interests|datelastchecked|
What I want to do is allow someone to select address2 (street) from a drop down.
Depending on the street show another drop down with the numbers on that street.
Then show me the names of the people at that address and let me add their interests.
I am hoping to use it on an ipad as I walk from house to house speaking to people.
I spent a little bit of time thinking about this, mainly about how you would have to make several tables and then get all your data properly linked etc.
But then it hit me that with the data you have it's pretty simple to get this done.
I made a little example file:
This uses only one table and two instances of the same table to get the relationships sorted.
It uses two global fields to select the street and number.
Let me know if you need any more splainin :)
WOW, thanks, much more help than I expected, but very welcome.
Looks like my work tomorrow morning will be to look at the demo and see how it all works and replicate it on my own database.
Excellent and thanks very much for your help.