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

AccessWorld

New member
Local time
Today, 13:30
Joined
Jun 23, 2020
Messages
27
I know it might be very late where you are - I do appreciate your help. Learning curve can be tough...and just when I thought I was getting a grasp on this :)
 

June7

AWF VIP
Local time
Today, 10:30
Joined
Mar 9, 2014
Messages
5,423
InspectionID_FK needs to be a number (Long Integer) field to save the InspectionID value from tblInspections. Another field called simply ID can be an autonumber type.

Set the two fields as a COMPOUND index No duplicates, not index them individually.

All primary key fields would be autonumber type.
 

AccessWorld

New member
Local time
Today, 13:30
Joined
Jun 23, 2020
Messages
27
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?
Is this what you are referring too for compound index? ....will look for answer tomorrow if you're posting... If not...Have a great weekend.
1596257446383.png
 

June7

AWF VIP
Local time
Today, 10:30
Joined
Mar 9, 2014
Messages
5,423
Not quite. Remove Inspect2 Index Name then the two fields will be a compound index assigned name Inspect1.
 

AccessWorld

New member
Local time
Today, 13:30
Joined
Jun 23, 2020
Messages
27
Last comment prior to hitting the hay...queries I develop use this schema result in an inability to update anything in the query or the form. What am I missing? Are my relationship joins not correct? Doing it right sure is a lot more challenging than doing it wrong :) However, I do want to do this one correctly.
 

June7

AWF VIP
Local time
Today, 10:30
Joined
Mar 9, 2014
Messages
5,423
Usually a form is to edit/enter records for one table. Don't try to build a query with all these tables to do data entry. Form bound to tblInspections could have a subform for tblInspectionDetails. Comboboxes on main form to select personnel and location. Combobox on subform to select inspection item. There would be independent forms for entering new records into the 'lookup' tables such as tblPersonnel. If need to add a new lookup item during data entry, use combobox NotInList event to open independent form. Use of NotInList is a common topic.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:30
Joined
Feb 28, 2001
Messages
26,999
In your post #16, that diagram shows something I missed at first glance - a 1 to 1 relationship which almost certainly cannot be correct. You've got tblInspections/InspectionID as 1:1 with tblInspectionDetails/InspectionID_FK.

I say it cannot be correct for one of two reasons: EITHER you are using the "...DETAILS" table as an extension of the Inspection table and there really WILL only be one of each, OR you meant something other than 1:1 on the relationship.

In general, proper normalization will never produce 1:1 tables because (based on "purity of purpose") you never need two keys to refer to what is really just one set of data. So you merge 1:1 table UNLESS there is a security issue that requires you to keep some data separate and protected from prying eyes. I saw a few cases like that when I was a Navy contractor on a personnel system. But it is highly rare.

In context, I can't be sure but it seems to me that the InpsectionDetails table should be a child table that lists / enumerates details relating to an inspection that touches many factors (as suggested by your table InspectionItems). And the ...Details vs. ...Items table relationship DOES make sense if the ...Details table really IS a list of individual items in an inspection.
 

June7

AWF VIP
Local time
Today, 10:30
Joined
Mar 9, 2014
Messages
5,423
@The_Doc_Man, post #17 advised about error on 1:1 relationship but I like your expanded explanation.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:30
Joined
Feb 19, 2002
Messages
42,971
The new model looks better but it still isn't right. It looks like inspection items should be linked to location. If you have items that are generic and cross locations, you can create an inspectionItems table with all items and then a LocationInspItems table which links which items are to be inspected at which location. When the inspection happens a record with the date and who performed the inspections is created in tblInspections and all the inspection items for that location are appended to InspectionDetails with "notInspected" as the InspResult. I don't like the UnSat name and I don't like it being a Y/N field since it actually has three states. I would use a combo or an option group if you prefer but option groups can't be used on forms in DS view so i would stick with a combo to choose yes/no/notinspected
 

AccessWorld

New member
Local time
Today, 13:30
Joined
Jun 23, 2020
Messages
27
I still have the schema from post 16. I had to leave the work for a bit...now back...here is a second attempt I did. I still have all of your original suggestions. Still trying to educate myself on setting up relationships.

Inspectors will inspect each building daily on each of 3 shifts. Each building has a number of locations. Each location has inspection items that I would like to break out for that specific location only (as you suggested rather than all 37 for each location).

The Sat/Usat checkboxes (one for each answer now) just indicate the status of the individual inspection - will likely default to Sat (yes) and inspector will only check Unsat when an issue is found. Priority is sell explanatory. I have the date and time of the inspection in the "connect" table....should it be somewhere else? Locations will have different times for inspections but I am comfortable for now just showing it as an ending time for all inspections (although I may add a start time and end time rather than a single end time- what table would that be in then if used).

This will evolve as we begin hence I want it to be right from the start. Your suggestions have been a great help.
 

Attachments

  • Schema for Inspections.JPG
    Schema for Inspections.JPG
    69.3 KB · Views: 125

vhung

Member
Local time
Today, 11:30
Joined
Jul 8, 2020
Messages
235

good day
>in some ways tables relation is so nice to create and connect
in which ideas/imagination of what you need to work out
>on the other hand the actual table relation needs to be test
it's better to create first the main table where to save the major data
of your banking...
>the additional table shall be added later soon as the Main Data Form is already functional
>additional table/forms would be possible as work flow needs arises
but i guess not all makes this way, but i did it, it's really challenging to do this
because you may find the best approach either simple but very effective...
 

Users who are viewing this thread

Top Bottom