Solved Normalization and user workflow issues (1 Viewer)

zeroaccess

Active member
Local time
Today, 08:11
Joined
Jan 30, 2020
Messages
671
I can't post this database at this time but I can provide a screen shot of the current situation. I'd like some input on this conundrum.

You have a parent main form and a child subform, each with its own table. The structure is inspections of products.

Samples of each product can not exceed a total lot size (number stored on main form in main table). Both samples and errors/defects are recorded on each line in the subform. That means with this structure, when inspecting products, there is a need to not duplicate your number of samples per product when recording many defects (otherwise you'd be saying you looked at 50+50 sprockets when there are only 50 total sprockets in the lot). You may find defects on multiple items. This is a hybrid approach that is built for speed and simplicity and the statistics work in the aggregate just fine.

But say you take issue with the zeros or say you wanted to make sure a user can never mess this up if they forgot their coffee one morning. I've worked out what should probably happen - that samples and errors be two separate tables, moving samples to another area on the form or a popup form. You would just enter them once. That eliminates the zeros and the need to manage that field for each item. The subform you see would then be only for errors/defects.

Is it worth it? Let me know your thoughts and suggestions.
 
Last edited:

plog

Banishment Pending
Local time
Today, 08:11
Joined
May 11, 2011
Messages
11,613
Since we are discussing tables and relationships it would be best to post a screenshot of your Relationship Tool to fully grasp this issue.
 

zeroaccess

Active member
Local time
Today, 08:11
Joined
Jan 30, 2020
Messages
671
Since we are discussing tables and relationships it would be best to post a screenshot of your Relationship Tool to fully grasp this issue.
Of course. I've mocked up how I think the table would be split. Ignore my scrawlings for the moment as I work out form design changes that would be necessary if this change were made.

On the form, you would visually see Samples moved to probably a popup form. Let me know if I have this right or if I should leave well enough alone. And yes, I know about the spaces in the field names.
 

Micron

AWF VIP
Local time
Today, 09:11
Joined
Oct 20, 2018
Messages
3,476
However, now we're creating for example 3 more child records with 3 fields each (InspectionID foreign key, item, sample count) to the database.
I'm not following that, so maybe what I think I'd do is not what I think you're also saying. AFAIC, product, samples and tests are 3 tables. The only fields I see for the table you don't have is sampleIDfk and whatever other fields required for sample data. I don't see a need to repeat inspectionID unless we're talking about the same thing. This would be a form/subform/subform relationship on the 3 tables where the 2nd subform is on the 1st subform, linked by sampleID (is that your inspectionID - I'm thinking not).

It's also my opinion that you're not talking about bloat. Bloat is wasted space. Normalization usually adds only one field per relationship, which as a long ID adds nearly nothing, which usually far outweighs any downside you might be talking about. As for making things more difficult, I can't see how from here, but what I'm saying follows the oft accepted design practice of one form/one table.
 

zeroaccess

Active member
Local time
Today, 08:11
Joined
Jan 30, 2020
Messages
671
InspectionID is at the top of the main form - in this example, 516.

I think I get what you're saying about a subform in a subform. Would you say subform 2 is the errors of the samples? That would make sense but I've never done that so I'm not able to visualize what the form would look like without trying it. And can this be done in the continuous forms structure? I suppose another way would be to move errors to a popup form instead but I'd rather not.
 
Last edited:

plog

Banishment Pending
Local time
Today, 08:11
Joined
May 11, 2011
Messages
11,613
Without knowing much about your organization's data, I can see from the Relationship that you have a few issues:

1. Circular references. There should only be one way to trace a path between tables. I see 2 ways to get from tblSampleDetails to tblQualityStandards (directly and via tblPerformanceRequirements). If tblQualitySTandards are related to tblPerformanceRequirements then you don't need tblSampleDetails to be directly related to tblPerformanceRequirements as well.

2. Duplicate tables. When you have 2 tables that are identical (or very near identical) you are essentially storing a value in the table name. That means most likely all the data in tblPerformanceRequirements and tblQualityStandards shoudl go into just one table with a new field that holds a value that lets you know if the record is a Performance Requirement or Quality Standard.

Unfortunately those 2 issues are tied together and require mutually exclusive solutions (keep both tables and change the relationships or merge both tables). So I don't know the way to fix it, because I don't know enough about your data.

I suggest you write 1 paragraph without any database jargon (no mention of table or forms or fields) that explains what it is your organization does. Then one paragraph with light jargon that explains what this database will help your organization accomplish. Lastly, please respond with your thoughts to #1 and #2 above.
 

Micron

AWF VIP
Local time
Today, 09:11
Joined
Oct 20, 2018
Messages
3,476
Would you say subform 2 is the errors of the samples? That would make sense but I've never done that so I'm not able to visualize what the form would look like without trying it. And can this be done in the continuous forms structure?
If by errors you mean defects then yes. A subform can be either continuous or datasheet. This is just one that I play with to answer forum questions, so don't be critical ;)
Here is a subform on a subform on a main form with continuous subs

subSubform.jpg
 

zeroaccess

Active member
Local time
Today, 08:11
Joined
Jan 30, 2020
Messages
671
Without knowing much about your organization's data, I can see from the Relationship that you have a few issues:

1. Circular references. There should only be one way to trace a path between tables. I see 2 ways to get from tblSampleDetails to tblQualityStandards (directly and via tblPerformanceRequirements). If tblQualitySTandards are related to tblPerformanceRequirements then you don't need tblSampleDetails to be directly related to tblPerformanceRequirements as well.
The relationship between tblPerformanceRequirements and tblQualityStandards exists because the data is related for lookup purposes. Regarding the relationship between tblSampleDetails and tblQualityStandards - I think you are hitting on the same reason why I posted this. What do you think of my proposed table split? The red box indicates the new table, to include the QS Name.

2. Duplicate tables. When you have 2 tables that are identical (or very near identical) you are essentially storing a value in the table name. That means most likely all the data in tblPerformanceRequirements and tblQualityStandards shoudl go into just one table with a new field that holds a value that lets you know if the record is a Performance Requirement or Quality Standard.
I can assure you these tables are not duplicates of each other. tblQualityStandards are like sub-requirements and is why each Quality Standard has a foreign key to a Performance Requirement. Each PR may have many standards. They are lookup tables.

Unfortunately those 2 issues are tied together and require mutually exclusive solutions (keep both tables and change the relationships or merge both tables). So I don't know the way to fix it, because I don't know enough about your data.

I suggest you write 1 paragraph without any database jargon (no mention of table or forms or fields) that explains what it is your organization does. Then one paragraph with light jargon that explains what this database will help your organization accomplish. Lastly, please respond with your thoughts to #1 and #2 above.
I will try without getting into proprietary detail. We are inspecting finished work for adherence to quality requirements. Each inspection of a finished process (which can contain many finished pieces) looks at all requirements that apply to that process. Any defects found are documented and sent back for correction. The data collection forms the basis for queries and reports that group, sum, etc to determine overall quality levels and identify problem areas.
 
Last edited:

zeroaccess

Active member
Local time
Today, 08:11
Joined
Jan 30, 2020
Messages
671
So you can't put a continuous form in a continuous form, but I think I've found the right structure thanks to Albert Kallal. 2 separate subforms, one filtered by the other.

grid2.jpg


Continuous subform 1 (left) would be Samples, and continuous subform 2 (right) would be Errors for each sampled item. I would perform the table split as proposed above to form the record sources. This should work - right?
 

zeroaccess

Active member
Local time
Today, 08:11
Joined
Jan 30, 2020
Messages
671
That is exactly what I showed in my picture??
Sorry, I guess it is - I was having trouble making sense of what I was seeing. Not dogging the mockup design, though!

I also misinterpreted your post - I thought you were answering "yes" that I could put a subform in a subform but that won't work since mine need to be continuous.

Still, you pointed me in the right direction - I hadn't even considered a 2nd subform. I will implement this and post the result here.
 

Micron

AWF VIP
Local time
Today, 09:11
Joined
Oct 20, 2018
Messages
3,476
I thought you were answering "yes" that I could put a subform in a subform
Look again. It is a continuous subform in a continuous subform. Now I didn't bother with linking, and that might be what you mean. You're saying you can do it but cannot link them (can't imagine why not as long as the proper linking fields are there for both) or just that you cannot do it for continuous forms at all? I'm having difficulty in understanding the issue you're having with this.

Child0 contains subform form7sub. Form7sub contains child13 (in the footer - perhaps that is your issue). Child13 sourceobject is a form called Calendar. As you should be able to see from my pic, both are continuous forms. What might not be obvious is that it's a cf on a cf. Maybe I should have posted a pic of the design as well.
 

zeroaccess

Active member
Local time
Today, 08:11
Joined
Jan 30, 2020
Messages
671
When I dragged the new subform into the existing one, Access warned me that it wasn't possible, and that it was changing it to single form. So they both need to exist on the main form.
 

Micron

AWF VIP
Local time
Today, 09:11
Joined
Oct 20, 2018
Messages
3,476
Did you add a subform control first? You can't simply drag a form onto a form.
 

zeroaccess

Active member
Local time
Today, 08:11
Joined
Jan 30, 2020
Messages
671
Yes - the message appears when adding a subform control to the existing subform detail section. I found it can be added to the footer of the subform, but I don't think that's the arrangement I'm going for.
 

Micron

AWF VIP
Local time
Today, 09:11
Joined
Oct 20, 2018
Messages
3,476
OK. As I mentioned that's what I did. Not sure why that wouldn't work for you. Obviously you can't add it to the detail section, otherwise I suppose Access would think that the subform needs to be repeated for each record, which would make no sense. So the detail section shows the upper level continuous form and the footer shows the lower level continuous form right below it. Unfortunately that doesn't seem to work for you.
 

zeroaccess

Active member
Local time
Today, 08:11
Joined
Jan 30, 2020
Messages
671
Right, and it's mostly an issue regarding what is the best use of screen space. On this form, the best layout will be to put them side by side so they basically sit exactly where they are now, but with a small gap. It could be a neat feature of access to allow moving the footer to any side of the form, but it is what it is.

I'll be using the method explained by Minty and Albert here:

https://stackoverflow.com/questions/42436632/ms-access-nested-continuous-form
 
Last edited:

Minty

AWF VIP
Local time
Today, 13:11
Joined
Jul 26, 2013
Messages
10,355
You can have multiple subforms on a single form, and they can all relate to different things, either each other (not so straightforward), or a field(s) in the main holding form.
I think my record was about 10. Some of them were literally a single field, made to look like a normal main form control.
 

zeroaccess

Active member
Local time
Today, 08:11
Joined
Jan 30, 2020
Messages
671
Do I need the ID of records in subform 1 in subform 2 as a foreign key? I think I do.
 
Last edited:

Minty

AWF VIP
Local time
Today, 13:11
Joined
Jul 26, 2013
Messages
10,355
You have to put a control on the main form (normally Hidden) that has the current value of the SubForm1 PK. You then use that as the Master/child key for SubForm2
The usual way to set its value is on the current event of Subform1.
 

Users who are viewing this thread

Top Bottom