• I am creating a new home page for this site, where the focus will be on directing people to the forums. If you would like to provide a testimonial, I would be most grateful. The thread where this is discussed can be found here: Seeking Testimonials Alternatively, just private message me.

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

AccessWorld

New member
Local time
Today, 11:31
Joined
Jun 23, 2020
Messages
19
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, 08:31
Joined
Mar 9, 2014
Messages
3,237
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, 11:31
Joined
Jun 23, 2020
Messages
19
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, 08:31
Joined
Mar 9, 2014
Messages
3,237
Not quite. Remove Inspect2 Index Name then the two fields will be a compound index assigned name Inspect1.
 

AccessWorld

New member
Local time
Today, 11:31
Joined
Jun 23, 2020
Messages
19
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, 08:31
Joined
Mar 9, 2014
Messages
3,237
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

Happy Retired Curmudgeon and Occasional Moderator
Staff member
Local time
Today, 11:31
Joined
Feb 28, 2001
Messages
17,270
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:31
Joined
Feb 19, 2002
Messages
29,158
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
 

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

Top Bottom