Solved Normalization and user workflow issues

[zeroaccess]

Interesting. So there's nothing in the table that relates the two?

 

[Minty]

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]

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]

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]

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]

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]

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]

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]

Always an issue - You can use something in the vba to hide it
If Me.Newrecord Then ...

 

[zeroaccess]

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]

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.

 

[Minty]

Glad you have sorted it out, good luck with the rest of it!