DB Design and dynamic data entry

[Zydeceltico]

Hi All -

We do several types of inspections of product where I work (e.g., welding, rolling, paint line, etc).

I have tables for each of these unique processes and inspection forms specifically focused on each one.

In an attempt to normalize even more, I have created two new tables: tblInspectionType and tblInspectionEvent.

I also have Lookup Tables for tblWorkstation and tblPartNumber. Each of these has two fields - one being an ID (autonumber).


The idea behind tblInspectionEvent is that it acts somewhat like a junction table to record an inspection OF ANY TYPE in a single table so that later I can query all inspections for a JobNumber across two important fields that coincidentally appear in all types of inspections. There are very, very few fields that occur in more than one type of inspection report but these two do - and they are important - but not PK material.

tblInspectionType is simply a Lookup Table with 2 fields (InspectType_ID and InspectionType (e.g., welding, rolling, paint line, etc)).

So I want to create a form (frmInspectionEvent) linked to tblInspectionEvent that has five fields: InspectionEvent_ID (autonumber PK), dDate, tTime, JobNum_FK, and InspectionType_FK (cbo that "looks-up" from tblInspectionType from frmInspectionEvent).

Remembering that I have unique forms and tables for each type of InspectionType - - I would like the user to make a selection from cboInspectionType and have a popup of the specified InspectionType appear (e.g., frmMillInspect).

I would like that form to be filled out and when the save button is clicked on that form (frmMillInspect) a record is created in tblMillInspect as well as a record of general data in tblInspectionEvent originally entered on frmInspectionEvent and before a selection was made in cboInspectionType.

Then I would like for frmInspectionEvent to requery itself to clear the record and prepare for a new record.

Is this possible?

How do I design this?

Thanks in advance,

Tim

 

[June7]

Is frmInspectionEvent a BOUND form set with DataEntry Yes? If you initiate record with combobox selection, this will conflict with code to create record.

Why don't you set up frmInspectionEvent as a main form with the other forms as subforms arranged on TabControl or use Navigation form?

 

[Zydeceltico]

Is frmInspectionEvent a BOUND form set with DataEntry Yes? If you initiate record with combobox selection, this will conflict with code to create record.

Yes it is.

Why don't you set up frmInspectionEvent as a main form with the other forms as subforms arranged on TabControl or use Navigation form?

I will look into that. I need to study up on forms and subforms. I'm always unclear how the tables behind forms and subforms work so I havent explored it much. If I am using subforms - given that the subforms represent the "front end" to individual tables on the "back end" do I need to establish relationships between the various inspection tables and tblInspectionEvent? I'm thinking the answer is likely yes.

As always - Thank You

Tim

 

[June7]

It is not mandatory to set relationships in Relationships window. Records are synchronized in subform container control Master/Child Links properties.

 

[Pat Hartman]

Remembering that I have unique forms and tables for each type of InspectionType

That's a problem. I've created several inspection applications for large companies such as Pratt & Whitney (airplane engines) and Clairol (shampoos and other hair care products) and in no case did I use separate tables for each type of inspection. You should look into normalizing the tables so that you don't need to do this. Think of it this way - If you had to track an attendance count for each day, would you create a table with one row for each year and one column for each day of the year? No, you would create a table with each day as a row. Inspections should use the same concept. Just because each attribute has a separate name, doesn't mean they should be stored as a column. Another example is an application that tracks expenses for an apartment building. Depending on where the building is located and what amenities it has, you will have different expenses. In the north we have to have a line item for snow removal, in Miami, we need one for pest control. You don't want to have to add a new column if you buy a building in a different location or with a different amenity. You want to just be able to add a row to your type table and log the value for a building.

Your inspection details would probably be displayed as a subform with the item being inspected and the inspection date in the main form.

It is not mandatory to set relationships in Relationships window

Relationships can ONLY be established in the physical database that holds the tables so relationships are defined in the BE database NOT in the FE and NOT using forms or queries. Relationships are specified so that you can enforce Referential Integrity. If you don't intend to enforce RI (big mistake), then there is no need to create a relationship. Joins and Master/Child links are temporary connections between tables/queries or a form and subform but relationships are permanent. This may be just semantics but "Relationship" has a specific meaning in a relational database.

 

[Zydeceltico]

That's a problem. I've created several inspection applications for large companies such as Pratt & Whitney (airplane engines) and Clairol (shampoos and other hair care products) and in no case did I use separate tables for each type of inspection. You should look into normalizing the tables so that you don't need to do this..

Hi Pat,

This has been mentioned to me before and I appreciate the level of detail and explanation you've offered here. I've been trying to wrap my mind around it since I saw your post a couple of hours ago.

Again - there are probably 20 or so inspection items on a welding inspection. A mill inspection has around 20 important discrete measures as well. We also fabricate doors that may or may not be assembled with the product on the mills or welders. Door fabrication inspection has its own set of discrete measures. There are probably 4 fields on each inspection - at most - that are found in all inspections. Oil Canning (yes or no) is one of them because it shows up mysteriously and randomly everywhere. 80+% of all the other fields are unique to the type of inspection.

I am probably just too thick headed - but following the one-table-for-all-types-of-inspections leads me to envision a table that has somewhere around 80 column fields. For any given inspection record of any type, only 20 or so column fields will hold any data and the rest will all be null.

Is this a good design or am I missing something? I'm probably missing something.

Thank you for your patience.

Tim

 

[Pat Hartman]

It is a lot to wrap your head around. You need five tables to describe the inspections and then record the results.
I built a database with the bare minimum of fields to get you started. The InspectionResults table probably needs a part number and a part table to relate to. I left the Result for InspectionResultsItems as text but it might need to be numeric or you might need two fields. One to hold a numeric result and another to hold a comment.

Try to add some data and see if it makes sense to you. Don't forget to examine the Relationship diagram.

 

[The_Doc_Man]

Pat's comments are spot-on. From your comments, it sounds like you are having trouble seeing the inspection as several fixed-form events when in fact it can be one event with a dependent LIST of several things that make it unique - a parent/child relationship.

Taking this approach means it becomes easy to build summary reports, unify labor-hour reports, unify cost reports, etc. The details are of interest when looking at detailed things The summaries are of interest when building overviews. In my not-so-humble experience, businesses need and want BOTH kinds of viewpoints. Having the ability to provide those two different-level views makes your project useful.

The comment about relationships is also correct, but I feel obliged to expand slightly. True, if you are not doing anything with relational integrity, you don't need a permanent formalized relationship. But if such a relationship exists in the real world, failing to record the nature of that relationship means your model is not being true to the process. You are using Access to make a programmed equivalent to your business data flow and in so doing, you are "mapping the territory."

First, if the relationship exists in real life, it should exist in the DB (in the back-end where the tables exist).

Second, having those defined relationships means that when you build things using the Access wizards, the wizards (as dumb as they are) can recognize and use the information in those defined relationships. This is a good thing.

 

[Zydeceltico]

It is a lot to wrap your head around. You need five tables to describe the inspections and then record the results.
I built a database with the bare minimum of fields to get you started. The InspectionResults table probably needs a part number and a part table to relate to. I left the Result for InspectionResultsItems as text but it might need to be numeric or you might need two fields. One to hold a numeric result and another to hold a comment.

Try to add some data and see if it makes sense to you. Don't forget to examine the Relationship diagram.

Hi Pat –
Firstly I would like to say thank you for taking the time to begin a design for me that I can tell is much more efficient. I’ve been studying the Relationships diagram and I am pretty sure I understand the majority of the work flow.

One thing that is not completely clear to me is your intention for the field “PassInspYN” in tblInspectionResults versus field “Result” in tblInspectionResultsItems unless the field “Result” is another value list type field that houses potential results for Items (ItemID) in tblInspectionItems.

Is that the purpose of the “Result” field?

I should mention that I do already have a tblParts that will act as a lookup table that I am able to import and link/relate to the InspectionResults table.
I’m unsure if that would link only to tblInspectionResults.

There is another issue that I have not brought up yet. We have roughly eighty products – some assembled from components that are likewise sometimes sold separately not as components but as standalone products.

Across all products there are subgroups of products (groups of 2 – 5 products) that have very specific characteristics unique to the subgroup and not in any way relative to any of the other subgroups or the entire group.

I’m wondering how to handle those subgroup specific “Items.”

I’m going to play with this for a while and see how badly I can break it.

Thank you.

Tim

 

[Zydeceltico]

Pat's comments are spot-on. From your comments, it sounds like you are having trouble seeing the inspection as several fixed-form events when in fact it can be one event with a dependent LIST of several things that make it unique - a parent/child relationship.

You are using Access to make a programmed equivalent to your business data flow and in so doing, you are "mapping the territory."

First, if the relationship exists in real life, it should exist in the DB (in the back-end where the tables exist).

Hi Doc -

I think you are right. I think I am having the "trouble" you describe above. Especially when it comes to product-specific inspection items. I'm having a great deal of difficulty making what appears to be a paradigm shift of perspective.

I might add that I'm pretty sure that both you and Pat are aware that I've been working on this for quite some time - over a year in fact. Truth be told - I've had a very good working model collecting data since September of 2018. We've been able to answer some very important questions already. The only thing is that I've treated each Inspection Type (i.e., Assembly, Millwork, Painting, Fabricating, etc.) as it's own database. I then just run a union query across tables that happen to be separate databases that have a few common fields - et voila. But I know I am not supplying the most elegant model and I also know that I am not 100% with what you have described above as "mapping the territory." This current effort is to resolve that - so - thank you for your insight - and validation!

I'm going to play around with Pat's design and see if I can come to grips with the shift in perspective that you describe.

Tim

 

[Zydeceltico]

One thing that is not completely clear to me is your intention for the field “PassInspYN” in tblInspectionResults versus field “Result” in tblInspectionResultsItems unless the field “Result” is another value list type field that houses potential results for Items (ItemID) in tblInspectionItems.

Is that the purpose of the “Result” field?

Hi Pat - Are you able to provide some clarification pertaining to my quote above?

Thanks!

Tim

 

[Pat Hartman]

PassInspYN - my thought was that since each inspection has multiple steps, you might need an overall indication of whether an inspection is passed. If you can "calculate" pass/fail based on the contents of the inspection details, then this flag would be redundant. If it is something that a human has to set, it belongs in this table rather than with individual details and rather than Y/N, it might be better to be an InspectorID and a separate field for InspectionDate. I'm just guessing at what you might actually need.

The point of the schema as I designed it is that you can create as many unique groups of inspection steps as you need. You initially said they were grouped by type. If some products need custom inspections, then custom1, custom2, or whatever would just define additional types. Use part number or something that you can easily control to name them. The number of "types" is infinite. The number of different steps is infinite. You create a table that links the steps to a type so it doesn't matter what comes down the pike. You never have to modify table structures or forms/queries/reports. You just add new rows to the type table or the item inspections table and link them together.

This is not a common design pattern by the way. I've only used it for three different types of applications and inspections was one of them. The other two were questionnaires and insurance products. The common problem is that there is "something" that you cannot predefine or control so you have to have a way short of major design changes to add "something" on the fly.

In a questionnaire, you can hard code all questions and create tables with columns 1-n to accommodate 1-n questions or you can think long term and decide to make a schema that works for ALL questionnaires without any design changes or hard coding. In the insurance application, the company was new and was constantly designing new products. Each different product could require different data fields and you have no way of knowing up front what the next product would be or what new data fields it might require. Plus, each policy had a dozen documents that needed to be filled with variable data - ie, the insured name had to be accurately placed one or more times in every document. Plus some of the product types could have hundreds of endorsement variations, also with data fillable fields. Think about the nightmare of trying to get a set of documents containing variable values all filled correctly and consistently! This is what computers were made for. The application collected all the individual pieces of data and they using automation filled bookmarked fields in thousands of Word documents. In the old application, it took IT a minimum of four months to add the structure needed to define even the simplest new product. My app and a competent user could do the same thing in 4-12 hours without having to wait get onto the IT schedule.

 

[Zydeceltico]

The point of the schema as I designed it is that you can create as many unique groups of inspection steps as you need. You initially said they were grouped by type. If some products need custom inspections, then custom1, custom2, or whatever would just define additional types. Use part number or something that you can easily control to name them. The number of "types" is infinite. The number of different steps is infinite. You create a table that links the steps to a type so it doesn't matter what comes down the pike. You never have to modify table structures or forms/queries/reports. You just add new rows to the type table or the item inspections table and link them together.

This is not a common design pattern by the way. I've only used it for three different types of applications and inspections was one of them. The other two were questionnaires and insurance products. The common problem is that there is "something" that you cannot predefine or control so you have to have a way short of major design changes to add "something" on the fly.

Hi Pat -

Thank you very much for taking this time for me! What you have described above clearly describes our circumstance and also why I am revisiting the basic design.

You've given me a solid base to work from...and direction to move forward.

Thank You!

I'll post when I have it more developed but I want you to know that I am feeling far more confident now.

Tim

 

[Pat Hartman]

Great. Once you add some data and flesh out your actual requirements, post the db and we'll look at how you are doing.

 

[Zydeceltico]

It is a lot to wrap your head around. You need five tables to describe the inspections and then record the results.
I built a database with the bare minimum of fields to get you started. The InspectionResults table probably needs a part number and a part table to relate to. I left the Result for InspectionResultsItems as text but it might need to be numeric or you might need two fields. One to hold a numeric result and another to hold a comment.

Try to add some data and see if it makes sense to you. Don't forget to examine the Relationship diagram.

Hi Pat - I'm revisiting the effort - and model - that you provided for me.

After working through many other learning curves and having the skeleton of the db working really well for many needs, I am finally understanding where you were trying to direct me with this.

Thank you - again!

Tim

 

[Zydeceltico]

Pat's comments are spot-on. From your comments, it sounds like you are having trouble seeing the inspection as several fixed-form events when in fact it can be one event with a dependent LIST of several things that make it unique - a parent/child relationship.

Taking this approach means it becomes easy to build summary reports, unify labor-hour reports, unify cost reports, etc. The details are of interest when looking at detailed things The summaries are of interest when building overviews. In my not-so-humble experience, businesses need and want BOTH kinds of viewpoints. Having the ability to provide those two different-level views makes your project useful.

The comment about relationships is also correct, but I feel obliged to expand slightly. True, if you are not doing anything with relational integrity, you don't need a permanent formalized relationship. But if such a relationship exists in the real world, failing to record the nature of that relationship means your model is not being true to the process. You are using Access to make a programmed equivalent to your business data flow and in so doing, you are "mapping the territory."

First, if the relationship exists in real life, it should exist in the DB (in the back-end where the tables exist).

Second, having those defined relationships means that when you build things using the Access wizards, the wizards (as dumb as they are) can recognize and use the information in those defined relationships. This is a good thing.

Hey Doc Man -

I am FINALLY understanding what you were trying to tell me in this post! I am sloooowwwwwwww.

I am also fully understanding what Pat where Pat was trying to guide me and am developing around the table/relationship design that he provided but that I could not comprehend. I get it now. And - interestingly - I don't think it will take a ton of rework of what I already have in place. Just thought I would share that 1) I am grateful and 2) all of you folks efforts did not fall on deaf ears. I just had to learn quite a lot between Day 1 and Comprehension.

Thanks!

Tim

 

[The_Doc_Man]

Pat was trying to guide me and am developing around the table/relationship design that he provided

Just for the record, I made this mistake the first I talked to Pat, and SHE was very cool about it. She won't slap you around for it. Pat Hartman is DEFINITELY a lady!

 

[Pat Hartman]

Thank you both

@Tim,
I'm really happy that you stuck with it. As I mentioned originally, this is not a common design pattern but it is extremely useful when you find yourself having to deal with data that can't be easily consigned to specific columns due to the quantity of known values as well as the potential for new, currently unknown pieces of data.
@Doc
And you are a gentleman and a scholar.