If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table design - multiple values
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 (). 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. |
#2
|
|||
|
|||
Table design - multiple values
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. |
#3
|
|||
|
|||
Table design - multiple values
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. On the other hand, I can't see me creating a separate table for each and every measurement. "Fred" wrote: 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. |
#4
|
|||
|
|||
Table design - multiple values
On Mon, 27 Jul 2009 11:55:01 -0700, Derek
wrote: 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 (). 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). -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Table design - multiple values
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 "Derek" wrote in message ... 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. On the other hand, I can't see me creating a separate table for each and every measurement. "Fred" wrote: 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. |
#6
|
|||
|
|||
Table design - multiple values
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. |
Thread Tools | |
Display Modes | |
|
|