Creating a database (noob) (1 Viewer)

[Rick Stanich]

I do not know how to begin a database based on the data I have, see attached txt file.
I had thought I could import the data from a text file but I am not finding an option to do so. Do I have to import into Excel and then to Access?
(Excel is easy for me as I know it well including VB.)

FYI - The attached file extensions from my CMM program is "OUT" (plain text), I changed it to txt to upload. If that makes a diference.
Please open the file in WordPad for propper line formatting, there is nothing special formatted but NotePad tends to wrap the lines different.

Any hints, tips or general instructions is appreciated.

Regards

 

[jzwp22]

Can you export your data out to a comma separated text file rather than just a plain text file? A csv file would provide a better way of importing it into Access

 

[Rick Stanich]

The CMM program only outputs a text type file but I can import this into excell and do with it what I wish.
I currently import the txt (OUT) file into Excel and extract the data I need to populate spread sheets but that is specific to that need.

I am attempting to expand the capabilities using Access, once I get a format (Table?) setup I can stumble through asking questions.

 

[jzwp22]

The line wrapping in the word pad file causes problems using the import routines in Access. Is there a way to post it in something other than word pad to prevent the wrapping & then I can see if Access can handle it?

 

[Rick Stanich]

I see I had "word wrap" on when using NotePad. Now the file reads the same as it does in WordPad, two lines per set of data.

"TOLA:x" signifies the start of a data set (where x is a numeric value) and the next "TOLA:x" starts the subsiquent data set, two rows are one data set.

This is how the CMM program formats its data and the only way I could change it is to manipulate it in Excel (due to file size and speed). The original attached file is about 100 times bigger in file size.

Edit:
I am waiting for the CMM to finish a run and I will see if there are other options.

 

[Rick Stanich]

It appears I am stuck with the format at hand. If I save the data as a csv it is the same format. Row 1 Column 1 will have the entire contents of the row in one cell and so on.
No different than if you changed the extension of the attached file to "csv".

 

[jzwp22]

Actually having the entire contents in one cell can work. You can open it in Excel and then use the text to columns utility to parse the data. Then you can import the parsed data into Access. There may be a way to set up an import spec in Access to do the parsing also, but I have not used it. It is in the advanced section of the import text option when creating a new table. Alternatively, you maybe able to create a custom parsing routine using VBA code within Access.

 

[Rick Stanich]

The Excel idea I have been using all along.
As for Access I will try the importing and see what happens.

Thank you for your help!

 

[Rick Stanich]

Importing works fine, looks quite like excel.
Now to figure out what to do with the data.

I have a worksheet of some ID's (auto numbering) and fields (1 thru 8). What I cant see is how to tie this to a specific Job Number and Serial Number. Traceablity is critical.

I need a tutorial.

 

[jzwp22]

Now that you have the data in Access, you have to create your table structure. You will most likely have to move the data from the imported table into the tables that make up your final table structure. To help you with the table structure, we have to understand in more detail what you are doing and how the data is related. You mention job number and serial number. You will need a table to hold the job information.


tblJobs
-pkJobID primary key, autonumber
-txtJobNo
other job related fields


I'm not sure where the serial number fits in.

Is the CMM data tied to a job or a part that is related to a job?

 

[Rick Stanich]

The CMM data is tied to the job, serial number and a part number, there can be many pieces for a job and each has a serial number (traceability).

I should have defined part and pieces as seperate entities.

The critical traceability is:
Part Number
Serial Number
Job Number
CMM Data
The above 4 items would be required for each piece.
Creating tables seems to be my stumbling block.

 

[jzwp22]

Let me ask some questions that might help flush out the table structure

Is a job related to 1 part or many parts?
Are there many pieces of the same part in the job?
Can the same part number be tied to many jobs?
Is the serial number related to the part or to the actual pieces?
Does the CMM data relate to a piece?

 

[Rick Stanich]

Let me ask some questions that might help flush out the table structure

1. Is a job related to 1 part or many parts?
2. Are there many pieces of the same part in the job?
3. Can the same part number be tied to many jobs?
4. Is the serial number related to the part or to the actual pieces?
5. Does the CMM data relate to a piece?

1. A job can be related to one or many parts of one part number.
2. Yes
3. Yes, each job has a unique value
4. Each piece, and is unique to each piece. Plus is related to a specific job.
5. Yes

As for the oroginal text file I attached I have found a better format option for the output that places the data for each feature into a single row, contiguous. I will post a new file when the CMM has completed.

 

[jzwp22]

Based on your responses, I would structure my tables like this


tblJobs
-pkJobID primary key, autonumber
-txtJobNo
other job fields

tblParts
-pkPartID primary key, autonumber
-txtPartNo
other part related fields

tblJobParts
-pkJobPartID primary key, autonumber
-fkJobID foreign key to tblJobs
-fkPartID foreign key to tblParts

tblPieces
-pkPieceID primary key, autonumber
-txtSerialNo
-fkJobPartID foreign key to tblJobParts

Now for the CMM data. I assume that you have multiple parameters that are measured by the CMM for each piece. This requires a 1 to many relationship, but first, I would set up a table to hold just the list of parameters that the CMM gives you.

tblCMMParameter
-pkCMMParID primary key, autonumber
-txtCMMParameterName


tblPieceCMMData
-pkPieceCMMID primary key, autonumber
-fkpkPieceID foreign key to tblPieces
-fkCMMParID foreign key to tblCMMParameter
-spDataValue

I would guess that you would want to have a unit of measure field. If the unit of measure for a parameter is constant then it should go in tblCMMParameter. If the unit of measure can vary then it should go in tblPieceCMMData

 

[Rick Stanich]

I am working on creating tables.
This one scares me.

Now for the CMM data. I assume that you have multiple parameters that are measured by the CMM for each piece. This requires a 1 to many relationship, but first, I would set up a table to hold just the list of parameters that the CMM gives you.

In some cases I have thousands of entries.
Fortunately I have this data formatted in Excel workbooks.

 

[jzwp22]

How many different types of parameters does the CMM measure? You might have thousands of datapoints but can they fit into a small number of parameters? Pardon my ignorance of CMM measurements.

 

[Rick Stanich]

Literally thousands of characteristics. A typical Feature like a diameter can have an average 3 charateristics, The diameter and two coordinates (Like X and Y axis). Get into GD&T and you can add at least two more characteristic minimum. If I have 500 features I can output approximately 1500 characteristics. Start using 3D features and you add more characteristics as in a cylinder, you would have a diameter, 2 coordinates, axial orientation and cylindrical form (Six characteristics for one feature).
As for data points, well, we wont want to go there.

Attached are two files. One is the imported Excel data and the other is the native CMM data (Mode 6, the prior attachment was Mode 5). Mode 6 is linear, each characteristics data is in its own row.

To answer your question, yes they will fit into a small number of parameters:
COORDINATE
DIAMETER
DIST_BETWEEN
POSITION
Etc.
In the Excel file Column A will show possibly all the basic parameters.

p.s.
Pardon my ignorance of Access

 

[Rick Stanich]

Also I dont understand what a "Foreign Key" is. There doesnt appear to be any info in the help file.

 

[jzwp22]

A foreign key is field that is used to link to the primary key of another table to establish the relationship between the two tables. For example, let's say you have a table of customers

tblCustomers
-pkCustID primary key, autonumber
-txtCustomerName


Each customer can have many manufacturing facilities

tblCustFacilities
-pkCustFacilityID primary key, autonumber
-fkCustID foreign key to tblCustomers
-txtLocationAddress

Let' say the value in the primary key field for the customer ABC Co. is =1. Let's say that ABC Co. has 3 manufacturing facilities. So they will have 3 records in tblCustFacilities. Each record will have a value of 1 in the fkCustID field so that those three facilities can be related back to ABC Co. and only ABC Co. Hence you establish the one-to-many relationship between the customer and their facilities.

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?

 

[jzwp22]

I took your text document and opened it Excel as a space separated file and it came out like the attached which I think would be more of what is needed to import into Access. Let me ask some more questions.

For a piece, will there be multiple locations at which measurements will be made?
Are those locations defined by the coordinates in your data?

There are multiple coordinates that define a location

I'm not sure I'm on the right track, but if so then we need more tables

tblPieceLocations
-pkPieceLocID primary key, autonumber
-fkPieceID foreign key to tblPieces

tblLocCoordinates
-pkLocCoorID primary key, autonumber
-fkPieceLocID foreign key to tblPieceLocations
-fkCoorTypeID foreign key to tblTypeCoor
-spCoorValue

tblCoorTypes
-pkCoorTypeID primary key, autonumber
-txtCoorName

You would have as many records in tblCoorTypes as you have types. Is a location defined by 3 coordinates X,Y and Z? If so, then you will have 3 records in tblCoorTypes.

Let me know if I am on the right track.