Solved Normalization and user workflow issues (1 Viewer)

zeroaccess

Active member
Local time
Today, 16:45
Joined
Jan 30, 2020
Messages
671
Interesting. So there's nothing in the table that relates the two?
 

Minty

AWF VIP
Local time
Today, 21:45
Joined
Jul 26, 2013
Messages
10,355
If there is something on your main form that can link them then ignore my advice.
Frequently you want a FK value from Sub1 to drive Sub2 that may not be readily available on the Main form?
 

zeroaccess

Active member
Local time
Today, 16:45
Joined
Jan 30, 2020
Messages
671
My tables all have keys.

Main table has a primary key
Subform 1 has the main table PK as a FK
Subform 2 has subform 1 PK as a FK, in addition to main table PK as a FK for reference

That is because each Inspection has many Samples, and each Sample can have many Errors. But I also want to keep track of which Inspection the Errors occurred on. Or is that already implied by the Inspections-Samples relationship? I didn't draw an Inspections-Errors relationship here due to previous comments about circular references.

Is that correct? Full relationship view – the 3 tables in the center are main form, subform 1, and subform 2:
 

Minty

AWF VIP
Local time
Today, 21:45
Joined
Jul 26, 2013
Messages
10,355
Okay - As you have surmised the InspectionFK in your errors table is surplus to requirements but in your case, if you want to see all the errors for an inspection rather than specific sample related records then it will actually achieve your goal.

Personally I would only want to see the ones related to the sample I had selected in subform1, but I'm not familiar with your business process. It may be seeing all of them gives you a better overview. This may be one of those instances where total normalisation actually causes you more work than it's worth. The only caveat would be how the InspectionID is set in the error record. If there is any possibility for an error in that process you should remove it.
 

Minty

AWF VIP
Local time
Today, 21:45
Joined
Jul 26, 2013
Messages
10,355
You could simply create a summed query and then use a count on that or add another subform with the query as its record source linked to the PR no?
 

zeroaccess

Active member
Local time
Today, 16:45
Joined
Jan 30, 2020
Messages
671
Was just going to post - unfortunately no, any summed field in a query makes the whole recordset "unupdateable". And I think I have enough subforms for now!

The solution was a DSum in the control source, which did not have that side effect. The DSum is using a where condition on the Sample ID so it's reasonably efficient.
 

Minty

AWF VIP
Local time
Today, 21:45
Joined
Jul 26, 2013
Messages
10,355
I meant to create a separate query with all the sums in it and simply display that, not much different to a DSsum() in effect.

Glad you have a working solution though!
 

zeroaccess

Active member
Local time
Today, 16:45
Joined
Jan 30, 2020
Messages
671
Thanks. DSum doesn't like to operate on the new record line though - results in ERROR.
Could it be wrapped in something like IIF? Tried if Sample ID=0, IsNull, etc...no luck.
 

Minty

AWF VIP
Local time
Today, 21:45
Joined
Jul 26, 2013
Messages
10,355
Always an issue - You can use something in the vba to hide it
If Me.Newrecord Then ...
 

zeroaccess

Active member
Local time
Today, 16:45
Joined
Jan 30, 2020
Messages
671
Okay - As you have surmised the InspectionFK in your errors table is surplus to requirements but in your case, if you want to see all the errors for an inspection rather than specific sample related records then it will actually achieve your goal.

Personally I would only want to see the ones related to the sample I had selected in subform1, but I'm not familiar with your business process. It may be seeing all of them gives you a better overview. This may be one of those instances where total normalisation actually causes you more work than it's worth. The only caveat would be how the InspectionID is set in the error record. If there is any possibility for an error in that process you should remove it.
I achieved this by just using the form's Before Update event: if Inspection ID = 0, set Inspection ID = to the main form's Inspection ID. The default value of the field is 0 - so immediately when the form became dirty, it got a 0, and the coming save would set it to the appropriate value.
 

zeroaccess

Active member
Local time
Today, 16:45
Joined
Jan 30, 2020
Messages
671
Always an issue - You can use something in the vba to hide it
If Me.Newrecord Then ...
I ended up wrapping it in =IIf([Sample ID]>0,DCount("*","tblErrorDetails","[Sample ID]=" & [Sample ID]),"").

The field in the new record line is now just happily blank.

A conditional highlight helps the user see the active record which will correspond to what is being displayed in Subform 2.
 

Attachments

  • DCount.png
    DCount.png
    13.7 KB · Views: 103
Last edited:

Minty

AWF VIP
Local time
Today, 21:45
Joined
Jul 26, 2013
Messages
10,355
Glad you have sorted it out, good luck with the rest of it!
 

Users who are viewing this thread

Top Bottom