Table Analysis - am I on the right track with these relationships (1 Viewer)

AccessWorld

New member
Local time
Today, 08:34
Joined
Jun 23, 2020
Messages
20
Am I on the right track with these Table Relationships? Nothing seems to be working when creating a form to make combo boxes for personnel, buildings, Location and Inspection Items (which is not complete yet for fields). I want to evolve to cascading combo boxes but can't even get separate boxes to work now. I reduce the form to just Personnel and Building tables/query info on the form and neither of those will work together for combo boxes. I have used them before but here it states that Buildings is not part of the recordset....even the Personnel only on the form will not give me a selection capability by itself??? Corrupted form? Stuck on this one...Simple dbase that and I am a bit embarrassed I can't figure this out. Do the pros here see anything I did wrong in the relationships?

1596236292660.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:34
Joined
Feb 19, 2002
Messages
29,437
The Inspection Items is not normalized. Each item should be a separate row and a separate inspection result. You should not have just one Yes/No for all the items inspected.

PS, Names should only include letters, numbers, and the underscore. They should not include spaces or special characters.

Also, you are probably missing a table. There should be a list of items to be inspected and that would be connected to either the location or a building. Probably a building. Then the inspection results is a junction table between the person doing the inspection and the inspection items table.
 

June7

AWF VIP
Local time
Today, 05:34
Joined
Mar 9, 2014
Messages
3,292
What exactly are you inspecting - just a part of a building?

If you want to provide db for analysis, follow instructions at bottom of my post.
 

AccessWorld

New member
Local time
Today, 08:34
Joined
Jun 23, 2020
Messages
20
Does this look better? Also here is an example list of inspection items in the building. This list is just a first swag at items to be inspected. It is a placeholder for now as items will change in the list. See second screenshot below the relationship window for the inspection items. Hopefully, these relationships are better designed? I am not sure if tbl_building and tbl_Location (inside the building) should have a relationship connection to tbl_InspectionItems - or will it work as shown here? Normalized the tbl_InspectionItems and placed tblComments and tbl_Unsat (Unsatisfactory) checkbox with relationships... and can do the same with tbl_Priority_Code if this is heading in the right direction. I want to get this relationship stuff right this time - with past dbases I have not done too well. Thank you!
1596238617516.png

1596238791167.png
 

June7

AWF VIP
Local time
Today, 05:34
Joined
Mar 9, 2014
Messages
3,292
So for each inspection location will repeat all 37 inspection items in tbl_InspectionItems? Why is personnel attached to building?

Consider:

tblPersonnel

tblBuildings
BuildingID
Address

tblLocations
LocationID
BuildingID_FK
LocationDesc

tblInspectionItems
ItemID
ItemDesc

tblInspections
InspectionID
DateInspection
LocationID_FK
PersonnelID_FK

tblInspectionDetails
InspectionID_FK
ItemID_FK
Condition
 
Last edited:

AccessWorld

New member
Local time
Today, 08:34
Joined
Jun 23, 2020
Messages
20
Just found your instructions to send the dbase file. It is just tables for now as I am trying to get this to work correctly from the start by correcting my relationships. Thank you for looking it over! Very basic for now. Once I get this right I "think" I can develop the rest of it.
 

Attachments

AccessWorld

New member
Local time
Today, 08:34
Joined
Jun 23, 2020
Messages
20
So for each inspection location will repeat all 37 inspection items in tbl_InspectionItems? Why is personnel attached to building?

Consider:

tblPersonnel

tblBuildings
BuildingID
Address

tblLocations
LocationID
BuildingID_FK
LocationDesc

tblInspectionItems
ItemID
ItemDesc

tblInspections
InspectionID
DateInspection
LocationID_FK
PersonnelID_FK

tblInspectionDetails
InspectionID_FK
ItemID_FK
Condition
So for each inspection location will repeat all 37 inspection items in tbl_InspectionItems? Why is personnel attached to building?

Consider:

tblPersonnel

tblBuildings
BuildingID
Address

tblLocations
LocationID
BuildingID_FK
LocationDesc

tblInspectionItems
ItemID
ItemDesc

tblInspections
InspectionID
DateInspection
LocationID_FK
PersonnelID_FK

tblInspectionDetails
InspectionID_FK
ItemID_FK
Condition

Inspection items will be selected by the Inspector and will result in either a Satisfactory or Unsatisfactory result. Unsat results will result in Comments on the nature of the issue and probably a WO ID (have not placed that field yet) from another maintenance dbase - just the Work order #. Ideally...they will have checkboxes for the inspection item list and comments based on those unSat inspection items and set a Priority for the repairs.
 

AccessWorld

New member
Local time
Today, 08:34
Joined
Jun 23, 2020
Messages
20
Inspection items will be selected by the Inspector and will result in either a Satisfactory or Unsatisfactory result. Unsat results will result in Comments on the nature of the issue and probably a WO ID (have not placed that field yet) from another maintenance dbase - just the Work order #. Ideally...they will have checkboxes for the inspection item list and comments based on those unSat inspection items and set a Priority for the repairs.
personnel should select what building they are inspecting and the location inside that building to start the inspection. Then select by check box what is Satisfactory (no comments) or Unsatisfactory( with comments). I just wasn't sure where to put that in the relationship tree or quite how to connect it yet.
 

AccessWorld

New member
Local time
Today, 08:34
Joined
Jun 23, 2020
Messages
20
I have to leave for about 2 hours to do some other airfield work. My sincere appreciation for your skills, knowledge, and assistance. I will check back when I get back to the office.
 

AccessWorld

New member
Local time
Today, 08:34
Joined
Jun 23, 2020
Messages
20
So for each inspection location will repeat all 37 inspection items in tbl_InspectionItems? Why is personnel attached to building?

Consider:

tblPersonnel

tblBuildings
BuildingID
Address

tblLocations
LocationID
BuildingID_FK
LocationDesc

tblInspectionItems
ItemID
ItemDesc

tblInspections
InspectionID
DateInspection
LocationID_FK
PersonnelID_FK

tblInspectionDetails
InspectionID_FK
ItemID_FK
Condition
Yes...but some locations it may not make sense to inspect all 37 items. But to have the checkbox listed on the inspection form is O.K. they simply select what applies. Later I may trymy hand at cascading combo boxes...plenty on the web on that I think. For now, simple is good.
 

June7

AWF VIP
Local time
Today, 05:34
Joined
Mar 9, 2014
Messages
3,292
My schema allows for any combination of inspection items. Yes, you can have a printed form that shows all 37 but only those that are checked as inspected would be entered into table. Have another field for Satisfactory/Unsatisfactory in tblInspectionDetails. Comments and WONum fields can be there as well. Sure, a lot of empty cells but I could tolerate them for the simplicity.

What is an inspection event - an entire building or area of building?
 

AccessWorld

New member
Local time
Today, 08:34
Joined
Jun 23, 2020
Messages
20
My schema allows for any combination of inspection items. Yes, you can have a printed form that shows all 37 but only those that are checked as inspected would be entered into table. Have another field for Satisfactory/Unsatisfactory in tblInspectionDetails. Comments and WONum fields can be there as well. Sure, a lot of empty cells but I could tolerate them for the simplicity.

What is an inspection event - an entire building or area of building?
So for this airport, Terminal A and Location areas associated with that building. Empty cells are not a problem. By selecting the building they will be presented with a cascaded combobox of locations inside of and associated only with that building. But the checkboxes can show for all locations on the form so that we ensure we cover all items for each location - like a reminder of what to inspect. I will print copies of the checklist and then they will enter their information on paper during the walkthrough. later, I might try my hand at an MS POWER APP for their phone to allow entries from the field on an app on their phone (down the road a ways). The building selection will limit the locations available inside that building but the locations will not limit the checkbox selections. If any of the selections are Unsatisfactory, then that checkbox is checked and the comment is entered on that form. They should be able to do multiple buildings in a single day and multiple locations on that day. I will try your suggestions tbl's and see how that works. Thank you!
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 08:34
Joined
Feb 28, 2001
Messages
17,695
Yes...but some locations it may not make sense to inspect all 37 items.
This implies that you have THREE values for inspection: Inspected/passed, Inspected/failed, Not Inspected. Although there IS such a thing as tri-state logic (Yes/No fields can do this), I've never been a fan of that ability because it leaves records with Null floating around and nulls can be tough to work with. I see an interesting omission that might help you solve the problem, though. Your InspectionItems table and its related tables don't seem to have a date. I would think that if you performed an inspection, you would want to stick a date on it. IF you do that, you can avoid the tri-state logic by saying something like

Code:
IF InspectionDate = 0 then 
    <inspection did not occur> 
ELSE 
    If UnSat = TRUE Then 
        <Inspection occurred and failed>
    ELSE
        <Inspection occurred and passed>
    END IF
END IF
I'm also concerned that it APPEARS that some of your relationship arrows in post #5 are perhaps backwards or at least are maybe not fully intuitive.

Take the diagram branch from tbl_Personnel to tbl_Building to tbl_Location. If I were interpreting what I see there, I would say that one person can interact with / inspect many buildings, and that makes sense. But how many inspection locations does a building have? Because you have one building, many locations. Are you breaking it down by floors (or finer, by rooms)? In that context, what is a location?

Then, I'm not sure what you are trying to say with the tbl_Comments and tbl_Priority_Code. That LOOKS like you can have a multiplicity of priority codes based on a single comment. That seems a bit odd.

Drawing diagrams and listing fields are all good, but you need to assure that you know exactly how those tables relate before you draw those relationship lines. Normalizing is good, but the tricky part is how those normalized tables fit together. And I'm seeing you try several different configurations. Just remember, the database comes second. Reality comes first. When you identify separable entities in the real world that will become parts of various tables in the DB, there should be a "natural" way in which they work together. Once you figure out how they work together, then you can draw in those relationship lines.

Don't feel bad, though. For complex problems, it might take several tries. Just remember - always start with reality and work towards your DB's representation of reality.
 

June7

AWF VIP
Local time
Today, 05:34
Joined
Mar 9, 2014
Messages
3,292
AFAIK, an Access yes/no field cannot be Null, must contain 0 or -1. An unbound checkbox can be triple-state.

InspectionID_FK would not be a primary key. tblInspectionDetails does not require a primary key field although an autonumber field can be useful in some queries. Might set InspectionID_FK and ItemID_FK as compound index to prevent duplicate pairs.

Otherwise, that looks like my suggested structure.
 

AccessWorld

New member
Local time
Today, 08:34
Joined
Jun 23, 2020
Messages
20
This implies that you have THREE values for inspection: Inspected/passed, Inspected/failed, Not Inspected. Although there IS such a thing as tri-state logic (Yes/No fields can do this), I've never been a fan of that ability because it leaves records with Null floating around and nulls can be tough to work with. I see an interesting omission that might help you solve the problem, though. Your InspectionItems table and its related tables don't seem to have a date. I would think that if you performed an inspection, you would want to stick a date on it. IF you do that, you can avoid the tri-state logic by saying something like

Code:
IF InspectionDate = 0 then
    <inspection did not occur>
ELSE
    If UnSat = TRUE Then
        <Inspection occurred and failed>
    ELSE
        <Inspection occurred and passed>
    END IF
END IF
I'm also concerned that it APPEARS that some of your relationship arrows in post #5 are perhaps backwards or at least are maybe not fully intuitive.

Take the diagram branch from tbl_Personnel to tbl_Building to tbl_Location. If I were interpreting what I see there, I would say that one person can interact with / inspect many buildings, and that makes sense. But how many inspection locations does a building have? Because you have one building, many locations. Are you breaking it down by floors (or finer, by rooms)? In that context, what is a location?

Then, I'm not sure what you are trying to say with the tbl_Comments and tbl_Priority_Code. That LOOKS like you can have a multiplicity of priority codes based on a single comment. That seems a bit odd.

Drawing diagrams and listing fields are all good, but you need to assure that you know exactly how those tables relate before you draw those relationship lines. Normalizing is good, but the tricky part is how those normalized tables fit together. And I'm seeing you try several different configurations. Just remember, the database comes second. Reality comes first. When you identify separable entities in the real world that will become parts of various tables in the DB, there should be a "natural" way in which they work together. Once you figure out how they work together, then you can draw in those relationship lines.

Don't feel bad, though. For complex problems, it might take several tries. Just remember - always start with reality and work towards your DB's representation of reality.
Understand...I was hoping working hard on the relationships and figuring out the necessary fields would help me visualize the real world stuff. Willing to go back and look at that line of thinking again. I envision just having checkboxes on a single form once they get to that page. Checking Sat or Unsat (yes or no to a checkbox labeled Unsat) will work for me. No third state is required. Your idea may have some implications when it comes to writing queries for my reports. I know it is likely late. I will continue to work on this tonight - here until 0300 local time. Reading through all of your comments. I want to get this part right from the beginning.
 

AccessWorld

New member
Local time
Today, 08:34
Joined
Jun 23, 2020
Messages
20
Understand...I was hoping working hard on the relationships and figuring out the necessary fields would help me visualize the real world stuff. Willing to go back and look at that line of thinking again. I envision just having checkboxes on a single form once they get to that page. Checking Sat or Unsat (yes or no to a checkbox labeled Unsat) will work for me. No third state is required. Your idea may have some implications when it comes to writing queries for my reports. I know it is likely late. I will continue to work on this tonight - here until 0300 local time. Reading through all of your comments. I want to get this part right from the beginning.
I see you point on the Not Inspected issue.
 

AccessWorld

New member
Local time
Today, 08:34
Joined
Jun 23, 2020
Messages
20
AFAIK, an Access yes/no field cannot be Null, must contain 0 or -1. An unbound checkbox can be triple-state.

InspectionID_FK would not be a primary key. tblInspectionDetails does not require a primary key field although an autonumber field can be useful in some queries. Might set InspectionID_FK and ItemID_FK as compound index to prevent duplicate pairs.

Otherwise, that looks like my suggested structure.
InspectionID_FK and ItemID_FK as compound index to prevent duplicate pairs
Set those two fields as Index: Yes, (No Duplicates)?
Removed Primary Key on tblInspectionDetails - should it remain an autonumber field or just a number field?
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom