Table structure for cascading combo boxes

[DevTycoon]

This is a question that will continue to allow me to ask questions related to another Thread titled: Combo box issues
http://www.access-programmers.co.uk/forums/showthread.php?t=266782

I need a table structure that will allow me to have a repair log data entry form with 3 cascading combo boxes on the repair log data entry form. There can be many repairs for a specific job but most of the time there will be one repair per job and 1% of the time two or more repairs for a specific job.

I am pulling a report based on a query that will show the repaired location, facility type, repaired item, repaired component on that item and other details related to the repair.

I uploaded an empty database with the structure. If I can get some feedback on the structure I can put data in there to help with my other questions

Cascade levels

I. Facility Type
II. Item by
III. Component by [II]


Thanks

 

[GinaWhipp]

1. There are no Relationships in this database.
2. Do not see any Row Source data on the Combo Boxes.
3. What is the purpose of tblName_FacilityType? It's not in tbl_FacilityDetails
4. Why would FacilityType_FK be in tbl_RepairLog? Shouldn't that be in tblName_FacilityDetails?
5. I'm not understanidn why you related tbl_JobLog and tbl_RepairLog that way, I beleive it should be reversed but perhaps an explanation will help.

Hmm, perhaps if you explain the real world scenario and what this database is hoping to solve. I *think* I know but might be better if you just tell us.

 

[GinaWhipp]

Oops, forgot... Hard to answer you questions without the answers shown in Post# 2.

 

[DevTycoon]

1. [FONT=&quot]There are no Relationships in this database.[/FONT]
2. [FONT=&quot]Do not see any Row Source data on the Combo Boxes.[/FONT]
3. [FONT=&quot]What is the purpose of tblName_FacilityType? It's not in tbl_FacilityDetails[/FONT]
4. [FONT=&quot]Why would FacilityType_FK be in tbl_RepairLog? Shouldn't that be in tblName_FacilityDetails?[/FONT]
5. [FONT=&quot]I'm not understanidn why you related tbl_JobLog and tbl_RepairLog that way, I beleive it should be reversed but perhaps an explanation will help.[/FONT]

[FONT=&quot]Hmm, perhaps if you explain the real world scenario and what this database is hoping to solve. I *think* I know but might be better if you just tell us.[/FONT]

[FONT=&quot]I have made corrections and added some records in the newly attached DB[/FONT]

[FONT=&quot]1 & 5 [/FONT]
I have related the tables with more precision now.

2
Now that I have been working on fixing my errors and thinking about this project more, row source data seems to be the root question I am asking about.

3 & 4
tbl_FacilityDetails has been changed to tbl_LocationDetail. The idea here is that any given location may have one or more different power generation facilities on location at any time. Also, the expense approval for a job can be open for any time window. For this reason it is important that the power generation facility type be documented in tbl_RepairLog and not the tbl_JobLog or tbl_LocationDetail because the value may not be static during the life of the location or even during the shorter life of the approved expenditure for repair.

The purpose of the tbl_joblog is to document any repairs made on the same approved expenditure (This will not happen often but needs to be accommodated for to retain a normalized table structure…I think)

The purpose of tbl_LocationDetail is to hold any static information on the location. At this moment in time the only static data for a location I can think of is its name and GPS coordinates.

The purpose of this database is to track repairs for the facilities. The failure cause detail has not been added into the tables. I want to plug that in after I make sure the tracking side of the database works and the comboboxes cascade properly.

The cascaded boxes are currenly done with the text values and not ID's. I know this will cause the database storage space to grow faster but I do not understand how I can do this with ID's and still cascade the three levels that I need. Hence my issues with row source of the comboboxes.

As a side note all values in the comboboxes are currently approved. I have other data that is not imported where the facility type, item, and component terminology combinations are no longer approved. I hope to deal with that after solving my fundamental concern with the table structure.
I apologize for rushing the thread without these details. Thanks for your interest in my project.

 

[GinaWhipp]

Looking at it today...

 

[GinaWhipp]

Okay so there are three tables...

tblName_Components
tblName_FacilityCategory
tblItemType

It appears as though tblName_Components is the *main* and the other two relate to the *main*. So, is it that tblName_Components is all the combinations and if you make a selection from that one you should only be allowed to pick from a slection in the other two fields? If that is the case then you only need to pick from that one and show the other two (if you want) but no need to store those values.

Or is it that once you select a Component you can make combination of the other two and that is what is stored in the Repair table.

 

[DevTycoon]

Okay so there are three tables...

tblName_Components
tblName_FacilityCategory
tblItemType

It appears as though tblName_Components is the *main* and the other two relate to the *main*. So, is it that tblName_Components is all the combinations and if you make a selection from that one you should only be allowed to pick from a slection in the other two fields? If that is the case then you only need to pick from that one and show the other two (if you want) but no need to store those values.

Or is it that once you select a Component you can make combination of the other two and that is what is stored in the Repair table.

Yes the tblName_Components is all the different combinations.

Ok so I do not need to store the other values, in that case should I just have those comboboxes unbound and use them to guide the user to the correct ComponentListID? Can I use the same strategy for cascading?

Another one of my questions is, when query time comes and I want to see only the facility type repairs in a time window I would just select the component listID and the facility type for that query. Is that correct?

Thanks, if this is the case then I could reduce the tables required to just one instead of three.

 

[DevTycoon]

Also I am curious if a facility type or item became no longer approved terminology how would that impact the cascading? Looking at the data for import I don't see any facility types that would cause this issue but I do see some items that are using Un-approved terminology (They were approved for use at one time).

 

[vbaInet]

I wonder if any of these models would be of any use:

http://www.databaseanswers.org/data_models/

 

[GinaWhipp]

Okay, let me work this out and then send you a sample back with explanations (easier when you can see what I am describing) and then we can go from there. Also, it is Sunday so I may not finish today, could be Monday. Hope that is okay?

 

[DevTycoon]

I wonder if any of these models would be of any use:

http://www.databaseanswers.org/data_models/

I LOVE THAT WEBSITE. I used it for this project about 6 months ago when I started and also used it for another project. I think for this project the best and most closely related design is "Tracking software problems"

http://www.databaseanswers.org/data_models/tracking_software_problems/index.htm

The only difference is, the problem_category_codes table is basically the tblName_Component I have except I need to integrate the facility type, item number and component. Other than this little complexity for my needs it is pretty much the same table structure.

 

[DevTycoon]

Okay, let me work this out and then send you a sample back with explanations (easier when you can see what I am describing) and then we can go from there. Also, it is Sunday so I may not finish today, could be Monday. Hope that is okay?

Yes sunday funday. I am merrily preparing for a presentation I have tomorrow and trying to keep the database question coming because I know you all have important things to do too. I figure if I ask a question today that will get the ball rolling on a solution within the next few days. Any help is greatly appreciated and I hope everyone's weekend is nice!

 

[vbaInet]

It's a brilliant site.

Alright. I'll be off so that you and GinaWhipp can continue.

 

[GinaWhipp]

Thanks for dropping by vbaInet (and posting that link), stop by anytime!

 

[GinaWhipp]

Okay, so here are my updated/adjusted Tables and Relationships with an updated Form. I removed two Combo Boxes and replaced with Text Boxes which will reflect the corresponding values once the Componnet has been selected. I have also locked those two Text Boxes because there will be no need for anyone to update them.

Please review the Relationships and make sure they reflect your Data Model (Business).

Another one of my questions is, when query time comes and I want to see only the facility type repairs in a time window I would just select the component listID and the facility type for that query. Is that correct?

I am assuming you mean you pick a date range and then diisplay the Components and Facility Types in a list. If that is true then yes.

 

[DevTycoon]

Okay, so here are my updated/adjusted Tables and Relationships with an updated Form. I removed two Combo Boxes and replaced with Text Boxes which will reflect the corresponding values once the Component has been selected. I have also locked those two Text Boxes because there will be no need for anyone to update them.

Please review the Relationships and make sure they reflect your Data Model (Business).



I am assuming you mean you pick a date range and then display the Components and Facility Types in a list. If that is true then yes.

Thanks! I will look at this immediately. I don't know why I did not see the email notification for the response. I could have been working on this for days now:banghead::banghead:. Anyway. Thanks again.

 

[GinaWhipp]

No problem... happens to me sometimes!

 

[DevTycoon]

Works like a charm!

I am assuming you mean you pick a date range and then display the Components and Facility Types in a list. If that is true then yes.

Followup to your last comment


Does this mean that I need to have a date associated with the approval...Like an approval date?

If so, perhaps I can use that as criteria for the record source of that component combobox. If today date is less than the approved date ( date the item was selected as not approved) then show in combobox else do not show in combobox. I think that logic would allow old records to show unapproved values.

This question is getting me back to my original question on how to populate the records source of the combobox for only approved values. Is this question better suited for that thread?

http://www.access-programmers.co.uk/...d.php?t=266782

 

[GinaWhipp]

Hmm, I would probably use an Approval Date and yes, you can use that for the Combo Box *filtering*. I also think you should go back to that thread as that is more on topic than this one and this one is basically done.