Creating a database (noob)

[Rick Stanich]

Back to your CMM data. I don't see where the data in the spreadsheet tells what piece the data is tied to. Is all the data associated with 1 piece?

Very good question. Yes the data is associated with one serial number or one piece. The file name maintains the definitions of the data such as Part Number, Operation, Job Number and Serial Number.
"4502730-135 32225-1 090408 M6.xls"
My intentions are to extract the data from the file name during the Excel formatting process or Access importing process.

I think I see a simpler solution to data processing. Each characteristic has an associated number (Bubble Number) like "#100" or "B#99". If there are multiples then the number would be like this "#100-1", "#100-2", Etc or "B#99-1", "B#399-2", Etc. There are also decimal formatted numbers like "#123.1" or "#125.1-1". Never any duplicates unless I make mistakes.

In the file you linked you see data in column B like this "TA(#89-1,2F3)", you can see where I can extract the number as defined above.

Reviewing the excel workbook you created, I see the import method is better than what I used. This method is more linear in data location. This should mean I dont need the data in Column A at all and can be deleted or ignored. Column C can be deleted or ignored as well. As long as I have the native CMM file I dont care what I do to the excel file.

Will this reduce the number of tables required?

 

[Rick Stanich]

The term "spDataValue", what is "sp"? I am not sure how to set this up.

This is what I have so far for a database. Hopefully I have been following your instrunctions correctly.

 

[jzwp22]

The sp at the beginning of a field name designates a single precision numeric field. Not everybody uses the prefixes such as sp, txt, fk, pk etc. and some people use different ones. It helps me identify the different data types. The pk and fk helps me distinguish a primary key from its corresponding foreign key. Some people just use ID but when you have multiple tables and you are trying to join them in a query having just ID makes it a real headache trying to figure out the relationships.

The number of tables in a database is irrelevant, the key is to set up the correct table structure for your application. And as it turns out, the table structure is the most critical part of a successfull relational database application, so it is well worth the time & effort to get it correct at the start.

I modified the relationships in your tables to enforce referential integrity. This prevents what are called orphan records (like cmm data with no corresponding piece #). The DB is attached

Regarding the CMM data, I think we can simplify some more. The deviation field is just a calculated value so you should not store it. The same is true for the misc2 column (In/Out). That can be calculated as well. So that leaves, nominal, upper tolerance, lower tolerance and the actual value and of course the bubble value. I'm not sure what misc1 is about with the +++.. What is its significance?

Of course, I have a couple more questions. Regarding the bubble #, does it stay the same from one piece to another piece of the same part #? And do the nominal, upper/lower tolerance values follow that bubble #?

 

[Rick Stanich]

The field for Misc1 is not needed, I simply forgot to mention it before.

Of course, I have a couple more questions. (1) Regarding the bubble #, does it stay the same from one piece to another piece of the same part #? (2) And do the nominal, upper/lower tolerance values follow that bubble #?

1. Yes
2. Yes

 

[jzwp22]

OK, things are getting clearer. Since the bubble and its associated nominal and upper/lower tolerances are tied to the part and each part can have many bubbles, we need a table to handle that. Within that table we'll capture the nominal & tolerance values. This way you would only need to import the bubble, nominal, upper/lower tolerances once for each part number. Then we need a table to tie the measured values & their corresponding bubble value to the piece; I took one of your existing tables and modified it accordingly in the attached DB.

I thought that would wrap up the table structure until I saw the field txtPartRev in your tblParts. This suggests to me that a part may have many revisions. I must assume that a revision can change the bubble values and their associated nominal and tolerance values. If my assumption is correct, this will require changes in the structure that will impact several tables and the joins between them. Now that we have progressed this far, do you think you can identify what needs to happen to the structure to accommodate many revisions of parts?

 

[Rick Stanich]

Your assumption is correct. The data needs to be tied to the PartNo, PartRev, SerialNo and JobNo. This creates traceability.

do you think you can identify what needs to happen to the structure to accommodate many revisions of parts?

I have to admit, I may have goofed on this. I ended up with "tblParts" and "tblParts_1" in Relationships. I deleted tblParts_1 but now I am more unsure of the end result.
On that note, I cannot.

 

[jzwp22]

In the relationship window having a _1 or _2 etc. after a table name just indicates that you are showing the table more than once in the window, it does not create another table--i.e. no harm no foul.


If a part can have many revisions, then you are dealing with a one-to-many relationship as we have previously seen. A one-to-many relationship warrants a table to handle it.

tblPartRev
-pkPartRevID primary key, autonumber
-fkPartID foreign key to tblParts
-txtRev

The jobs, the pieces and the bubbles (including nominal values and tolerances) are not dependent soley on the part anymore but on the part and its revision so to handle this, the joins to those other tables have to come from the tblPartRev rather than tblParts. I've attached the revised DB; compare the previous version to the new version to see what I did.

 

[Rick Stanich]

I will have to remember the "one-to-many" relationships in the future.
Inoticed in tblBubbles there is no entry for the field "Actual" and in tblPieceCMMData there is an entry called spMeasuredValue.
What is spMeasuredValue? And should I add spActual to tblBubbles?

 

[jzwp22]

Your actual measured values (spMeasuredValue) from the CMM are done on a piece so that goes in tblPieceCMMData. tblBubbles only holds the specification values (nominal and the tolerances) which are tied to the part rev and should never change for that rev (otherwise it would be a new rev, right?).

You will need a query to compared the measured values against the specified values.

 

[Rick Stanich]

You are correct again and I understand now how this may work.

 

[jzwp22]

Post back if you have question. Glad I could help out.

 

[Rick Stanich]

jzwp22, thanks for all the help!
I am working on querries and reports for now.

 

[jzwp22]

You're welcome. Let us know if you run into difficulties.