Solved Normalization and user workflow issues

[zeroaccess]

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.

 

[plog]

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]

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.

 

[Deleted member 147267]

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]

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.

 

[plog]

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.

 

[Deleted member 147267]

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

 

[zeroaccess]

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.

 

[zeroaccess]

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.

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?

 

[Deleted member 147267]

but I think I've found the right structure thanks to Albert Kallal

That is exactly what I showed in my picture??

 

[zeroaccess]

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.

 

[Deleted member 147267]

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]

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.

 

[Deleted member 147267]

Did you add a subform control first? You can't simply drag a form onto a form.

 

[zeroaccess]

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.

 

[Deleted member 147267]

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]

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

 

[Minty]

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]

Do I need the ID of records in subform 1 in subform 2 as a foreign key? I think I do.

 

[Minty]

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.