Table design - multiple values

[Derek]

Re-posting in the correct sub-section. I originally posted in "Queries" where
it obviously shouldn't be. I'm not a serial-poster, honestly o).

I need to make a table to store multiple measurements on parts and I need
some ideas how to proceed.

Each measurement job consists of up to 289 measurements; the property being
measured is identical for all locations, e.g. thickness, but the part is
measured in 289 different locations. Cartesian or polar coordinates are used
to define the locations of each measurement on the part.

So, in the case of a 289-point measurement, for each record I will have 289
measurement values. For each measurement value there will be EITHER one
x-coordinate and one y-coordinate OR one radius and one angle.

What should my table look like and what is my primary key? I have a
gut-feeling that the answer is obvious, so I'm sure I will get a useable
answer from someone. Thank you.

 

[Fred]

You'll have to decide upon, name and identify the entity that created the set
of 289 measurements. To do this you'll need to answer (for yourself or us)
thee quesiton:

Are all of the parts being measured the "same part" (e.g. same part number)
?

Is there a unique identifier for each individual part (e.g. serial number)

Either way, what is the unique identifier for the act of taking the 289
measurements? Part serial #? Test serial number? Date and time of the
measurement set ? RFID tag #? etc.


Either way, you'll end up with a measurements table. If the particulars
call for storing the co-ordinates in their original system (cartesian vs.
polar) (vs. conversion to one or the other) then you'll need 4 fields for
those 4 possible co-ordinates.

 

[Derek]

The parts are usually the same, but in reality each part will be a unique
entity with a PartID.

The act of measurement will also be a unique event e.g. MeasurementJobID.

The set of co-ordinates may also change. For example, the standard test
might measure 10 points on the part. If a defect is found the part will be
re-measured with a 100-point coordinate set.

What I'm concerned about is having a table with x1coord., y1coord.,
measurement1value, x2coord., y2coord....etc. The table would be huge and
contain repeating data types.

 

[John W. Vinson]

Re-posting in the correct sub-section. I originally posted in "Queries" where
it obviously shouldn't be. I'm not a serial-poster, honestly o).

I need to make a table to store multiple measurements on parts and I need
some ideas how to proceed.

Each measurement job consists of up to 289 measurements; the property being
measured is identical for all locations, e.g. thickness, but the part is
measured in 289 different locations. Cartesian or polar coordinates are used
to define the locations of each measurement on the part.

So, in the case of a 289-point measurement, for each record I will have 289
measurement values. For each measurement value there will be EITHER one
x-coordinate and one y-coordinate OR one radius and one angle.

What should my table look like and what is my primary key? I have a
gut-feeling that the answer is obvious, so I'm sure I will get a useable
answer from someone. Thank you.

You'll need a one to many relationship from Parts (or Jobs) to Measurements.
Assuming you're working in two dimensions (X-Y or R-Theta, never X-Y-Z or
R-Theta-Phi) I'd suggest a structure like:

Parts
PartID <Primary Key>
<information about the part as a thing in itself, no measurements>

CoordinateSystems
System <Text, Primary Key, "Cartesian" or "Polar">

Measurements
MeasurementID <autonumber primary key>
PartID <foreign key to Parts>
System <link to CoordinateSystems, so you can decipher the numbers>
Pos1 <X or R as appropriate>
Pos2 <Y or Theta as appropriate>
Thickness <the actual value at that point>

Each Part would be linked to any arbitrary number of measurements by PartID.
I'd suggest creating a unique Index on the combination of PartID, System, Pos1
and Pos2 to prevent entering two different measurements for the same location
on the same part (unless you WANT to do so for statistical
error-of-measurement testing).

 

[Jeff Boyce]

Derek

John V's response points the direction you need to be headed. Don't even
consider "adding columns" to add measurements. That's how you'd do it with
a spreadsheet, but Access is a relational database.

If "normalization" and "relational database design" are unfamiliar terms,
plan to spend some time learning your way up that curve.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Fred]

A core point of all of the answers is that your "measurements" table has one
small record for each measurement. They are all linked back to a table which
lists sets of measurements. Or, more specifically, all of the measurements
in each "set" are linked to that "set" record.