Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Inspection Tables Picker

Inspection Tables Picker

Inspection Tables Picker

I’m trying to create an inspection report generated by clicking on multiple drop down boxes. To help me accomplish this, I’ve been doing research online as well as access training. However, I’m still in need of some help in setting up the tables and relationships properly. Here’s some background:
When we receive a component from a customer for inspection, and subsequently repair, I would like our inspector(s) to pick from multiple drop down menus to select recommended repairs.
The hierarchy to select a repair would be as follows:
1. Component
a. Section
i. Feature
1. Findings
a. Recommended Repair(s)
There could be multiple findings per feature each with its own list of recommended repairs. Also the findings and recommended repairs could be the same for different components. For instance, “No relevant findings” and recommended to “Use as is.”
The only information that I need the inspectors to manually input is: Customer name, inspector name, purchase order numbers, and date of inspection. The Recommended Repairs will be pre-populated for the inspectors for every component. Eventually I will need to create a form to revise existing components or add new ones.
Do I need tables for each item with a high likely-hood of repeatability? That is…something similar to the following?
tblComponents – Component ID, Component
tblSections – Section ID, Sections
tblFeatures, Feature ID, Features
tblFindings – Findings ID, Findings
tblRecommended Repairs – Component ID, Section ID, Feature ID, Finding ID, Inspector ID, Customer ID, Recommended Repair
tblInspectors - Inspector ID, First Name, Last Name
tblCustomers – Customer ID, Customer Name, Order ID
tblOrders – Order ID, Purchase order number, Date of Inspection

From what I’ve found online this should be setup as a “survey” type database.

RE: Inspection Tables Picker

Without knowing everything involved, having the tables you listed is a good start...

Data Normalization is the process of organizing the data the best way. There are rules aranged as Levels and there are other concepts that have names. If you look at the first 5 levels of Data normaliztion, you will be on your way to understanding how to structure your data.

Having said that, there are practical performance reasons not to normalize some things completely and may be worth considering. The most important thing to understand is if your database is not at least in Third Normal Form, you have done likely done something wrong.

Data Normalization Rules should be easily found with your favorite search engine. There is a Kennel (dogs) example that is very easy to follow (to the extent that it is easy to understand, the 5th rule hurts my head everytime I take a look at it).

One last thought, the first three rules can be summarized as "Everything must relate to the key, the whole key and nothing but the key." (I forget the origin of the quote).

RE: Inspection Tables Picker

Without knowing your business model, this is speculative. But rather than maintaining the OrderID in the Customer table, I would put the CustomerID in the Order table. Your current arrangement assumes that each customer will only have one order.

Also leaving the spaces out of field names is good standard procedure. Not all database platforms will tolerate them.

RE: Inspection Tables Picker

Good catch on the Foreign key placement between orders and customers... I clearly read over it.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close