Thread: Dependent Dropdown and Multi Columns

I hate to be the bearer of bad news, but you need to redesign you database structure. You need a table for the client locations, not three columns in the client table. This will make life simpler for you. Change this and then you will be able to have your combo box that will very easily show the available locations for a selected client.

thats not too bad, theres no actual data in the database yet. how would the locations table look exactly?

do you mean simply a table that will serve as a lookup with a set of locations? each client has a set 3 locations that wont change. When data is captured, it is for a consultancy visit, so the client name will be selected, and 1 of the 3 locations needs to be a dropdown to select which location they were visited at.

If the 3 locations are uniquely related to one specific client then you need a table that will hold any and all related data for each location. I could be as simple as a primary key (autonumber field), a foreign Key field (number field) to link to the appropriate client by the clientID and a text field for entering the name of the location or the address or what ever the data specific to the ...

thanks for the reply. the locations are simply things like London, Birmingham, Guildford etc. which will obviously be shared by some clients. Does this affect how i make a location table?

You can use another table as a lookup table for the actual list of available locations, but if you want to be able to specify all of the locations for any one client, you need a table that will allow you to do it as it would be done in real life. There is a one to many relation between any client and the locations they can have. I know you said that there was only a possibility of them three ...

Many thanks, I understand what I need to do. Would I have to make duplicate entries for each client depending on the locations I have? Im just thinking in terms of ease of use to add another client to the database

You would only need to identify the locations for any client one time unless the locations change. You would have one record in the "tblLocations" table for each location that each client has. If client "A" had 3 locations, you will have 3 entries in the locations table for client "A". If client "B" has only 2 locations, you will only have 2 records in the locations table for client "B". Thus ...

This works fantastically, many thanks! One last thing, is there any way to make it default to the first location for a specific Client ID? I was looking in the expression builder for default value, but I'm unsure how to make it pick the first of the 3.

This works fantastically, many thanks! One last thing, is there any way to make it default to the first location for a specific Client ID? I was looking in the expression builder for default value, but I'm unsure how to make it pick the first of the 3.