Dependent Dropdown and Multi Columns

[jackyaz]

Hi guys,

Im hoping you can help me here. Im currently making a database with several tables for clients, consultants etc. On the data capture form, Im using a combo boxes for client name (which links up to the Client ID when I use the lookup wizard, but still produces the name), consultant name (links to Consultant ID) etc. Now, onto the problem. Each client can have up to 3 locations, which I have stored as 3 separate columns in the Client table, as Location 1, Location 2, Location 3. What I would like the form to do is have a dropdown for location, that updates its contents based on what is entered in the Client name dropdown. However, I would like it to bring up the 3 locations as a single column in the Location drop down, if this is possible?

 

[Mr. B]

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.

 

[jackyaz]

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

 

[jackyaz]

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.

 

[Mr. B]

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 loacation may be. Or it could need more fields related to one location.

 

[jackyaz]

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?

 

[Mr. B]

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 locations, but what happens if sometime in the future the rule that says they cannot have but three locations changes and now they can have six location?

You need a table with the structure I outlined above.
Your "tblLocations" table might have a structure like:
LocationID - Primary Key, AutoNumber field
ClientID - Foreign Key, Number (long integer) field, id field from clients table
Location - Text field for the Location value for each client's location
Other fields as required that are directly related to each location.

The "location" field could also be a Number field if you want to have another lookup table that would have a primary key field and a text field. In this table you would have the list of all posible locations available. But this may be a little "over kill".

 

[jackyaz]

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

 

[Mr. B]

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 at this point you would have 5 records in the locations table. If you use a sub form for with a combo box for the location field, linked by the ClientID master and child fields, all you would have to do when adding a new client, would be to select the location for each record you need to create for the new client. This method assumes that you have the third table of available locations or that you are presenting a unique list of existing values form the Locations table.

 

[jackyaz]

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.

 

[Mr. B]

Take a look at this:

http://support.microsoft.com/kb/105519

Shoudl help.

 

[jackyaz]

ah thanks!