Need some suggestions on table layouts

[chad101]

I am converting a log book over to MS Access. The log book contains information on inspection results for a metallurgical lab. I have one problem w/ my “inspection table” which I will explain later in the post.

This is the plan I am using to figure out my entities and attributes for the database. I wrote a short description about the hand written log book we use at work.

A log book is an array individual fields. 
Each field is made up of 11 columns 
(date, initial, log #, sent to customer, customer, dept, part #, load #,
 results, acc/rej, and defect code).

The [B]date[/B] pertains to the time a particular part was inspected.
[B]Initial[/B] indicates who checked the part.
[B]Log #[/B] starts at “1” Jan 1st and increments for each check throughout the
year
[B]Sent to cust[/B] asks if “inspected” samples are sent back with finished product.
[B]Customer[/B] displays owner of finished product.
[B]Dept[/B] indicates the processing area of plant.
[B]Part #[/B] distinguishes the part type.
[B]Load #[/B] may display what furnace and run count for the year to date. 
Or, tub number in an array of tubs processed through a continuous furnace. 
[B]Results[/B] give a list of values on test results.
[B]Acc/rej [/B] is a simple “yes/no” question asking if finished product is
acceptable to ship to customer.
[B]Defect Code[/B] represents a particular defect found during inspection

Tables
Log -- *LOGNum, LOGDate, &LOG_TECId, &LOG_CUSTId, &LOG_FURId &LOG_PARTId, &LOG_INSPId, LOGaccept, &LOG_DEFCode
Customer -- *CUSTId, CUSTName,
Part number -- *PARTNum, &PART_CUSTId,
Technician -- *TECHId, TECHName
Defects -- *DEFCode, DEFType
Furnace -- *FURId, FURType
Inspection type -- *INSPid,

* Primary key
& Foreign key

One problem with the Inspection table; we have different inspections for each part number. Some require 10 RC punches others only require 5 RC punches. Some Require a Total Case inspection. Others Require an Effective Case inspection or both TC & EFF Case.

Some part #s require all tests (i.e. Crush, Total Case, Effective Case, RC, & R15N)

Once I figure out the basic table setup I can use a query to build the final log. And develop a forum for the data entry. I’ll worry about the CPK reports later

Here is a sample database Let me know what you think. I still need help on the INSP table
Your Download-Link: http://rapidshare.de/files/10087240/SampleTable.zip.html

 

[JoeCruse]

You need to get customerID out of the part table. The Parts table should have a partID and partdescription. Even if parts are specific to a customer (internal or external), it's probably not necessary to have it in the parts table, as you'll already have that noted in your log table.

You might also want to make field names in the log table the same name as the corresponding field names in the related table; make tech into techID in the log table, since you're storing the related Long value for tech and not the text value.

The log in table log (consider renaming to logID), techID in table, and customerID in table customer, should be autonumber fields, in my opinion. As you enter new log entries, customers, or techs, the d'base will assign the number for you. With proper relationships set and setting up your forms and subforms correctly, the underlying log entries will be properly filled for you with lots less chance of users screwing it up. Build your data entry form/subforms based on queries of the tables, not on the actual tables. If you are a busy met lab, you'll likely have thousands of test records, so you can have the underlying queries for the forms set to either show a limited amount of records, or only allow to enter new records and show no previous ones (I prefer to allow to see 3-12 months of data).

For your inspection table, link it to the log table by using a FK of log (logID) in the Inspection table linked to PK of log (logID) in table Log. No matter how many inpections available, it sounds like they are pass/fail. If so, each inspection is merely a possible attribute in the inspection process. So I'd make the Inspection table up of: logID-FK to Log table, attribute-name of test used, result-result of the test. The Log table and Inspection table are set up in a one-to-many relationship, with RI set in place. Unless I'm missing something, the Log table is the main table, and the Inspection table will hold inspection results for each inspection done for each part. This Inspection table would then be best built in the manner above, with the Inspection table having possibly multiple records for every single entry in the Log table. I'd stay away from the format you set your Insp table, because that's looking like a spreadsheet, not a relational database table, which defeats the purpose of going to Access.

For Cpk's, make a cross-tab query with the Log and Inspection tables, and you can then send the results to either Excel, or to most any decent SPC program out there (I use SQC Pack, and do this all the time for customers with alloy chemistry shipping data for automotive stuff).

I'm not sure I completely understand what you want, but I hope this helps a little. I've been SLOWLY developing our own lab app for several years now, and am just lately learning what a normalized table structure should be for an inspection laboratory application. If you're unsure of what table normalization entails, do a search here on it, as there are many good posts on the subject. Then post back with any questions. You "could" keep your table Insp as is and probably do okay (I set up several tables for us in a similar fashion when I first started and did not know better), but normalizing it all will give better performance, especially if you generate a lot of data. And if you start off wrong and later go to normalize it all, it is a MAJOR pain in the butt to get several years worth of data moved into a more normal structure (I have been finding out this personally in the last few months).

 

[chad101]

Wow, thanks allot! I cannot describe how much that helped. I plan on reworking tables tonight. My main problem with the inspection table is different parts require different inspections. I don’t want a table with 10 different columns for each inspection type. This would cause blank fields and unneeded space as the database grew. The inspection work we do for the big 3 automakers is in the tens of thousands yearly. So size control and query speeds is a big plus. I was thinking about creating a separate table for each inspection type but I have now idea how I would tie this into the part number or customer table. Once again thank you for the help and please drop another line if you have any other suggestions. I’ll upload the edited tables tonight for possible review?

Thanks a million
Chad

 

[JoeCruse]

Chad,

it doesn't matter if different parts require different inspections with this setup. If I understand you correctly, each part received is logged and then tested. The Log table catches the part then. The related Inpection table will have the logID in it for each required test and corresponding result. So it is all tied together with these 2 tables.

Just so I'm clear: You have several possible inpsections available for a range of parts, correct? Are these inpsections pass/fail, or do they entail their own set of test attributes? Or do they only record one outcome (say a hardness test with one number reported)?

In my lab, we have 4 different instruments to run samples on, which can report from 2 to 75 different attributes/elements in one test. This complicates things a bit for us, especially as some of the instrument's software runs the results into their own database, where we have to pull results into our Lab app.

If we were working with tests where it was either a pass/fail result, or each test gave a single result, we could much more easily implement the table setup I'm suggesting to you. With us, customer shipments get logged into our system. The logID is used for subsequent testing of the sample on any of the 1 to 4 different instruments. I've had to relate these in a one-to-one relationship, because of the way we implemented pulling data from our main instrument's software. I'm still looking at having ALL test data pulled into one major result table, related to a log table by a one-to-many relationship. For us, it is difficult because of the way we have to run regular production samples.

Probably too much info . Good luck and post back if you can, as I'm interested in seeing how you go with this.

 

[chad101]

The inspections require a set test of attributes, entered by the user. Our total/effective case depth results range from .000 to .099 thousandths of an inch. RC, RB, R15N, R45N, and Birnell results range from 0.0 to 100.0 depending on the particular check. The only pass/fail test we have is MFH (metal file hardness); which I haven’t implemented into the database yet. The inspection log will contain a yes/no field asking if the particular part passed the over all inspection

Example checks


Fisher 23510A949
RC inspection – 10 pieces – range = 0.0/100.0
Straightness inspection – 100 pieces - “we only write down the range on this” i.e. 0/100 failed or 1/100 failed etc etc. (not implemented in database yet)

Fisher 23511 A840
RC inspection – 10 pieces – range = 0.0/100.0

Tenneco 436610
Crush inspection – 10 pieces - range = .000/.099 (table not made yet)
Total Case inspection – 2 pieces – range = .000/.099
Core RC inspection – 2 pieces – range = 0.0/100.0 (table not made yet)
MFH – 2 pieces - yes/no (not implemented in database yet)

Tenneco 43616
Crush inspection - 5 pieces – range = .000/.099 (table not made yet)
Total Case inspection – 2 pieces - range = .000/.099
Effective Case inspection – 2 pieces - range = .000/.099
RC inspection – 2 pieces – range = 0.0/100.0

P&A (Customer not in database yet)
Total Case inspection – 2 pieces - range = .000/.099
R15N inspection – 12 pieces - range = 0.0/100.0
R45N inspection – 12 pieces - range = 0.0/100.0
MFH – 2 pieces - yes/no (not implemented in database yet)

Winzler (Customer not in database yet)
Total Case inspection – 10 pieces - range = .000/.099
Effective Case inspection – 10 pieces - range = .000/.099
MFH – 2 pieces - yes/no (not implemented in database yet)

We have hundreds of customers besides the Big 3 and all call for different inspections with differentiating piece counts used in the inspection. I uploaded the new design and fixed the tables based on the suggestions you gave me. My inspection table relationships is incorrect at the moment; but it revels the over all structure I am trying to create.

Your Download-Link: http://rapidshare.de/files/10309334/SampleTable2.zip.html

Thanks, Chad

 

[chad101]

One more question…

I was over looking my design and I think the most appropriate way to design my form is by having the main log table in the default form then once the log info is entered; the subform containing the inspection table is loaded tblINSP PK = tblLOG PK. This table will contain different fields for each “type” of inspection preformed on part number located in log table. The user then picks the inspection types and hits “enter” this will load another subform which will contain a table representing each “inspection type”. Each table will show only one “blank” record, for data entry, on separate lines. All PKs in the “inspection type” tables = tblINSP PK.

I think that makes sense lol! I took a perquisite class based solely on MS Access last winter. But we stopped at macros. The final two chapters talk about VBA Coding in MS Access but nothing really helpful.

My only question is how can you make a subform that loads multiple “inspection type” tables based on the result entered in the inspection table?

I did a quick screen shot of a sample forum I designed to help clarify my question.

 

[JoeCruse]

Chad,

take a look at this. Your individual tables for the possible inspections (Brinell, etc) were still set up to resemble a spreadsheet. You want to move away from that if you go to a relational database.

I set up some tables and defined the relationships in the attached file. Note the names of tables and fields. Try to get into the habit of using a similar naming style for this kind of work, and avoid using names like "date" for field names in tables, because "Date" is a reserved word in MS Access.

You could use this setup and make a series of forms and subforms to enter your test data here. Base forms on queries, which are in turn based on the data tables.

I have a few more questions for you on sampling and test data, but it's late and bed time.

 

[chad101]

thank you

That makes more sense. Having a results table also allows multiple inspections on any given part number with out having to create different inspection tables with “checktype1” “checkype2” etc etc. That makes perfect sense now. Thank you! Drop me another line I’ll be happy to answer your question.

I’m going to create my queries based off the tables then implement them in the forms. I’m still trying to get my hands on a VBA snippet for loading the correct subform. I’ll try to explain it in pseudo code.

if (tblLog.logID = = tblInspection.logID)
{
  if (tblInspectionID = = tblResults.inspectionID)
   load tblResults.inspectionattribute && tblResults.result into the subforum;
}

else subform visible = false;

I know it looks like C++ which dose not resemble VBA what so ever. But the algorithm is similar to what I’m looking for in VBA code. I searched different websites offering VBA snippets; but, nothing very useful so far.

 

[JoeCruse]

Chad,

you don't necessarily have to use code. There are a few ways to approach it. You could have your main Log form, with an inspection subform and results subform directly on the main form. Link the forms by the Child:Master option in the main form's design view, linking inspection subform to main, then linking results subform to the inspection subform. Or you could click a button to open each subform as needed, with code opening the form and populating the linking field of the subforms.

Are you receiving one sample per part and running multiple tests on it, or receiving multiple samples of a part? Not sure how your sampling process for tests go, as I have no experience with a met lab.

Hope this is helping.

 

[chad101]

samples

Yes and yes, we receive multiple samples and sometimes do multiple inspections on each sample.


We receive 3 GM Truck shipments a day (we have hundreds of customers; GM is just one of the biggest). Each Truck contains 30 to 40 tubs of transmission pinions. Each tub (containing 40,000 pinions) is individually Heat treated. Then 24 sample pieces of this finished tub is brought to the metallurgical lab. I have to perform 3 different checks on these 24 sample pieces. I start with a superficial metal hardness check “*R45T”; R45T checks are preformed on a Wilson Rockwell . Each piece is punched once for this test and each result is recorded. I continue to do this for all 24 samples. Then, I do surface hardness R15N check and each result is also recorded. Once these two checks are complete; I cut 1 sample piece. This sample is checked for carbon enrichment under a microscope @ 200 times and measurement is recorded in thousandths of an inch (.000/.099). This process repeats for every GM tub.

All 24 sample pieces are inspected twice (R45T and R15N)
1 GM sample receives 3 inspections (R45T, R15N, and carbon enrichment)

This inspection process remains the same for different parts; we just do different “types” of inspections on different parts. It all depends on the customer and what inspection “types” they want. But for the most part “almost” all individual sample pieces from each load/tub(s) receive multiple inspections. I say almost all because we have one customer who wants 10 pieces inspected per load and 1 R150C hardness test on each sample piece.



*
R = Rockwell, 45 = Weight used, T= Metal penetrator type.

 

[JoeCruse]

Do you report one result per each piece tested, per tub of pieces, or per truckload of pieces? Are you logging in now every individual piece you test (that was my impression), or is each tub an individual log entry, or the entire truckload an individual log entry? If each piece taken from a tub is the log entry, I'm assuming you keep the tub's ID and truck's ID with it (though it depends on what quantity makes up an individual "lot"), and so this will need to be added to the table tblLog, as will the accept/reject part and defect code.

So is that your met lab in the link? I know this company, as I've sent the Louisville lab some Aluminum samples once before (we do WDXRF, but I don't have any standards to make a calibration for Al ingot alloy). It's been a while, 7 or 8 years.

This is a good thing you're doing, going from paper system to electronic. It will really enable you to deal with your data and customer reports a LOT easier.

 

[chad101]

Sorry for the delay, I work the 3rd shift and I stayed over and slept the second half of today.

For the moment we are still using the hand written log book. I was granted 2 months for this project since I go back to school next week. Right now when we check all 24 sample pieces we write down each result on the back of the process control sheet. Then we write the range into the log book ex (63.3/65.1 R45T, 66.2/71.9 R15N, and .0105/.012 CE). This result represents each individual tub (1/40 2/40 3/40 etc); we don’t log or track each truck delivery in the lab. When the database is finished we will not log the “range” anymore. We want to record all the test results for each tub that we would normally write on the back of the process control sheet. These results will be linked to this single tub. When we send a finished tub back, we give GM the furnace name and load number so we can look up the results if any questions are asked about a given tub.

No, I work at another lab in Toledo Ohio. We don’t have a website but I wanted to give you an example of a metallurgical lab. I liked this particular site because we perform identical tests. Except one, we are not licensed to inspect materials for the Dept of Defense and they are. So that’s one of the few minor differences between us.

I Made a few changes to the chad project. I used look up fields since my manager asked if I could increase the speed of data entry. I also added a acc/rej table and a furnace table. I have the project saved at work, I’ll upload it later tonight.

 

[chad101]

This is the new layout w/ furnace and acc/rej table. I also made a simple forum for the log.

EDIT
I worked on the queries Take a look @ QueryInspectionLOG (Chadproject2.zip)

Shouldn't inspection number increment everytime i enter a new inspection?

 

[JoeCruse]

Chad,

the tables are looking good, except for the lookup fields. If you do a search on them here, you'll find most of the experienced folks here abhore them. I, too, built some of my first stuff using them, because Access' sample DB's used them, but it's not good practice. You and especially your users should NOT be using tables to enter data, but forms. You also need to base the forms on a query, and not the table the data goes into. Make a query based on the data table and then base the form on that query. You can have the query limit the number of records shown in the form, keeping the load down on the traffic on the server that your backend tables might be residing on. In the forms, these fields that you have setup as lookup fields show as numbers if you take out the lookup. To help with data entry, set up these fields in the form as COMBO boxes, with the row source set by an underlying query based on your lookup tables.

In your tblLog, you'll need to add more ID field(s) for the log entry, given what you said on reporting results per tote. At a minimum, add a tote field to the table to record the tote# that the parts were sampled out of. If tote#'s repeat, you may need to make the Tote ID up of the lot# (if each truck comes with one) AND the tote#. This will allow you to trace and report results based on tote ID.

Yes, inspectionID should auto increment with each entry. When you set up subforms for inspection and results and tie them together, this should work out for you. For each subform, make a query, based on the underlying table. Right now, I'd go with your main form, and add 2 datasheet subforms for the inspection and results part, based on what you've described so far. You could also do the main Log form, click a button which brings up the inspection subform (datasheet), linked by logID, and add the inspections you intend to perform. Then go do the tests and bring the results to a workstation to enter. Open another Inspection form that either has a Results subform (datasheet) on it or is brought up by clicking a button. These would be linked by inspectionID. So you have a couple of ways at going at this.

I once set up an industrial hygiene sampling form as follows: Main form had the initial log of the event. A subform on the main form (datasheet) recorded the individual air sampling cartridges to be used in the test, linked by eventID to the main form. When we ran the hygiene samples, we sent them off-site for analysis. When the results came back, I open the form and go to the event. I click a button on the main form, and a Results subform (datasheet) pops up, populated with the test cartridge ID, found on the cartridge subform in the main form, automatically by code. I simply have to enter lab results in the rest of the subform, and then close it out. You might go with this format.

I have another application which does it another way: a main form, a related datasheet subform on the main form, and another datasheet subform on the main form, which is related to subform one. All of them work.

Another twist for you; Does any of your test equipment have electronic reporting capability? If so, can you set it up to dump the data so your Access project can automatically take it? It would be a nice feature, if possible.

On the queries, why have you set up queries on the Acc/Rej, Customer, Furnace, InspectionType, PartNumbers, and Techs tables? Oh wait, you can use them for forms to enter new items in, couldn't you?

Good luck, and keep posting back if I can be of help.

 

[chad101]

That makes sense; I have a new table called “tblLoad” which has loadID PK, logID FK, and load. My new form is using queries for data entry which is based off each table. InspectionID is now incrementing when I place it inside a subform on the main form =)). I used lookup fields for the tables because I was going to create the query on the table afterwards and then use the query as my forum. This would put a drop down box inside the forum for faster data entry. I’ll switch it and use combo boxes instead. I should have a beta form finished sometime tonight. I’ll upload it tonight; let me know what you think.

Thanks for all your help; I really appreciate it, Chad.

 

[chad101]

I have a quick question, My employer asked me to just produce a Cpk result on each part number, not a graph or report just a calculated field in the results form which will display the year to date Cpk on each part number as you scroll though the results.

I Know how to make a calculated field and but what in order to produce a Cpk on a particular part number I need to know the inspection “specs”. Say one part number calls for a crush inspection on 5 pieces and total case inspection on 2 of the original 5 pieces.
Lets say the specs call for a
Crush w/ a max deformation .015 thousandths and
Total case asks for a max of .018 thousandths.

Would I have to create another table which would hold these “spec” values, link it to the part table, and call this table from the main form when I do a Cpk calculation?

Also, my results field needs to hold decimals and whole numbers. Any suggestions?

Thanks Chad

 

[JoeCruse]

Yes, you'll need a table for specs. You already have a parts table, so use the partID in the specs table. You should only need a partID field, attribute field (say for attribute "upper" or "lower") and spec field. The key in this table will be composite: both partID and attribute field, since partID may appear many times.

You can run a crosstab query on the table to get an excel looking return, and use it to pull specs for a report that gives Cpk's.