Inspection Database- Template or Example

Question

Inspection Database- Template or Example

Patrick Snyder 5

Hello,

I am trying to create a new database for an dimensional inspection report. See snippet below. Before I waste a bunch of my time creating tables, forms and such, I want to know the best way (and easiest) to do this. The snippet only represents the first four inspection points or features. Currently, I use a form that I created in Excel and it works well but I much rather start using Access so that I can view multiple inspection reports without having to search through many different folders.

User's image

So my vision of the form would look like the following: The header (not shown in snippet above) would have a part number and a report number that I would provide (unable to use the autonumbering feature), plus there would be some other key header details, date and such. Each different part (or report) that is inspected would have a different number of features requiring to be inspected. Example: inspecting cube, there is a width, length and height. This means there would be a total of three features, for each sample, requiring to be inspected. If I had five samples, that is a total of 15 different measurements.

The number of features are listed down in column B "Item". Key information about the dimension being inspected are columns C thru Q (column C through I is a merged cell). Each inspection requires, at minimum 5 samples to be inspected, this is columns R thru V. Columns R thru V is the as-found condition.

Merged Column C thru I- value that I enter.
Column J- value that I enter.
Column K- value that I enter.
Columns L, M, and N would be values that I need to enter.
Columns O, P and Q are fields that are auto-calculated based on Columns L, M, and N.
Columns R thru V are values that I enter but with conditional formatting change cell color is the as-found measurement is in tolerance based on values in Columns O, P, and Q.

The form should be able to support a minimum of 300 different features but ideally it should be infinite.

Any help to get me started would be much appreciated. I've done some searching for something similar but I am unable to find anything and I am sure the AI answer will not be much help either!

Thanks,

0 comments

2 answers

Your answer

Answer 1

George Hepworth 22,855 Volunteer Moderator

Database development begins with tables. Do nothing with regard to forms for display until your tables are properly designed and built. This can't be emphasized too much. Without properly normalized tables, your forms and reports will be crippled.

There are hundreds of good and excellent discussions of Database Normalization available if you search for that term. Look for Database Normalization, to distinguish it from other uses of Normalization.

Identify the data you need to track and store, then use what you learn about Database Normalization to set up some tables. We'll help you work from there to a properly normalized table design, at which point it will become worthwhile to discuss forms.

Patrick Snyder 5 Reputation points

2026-03-16T19:40:04.17+00:00

George,

I got something started. I created 3 tables, 1 with header details, 1 with the inspection data and 1 with footer details.

I've created a relationship of all 3 tables.

I've created a form but this is where I am stuck. The form should be like the snippet that I've showed in my original post. The form should be able to handle an unlimited number of features (in theory) to be inspected per part number and this is where I am stuck. When I go to the form, it allows me to start a new record each time I enter data for one feature, but it shouldn't work this way. I vision seeing blank rows and columns. Each new record will be a different inspection report. I just don't know how to do this.

Thoughts?
George Hepworth 22,855 Reputation points Volunteer Moderator

2026-03-16T19:59:41.12+00:00

Please show us the tables. Wait for the form until we're all on the same page with regard to the table design. Most of us rarely get it correct right out of the gate. For example, the names of the tables don't seem to me to reflect what I'd expect to see.

I'd expect to see tables like "Part", and "Tolerance" and "Inspection" and "InspectionResult". Maybe "UOM" and "Tolerances" to look up the standard units of measure you use and the tolerances that apply to each type of part.
Patrick Snyder 5 Reputation points

2026-03-20T19:32:45.62+00:00

George,

Attached is a copy of what I have so far, nothing private just some stuff to get started. I changed the file extension from .accdb to .txt. Just change it back and it will open just fine.

Inspection Report - Copy.txt
Patrick Snyder 5 Reputation points

2026-03-23T19:04:29.3166667+00:00

George,

I've been doing some reading and thinking, of course I am no expert with Access. Per my snippet below, I have a total of 15 different columns of data, within the portion of the report where I record each feature being inspected. I learned that the max number of fields (or columns) there can be in one table, per Google, it's 255. Each page of my inspection report has enough rows for 50 different features (or dimensions) that require to be inspected, 15 x 50 = 750 possible fields. Not going to work. Per Google, I need to normalize my database into smaller manageable tables.

If I just look at the far right 5 columns, the as-found measurement results, that would be 5 x 50 or 250 fields. And by the way, some of my inspection reports are 3 and 4 pages long, so I have some parts that have over 250 different features that require inspection. So, I guess that would mean that I would need a different table for every 50 features that require to be inspected: table 1 is features 1 thru 50, table 2 is 51 thru 100, table 3 is 101 thru 150 and so on. And this is just the far right columns.

I am just thinking about all wrong, isn't there an easier way to do this??? I look at templates online and examples and I don't see anything like what I am trying to recreate.

I've already sent you a copy of the database to look at, it has three tables; header, body and footer table. If I start calculating the total number of possible tables based on my current inspection report (6 pages or 300 features), I may have up to 23 different tables, 20 of which are just the actual inspection portion.

Isn't there an easier way? Isn't there a way that the table is generated based on values that I enter into a form? Couldn't I create the form and have a query or macro or VBA examine the form, if there is text boxes with data, then transfer that data into a table? Maybe it's just crazy talk or something...
George Hepworth 22,855 Reputation points Volunteer Moderator

2026-03-23T20:23:15.1566667+00:00

I missed your previous post with the attachment. I'll download now and look at it.

Normalization is more than simply decomposing tables into tables with smaller number of fields. That is a by-product of Normalization, though.

After I have had a chance to look through the sample, I'll probably have content questions. Sorry for the delay.
George Hepworth 22,855 Reputation points Volunteer Moderator

2026-03-23T20:42:55.4166667+00:00

There are abbreviations like "pn" and "fa". I have no idea what that means. Other names like "man_date" and "man_title" are ambiguous. As a general rule, clarity is much more important than being too concise.

A second thing I see is that there are two patterns often associated with "tables" imported from Excel worksheets. That's because Excel doesn't support relational data and you have no choice. In Access, though, we need to resolve them. For example, in table "header", approve, fail, deviate and resubmit are boolean (yes/no) fields. That design pattern is not appropriate for a relational table in Access. It needs to be ONE field, probably "Status" or "InspectionResult" or something along those lines. That field can contain one of four values. Instead of one field being marked "yes" and three "no", or even worse, one field being marked "yes" and the others null, you have one field which has one of four predetermined possible values.

The table called gauge illustrates another common design problem we see a lot. You have many fields with numerical suffixes to the same name. This is referred to as "encoding values in field names". It is resolved in a similar way. One field for "GaugeType", a second field for "GaugeCal" whatever "cal" means?

However, I don't know beyond that how to properly normalize this table because it's not clear what it is for, and what is in it. "pn" might be "PartNumber" I guess, but "fa" eludes me. I'm going to go out on a limb and guess this is the table for the tools you use, or the tools you make, or tools you calibrate for someone.

I'm going to make some guesses like that for a first attempt to suggest ways to normalize. I'll be back in a while with that.
George Hepworth 22,855 Reputation points Volunteer Moderator

2026-03-23T20:48:44.9566667+00:00

I got stuck right away. The table called gauge lists 6 different gauges in one record. How does that work? Do you really have 6 and only 6 gauges to track? I'm afraid I'm stalled until we understand what we're looking at.
Patrick Snyder 5 Reputation points

2026-03-24T14:30:52.6133333+00:00

George,

Completely understand your frustration...

Since writing my last entry yesterday, I probably made a bunch of unnecessary additions to the database, just because I wasn't sure when you would get a chance to look at the first draft. No big deal.

I went through each table and in the description column in Design View, I wrote a brief description of what each field is, I hope this helps. I purposely left the name of each field kind of ambiguous because of a comment that I received from another moderator on a different database. In the past, I made the field names pretty clear of what they meant but probably too clear. I had a couple of extra spaces and misspellings and as a result, the VBA and queries didn’t work. After several weeks, I found the error. The moderator pretty much said the opposite of what you’re saying. So, this time around, I tried to keep the field names short, all lower case and if I needed space then I used the underscore. I guess everyone has a method that works for them.

Another thing I did that is probably not necessarily, when I created my tables, I created them in the order of how the current inspection form is used, however, tables by default are listed in alphabetical order. So, each table has a number before it, then the name of the table. I know this seems silly after doing and telling you this but at the time, it seemed helpful for me and yes, if I need to add a new table after the fact, then it might be out of order. Crazy, I know…just ignore it.

I am attaching the latest version. Your question/comment about the yes /no for the pass, fail, etc... I deleted those and changed the field to "dispo" (disposition). This can be a combo box where there are four default values that the inspector can choose from.

As far as gauges, I was initially trying to mimic what was on the Excel version of the report but at times we may use more than 6 different gauges for an inspection. So, I created enough fields for 12 different gauges. I also created a separate table listing the different types of gauges. On our inspection report, we have a column that displays an abbreviation of the type of gauge used for the inspection. For a Caliper, we would use the letter "C". The table named "8tools" is this list (it’s just a list). I was thinking that the inspection form would have a combo box that the inspector would choose an abbreviation for the tool used, this would come from that table. The inspection report needs to show what gauge was used, including the ID. There is no easy way of creating a table of all the gauges we have. We have over a thousand different gauges but at most we may only use 2-7 or so gauges on a single part.

oh, another thing, I didn't assign a primary key, I found it useful not to have one on my previous database. A common field that will try all of the tables together will be field labeled "fa". FA is the fair number (first article inspection report).

Inspection Report - R2.txt
Patrick Snyder 5 Reputation points

2026-03-26T16:57:17.7266667+00:00

George, did you see my latest response yet?
George Hepworth 22,855 Reputation points Volunteer Moderator

2026-03-26T17:37:31.6533333+00:00

I'm a bit frustrated with the horrible layout and configuration here. New response don't always appear where I expect them, so no. I hadn't looked yet.

I will try to find time later to do that. With regard to lack of primary keys. It is integral to a properly performing relational database to have a primary key on every table. The only reason anyone would ever NOT have Primary and Foreign Keys would be to allow for non-conforming data to be stored, and that's a short path to lost data integrity.
Duane Hookom 26,825 Reputation points Volunteer Moderator

2026-03-26T19:37:10.5266667+00:00

There are lots of issues with your current table structure. Your tables all need primary keys that are used with foreign keys to create relationships. The 12 groups of repeating columns are not normalized. Minimally each of those 12 sets should create their own record in a related table.

I didn't look through the entire AI generated response, but it seems like a much better solution. I had implemented third party measure systems databases like this before retirement and the AI was much more realistic.
George Hepworth 22,855 Reputation points Volunteer Moderator

2026-03-26T20:11:01.3133333+00:00

I agree with Duane's assessment. I see some improvements from the original version, but this is still centered around the layout of a report, rather than the entities being tracked. The only table in this version that resembles a proper relational database tables is called "8Tools". It's not clear what function the "8" performs. It probably means it's "the eighth" table you created. And if so, that's not useful information at all.

"Header" and the two "footer" tables are apparently supposed to represent values to be placed in the header and the footer of a report. They have nothing at all to do with the actual data being stored, in other words. They are not useful in a database as tables.

**I strongly urge you to put this part of the process on hold. Study the rules of Database Normalization. It's a totally different mindset from Excel, and that is part of the reason you are struggling. It's not an Excel report. It's an Access relational database, and that requires a very different mindset and approach.

Here are some references to start with.**

https://learn.microsoft.com/en-us/troubleshoot/microsoft-365-apps/access/database-normalization-description

https://youtu.be/GFQaEYEc8_8?si=pG-7mDMbnnRdENsU

Your tables need to be named for the Entity in them, i.e. the name of a thing, like a "Part" or an "Inspection" Fields in the tables need to be the attributes of the entity, such as "PartNumber" for a Part, or "InspectionDate" for an inspection.

Along those lines, you do have a number of Inspection tables. You probably need ONE, not 4. One of the attributes of an inspection would be which kind of inspection it is, "1", or "2" or "3" or "4". That allows you to add additional inspections as your processes change, without creating new tables.

And finally, for now, you continue to use abbreviations for field names, preventing others from being able to easily grasp what they represent. Abbreviations tend to obscure information and seldom work out well.

I'm sorry to be so negative. Although the Tools table is a step in the right direction, there's a long way to go elsewhere.

Answer 2

Access does not have a built‑in “inspection database” template that matches this layout exactly, but it can handle this scenario well if the data is normalized and the form is designed on top of that structure.

A simple starting design that supports any number of features and measurements per part/report:

Reports (header information)
Stores one row per inspection report.
- ReportID (AutoNumber, primary key – internal only)
- ReportNumber (Text – the manual report number entered)
- PartNumber (Text)
- DateInspected (Date/Time)
- Other header fields as needed (customer, inspector, etc.)
Features (what is being measured)
Stores one row per feature on a report (width, length, height, etc.).
- FeatureID (AutoNumber, primary key)
- ReportID (Number, Long Integer – foreign key to Reports.ReportID)
- ItemNumber (Long – corresponds to column B “Item”)
- NominalDescription (Text – what is now in merged C–I)
- InspectionMethod (Text – column J)
- UnitOfMeasure (Text – column K)
- Nominal (Number – column L or K depending on how defined)
- MinusTol (Number – column M)
- PlusTol (Number – column N)
- LSL (Number – column O, can be calculated)
- USL (Number – column P, can be calculated)
- TotalTol (Number – column Q, can be calculated)
LSL/USL/TotalTol can either be stored or calculated in queries/forms as:
- LSL = Nominal − MinusTol
- USL = Nominal + PlusTol
- TotalTol = PlusTol + MinusTol
Measurements (as‑found values)
Stores one row per actual measurement.
- MeasurementID (AutoNumber, primary key)
- FeatureID (Number, Long Integer – foreign key to Features.FeatureID)
- SampleNumber (Integer – 1,2,3,4,5, …)
- MeasuredValue (Number)
This structure supports any number of samples (not just 5) and any number of features per report.
Forms a. Report form (single‑form)
- Record source: Reports table.
- Shows header fields (ReportNumber, PartNumber, Date, etc.).
b. Features subform (continuous form)
- Record source: Features table.
- Linked to Report form by ReportID.
- Layout columns to match the Excel header: ItemNumber, NominalDescription, InspectionMethod, UnitOfMeasure, Nominal, MinusTol, PlusTol, LSL, USL, TotalTol.
- Use calculated controls for LSL/USL/TotalTol if preferred instead of storing them.
c. Measurements subform (continuous or datasheet)
- Record source: Measurements table.
- Linked to Features subform by FeatureID.
- Shows SampleNumber and MeasuredValue.
To mimic the Excel layout of 5 fixed columns (1–5), use:
- A crosstab query or a form with 5 bound controls (Sample1–Sample5) that read/write to the Measurements table via VBA.
  Or keep it simple and show measurements vertically (one row per sample) in a datasheet subform.
Conditional formatting (in‑tolerance highlighting) On the Measurements subform, add conditional formatting to the MeasuredValue control:
- Condition 1: Field Value Is Less Than [LSL] → set background color (e.g., red).
- Condition 2: Field Value Is Greater Than [USL] → same color.
- Otherwise leave normal.
LSL and USL can be exposed on the subform via the relationship to Features (include them in the subform’s record source or reference them from the parent form).
Getting started quickly in Access
- Create a Blank desktop database.
- Create the three tables above and define relationships: Reports 1‑to‑many Features, Features 1‑to‑many Measurements.
- Use the Form Wizard to build the main Report form with a Features subform; then add the Measurements subform.
- Use queries to calculate LSL/USL/TotalTol if not stored.
Access templates and wizards can help with the basic database and form creation, but this inspection layout requires a custom design as outlined.

References:

Share via

Inspection Database- Template or Example

2 answers

Your answer