Cascading combo boxes... help me again please!

[aqueousdan]

I am seeking your help once again as I am totally lost!



With your help I managed to get a form in my database to work correctly. I have a table of factories which are located at various sites. I am able to now select a factory based on which site I select from combo boxes.

I would now like to do the same again for engineers based at each specific site but I cant see how to set it up.

The Site and Factory information was located in a table together but the details for the Engineers are based in a different table. I can get this to display ok but it just shows all of the engineer names.
I have tried creating a query to get all of this information together and using that but I cant get it to work.

Can anyone take a look and see if this can be done?

Thanks for your help once again!

Dan B

[ItsMe]

Perhaps learning about the various properties and events of the combobox control will be the most helpful to you. Here is a link to a screencast series that does just that.
https://www.accessforums.net/showthr...671#post277671

Paste the username and pass into an open text file for ease of logging onto the landing page. From there, you can download the series.

[Micron]

You have replicated factory name and site name in two places, combined sites and factories into one table, and combined first and last names in one field - these are examples of less than perfect normalization. What should be in the engineers table is the factory id, not the name. Sites should be one table, factories another, joined by having the site id as a FK (foreign key) in factories. This sort of thing will cause design problems which will only perpetuate, and probably get worse as you go. Each "thing" that can be considered to be its own entity should usually be in its own table. I recommend you read up on normalization as well. A more normalized set up based on your attachment would be as follows (note, this is not a complete overview, being limited to the 3 entities related to your post). Note also that while I've used the auto id's from your tables as foreign keys, you could substitute the value fields instead. Hopefully I have made the correct connections based on your data.

tblSites
SiteID	SiteName
1	Barton
2	Scunthorpe
3	Howden

tblFactory
FactoryID	SiteFk	FactoryName
1	1	Durham
2	1	Lincolnshire
3	1	York
4	1	Kent
5	1	Surrey
6	1	Devon
7	2	Factory 1
8	2	Factory 2
9	2	Factory 3
10	2	Factory 4
11	3	Factory 1

tblEngineers
EngrID	SiteFk	FactoryFk
2	2	8
3	2	8
4	2	7
5	1	1
6	1	1
7	2	8

You should also consider not having attachment fields as these can cause exponential db bloat. I think most developers would prefer these fields to contain links to folders if possible. I could probably come up with a solution to your problem, but if you're going to take the advice on normalization, that would make the solution useless.

[orange]

I agree with the points micron has made.
My recommendation is that you research Normalization and get your tables designed to support your business facts.
Do not use sitename as the link/relation, use SiteId the PK of the table. SiteId in Factory should indicate 1 site has 1 or more Factories.

It would be helpful to you and readers if you had a 4-5 line description of the business and processes you are trying to support with this database. Best if done in plain English as you would tell a child or your 80 yr old granny.

Good luck.

[aqueousdan]

Does this look correct?

This may be a completely stupid question but when I set up my database like that, should the foreign keys automatically fill in due to the relationship or do i need to input them myself?

[orange]

Does this look correct? No

re Foreign key do i need to input them myself? Yes

In overview, you have Factories at different Sites. Some Sites have more than 1 Factory. You also have Engineers. It appears you want to know/record which engineers work(are located at) which Factory.
For consideration my initial set up would be:

Sites-->Factory--->EngineerWorksAt<----Engineer

Which repesents

There are 1 or more Sites
A Site has 1 or More Factories
There are 1 or Many Engineers
0,1 or more Engineer(s) work at a Factory

Set up relationships based on the ID of the given table. Research Normalization.
Good luck with your project.

[Micron]

The only stupid question is the one that doesn't get asked.
Should they automatically fill in where? The engineers table or the Factories one? The answer depends on how you mean to enter the data.

If directly into tables as your table image and question implies, the answer is you would have to manually enter the fk's. However in the general sense, you shouldn't enter data directly into tables as this can be a recipe for trouble. Data entry should be via forms, so to create the relationships between the first two tables, a form/subform setup (Site/Factories) would be the "correct" way to create and edit the sites and factories relationships. Truthfully, for a very small data set that will likely never change, I'd probably do it directly, but then I have the advantage of experience. I would have to ensure I entered the correct site fk for each given factory. However, I would NOT do this in any tables related to these main tables, such as for engineers. This is definitely a job for a form. To control the site and factory input for a given engineer, the typical approach would be to use combo boxes which are typically based on queries of their respective tables; the factory combo being dependent on the site selection (research cascading combo boxes). In your case, these combos would contain the ID and name, but you would not show the ID in the combo list. The completed record gets written to the engineer table, thus the fk's are determined by the factory/site choices and are added to the table via the form. You are adding them manually in a way, but it is transparent to the user.

NOTE: to be honest, I wonder if you didn't read up much on normalization since you still have SiteName in the Engineers table. This was already pointed out.