Access unit of M? for products
Hello Gurus,
While trying to store product specific information in a newly create db, I think I have hit a wall.
Few basic assumption and facts:
There are item parts that are used to make machines or equipment for different projects (BOM)
Each item can be part of different bill of materials or projects. Each bill of materials can have different items (many to many)..so far so good
Now an item has only three attributes. The first two attributes are the code and description (which are unique ).
The third attribute is the length (which can be different for different unit of measurement -like meter for English or foot for American) -
item table
Code | description | length
0023 | pencil | 5(meter)
Code | description | length
0023 | pencil | 16 (foot)
The user doesn't want the DB to do any conversion (for foot to meter or vice versa as there are situations where the exact conversion would not be preferred rather an manually adjusted input be used) ). In that case I need to do something so that when they enter in a new item - And would want to add two different values for the [length] to correspond to the two unit of measurement - they can do it
so how do I store the information about the item's length that is specific to the English and american units so that the users can put in different lengths for the same item in the DB - ?
maybe like a many to many between item and unit of measure table?
again - the item's code and description are UNIQUE to the item. Its only the measuring metric [length] that will be different for the two unit of measurement....
While trying to store product specific information in a newly create db, I think I have hit a wall.
Few basic assumption and facts:
There are item parts that are used to make machines or equipment for different projects (BOM)
Each item can be part of different bill of materials or projects. Each bill of materials can have different items (many to many)..so far so good
Now an item has only three attributes. The first two attributes are the code and description (which are unique ).
The third attribute is the length (which can be different for different unit of measurement -like meter for English or foot for American) -
item table
Code | description | length
0023 | pencil | 5(meter)
Code | description | length
0023 | pencil | 16 (foot)
The user doesn't want the DB to do any conversion (for foot to meter or vice versa as there are situations where the exact conversion would not be preferred rather an manually adjusted input be used) ). In that case I need to do something so that when they enter in a new item - And would want to add two different values for the [length] to correspond to the two unit of measurement - they can do it
so how do I store the information about the item's length that is specific to the English and american units so that the users can put in different lengths for the same item in the DB - ?
maybe like a many to many between item and unit of measure table?
again - the item's code and description are UNIQUE to the item. Its only the measuring metric [length] that will be different for the two unit of measurement....
Another option is to add a field identifying whether the unit of measurement is in Meters or Feet.
Code | Description | Length | UofM
0023 | pencil | 5 | M 
Conversion, perhaps?
It's not uncommon to require users to enter values in a specific format (like Meters, or Feet, or Inches), and your program would then convert those values.
So you could store your values in Feet, but convert to Meter when needed:
1 Meter = 3.2808399 Feet
So if you store the value in Feet, then do this in your query:
SELECT Length, (Length / 3.2808399) AS Length_Meter FROM YourTable
It's not uncommon to require users to enter values in a specific format (like Meters, or Feet, or Inches), and your program would then convert those values.
So you could store your values in Feet, but convert to Meter when needed:
1 Meter = 3.2808399 Feet
So if you store the value in Feet, then do this in your query:
SELECT Length, (Length / 3.2808399) AS Length_Meter FROM YourTable
ASKER
Hello Everone,
Thanks for your comment so far. I just mentioned 1 [length] for explaining things very simple. In reality there will 12 such type of columns having measurements (volume wise, dimension wise) - in that case if I have to have a extra column for each of them- it would mean 12 extra columns - doesn't look feasible...
another point - IrogSinta
I can do that but how do I store the same itemcode twice with different values of the measurement within the same item table? violation of primary key isn't it. I have set the PK to be the item code itself due to db design
Scott- the user said they don't want it the conversion as they really want to manually enter the different values due to the complexity of the dimensions. so a simple conversion might mess up their complex way to get to a dimension value..
Thanks for your comment so far. I just mentioned 1 [length] for explaining things very simple. In reality there will 12 such type of columns having measurements (volume wise, dimension wise) - in that case if I have to have a extra column for each of them- it would mean 12 extra columns - doesn't look feasible...
another point - IrogSinta
I can do that but how do I store the same itemcode twice with different values of the measurement within the same item table? violation of primary key isn't it. I have set the PK to be the item code itself due to db design
Scott- the user said they don't want it the conversion as they really want to manually enter the different values due to the complexity of the dimensions. so a simple conversion might mess up their complex way to get to a dimension value..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
...in reality, there will be 20 columns specific to the item that will be unique to it and not numbers (so they are Not dependent on the unit of measure) ..its only the number measurements...that i am worrying about..
not all items will have a values for meter and feet (as they will be us only ...)but some items will need to have meter and feet both as they will be used both in usa and england...make sense?
not all items will have a values for meter and feet (as they will be us only ...)but some items will need to have meter and feet both as they will be used both in usa and england...make sense?
ASKER
Hello Scott,
I did checked my notes - in the short and long term - they would not want any calculation to be done by DB for these measurements - they would rather enter it...so in that case your solution stands ? :)
I did checked my notes - in the short and long term - they would not want any calculation to be done by DB for these measurements - they would rather enter it...so in that case your solution stands ? :)
<<The user doesn't want the DB to do any conversion (for foot to meter or vice versa as there are situations where the exact conversion would not be preferred rather an manually adjusted input be used) ). >>
I going to tell you right now from past experience, this is a really bad idea. Store a single measurement and the unit of measurement that it is made in.
Allowing users to input a measurement manually in two different units (feet/meters) is just asking for all kinds of trouble.
<<Scott- the user said they don't want it the conversion as they really want to manually enter the different values due to the complexity of the dimensions. so a simple conversion might mess up their complex way to get to a dimension value.. >>
The conversions between UOM's are well known and the only real issue is one of precision. It is far more likely they would screw up a measurement by entering it twice in different units rather then having a problem from a computer based conversion.
I've been dealing with Mfg systems in one form or another for thirty years and I can tell you they are asking for trouble if they force you to proceed along these lines.
I'd really like to see them give an example where they would have to convert from one unit to another and due to "their complex way to get to a dimension value" would cause a problem if the app handled the conversion.
Jim.
I going to tell you right now from past experience, this is a really bad idea. Store a single measurement and the unit of measurement that it is made in.
Allowing users to input a measurement manually in two different units (feet/meters) is just asking for all kinds of trouble.
<<Scott- the user said they don't want it the conversion as they really want to manually enter the different values due to the complexity of the dimensions. so a simple conversion might mess up their complex way to get to a dimension value.. >>
The conversions between UOM's are well known and the only real issue is one of precision. It is far more likely they would screw up a measurement by entering it twice in different units rather then having a problem from a computer based conversion.
I've been dealing with Mfg systems in one form or another for thirty years and I can tell you they are asking for trouble if they force you to proceed along these lines.
I'd really like to see them give an example where they would have to convert from one unit to another and due to "their complex way to get to a dimension value" would cause a problem if the app handled the conversion.
Jim.
ASKER
That i awesome Jim. Thank you for the priceless feedback. I will ask. sure it rings a bell :)
I'd really like to see them give an example where they would have to convert from one unit to another and due to "their complex way to get to a dimension value" would cause a problem if the app handled the conversion.I agree entirely. While I don't have 30 years dealing with mfg situations, I have quite a bit. Assuming that (a) the user-entered formula is correct and (b) the calculations your code uses are correct, then I've never seen a situation where the user-entered value was different than the one calculated by the formula. As Jim said, conversion formulas don't differ due to the product being handled - a meter is still 3.2808399 Feet, regardless of whether you're measuring round bar stock or the square footage of a mainsail.
ASKER
Hello Jim & Scott,
With regards to your comment " I'd really like to see them give an example where they would have to convert from one unit to another and due to "their complex way to get to a dimension value" would cause a problem if the app handled the conversion"
Please advise - this is what I heard back -
The expressed quantification (measure) can have an acceptance of up to 2.5% deviation from the actual value. if Any more deviation than that, can’t be tolerated or put in writing.
So if user put in their value in meter, or meter cube – will that converted value for feet or feet cube (via Access) will be within 2.5% of actual quantified value?
Please update ...what do you think - access will accurately do this conversion within the tolerance?
With regards to your comment " I'd really like to see them give an example where they would have to convert from one unit to another and due to "their complex way to get to a dimension value" would cause a problem if the app handled the conversion"
Please advise - this is what I heard back -
The expressed quantification (measure) can have an acceptance of up to 2.5% deviation from the actual value. if Any more deviation than that, can’t be tolerated or put in writing.
So if user put in their value in meter, or meter cube – will that converted value for feet or feet cube (via Access) will be within 2.5% of actual quantified value?
Please update ...what do you think - access will accurately do this conversion within the tolerance?
ASKER
let me know if I need to open another question or follow up within this question is ok...
tHank you
tHank you
<<The expressed quantification (measure) can have an acceptance of up to 2.5% deviation from the actual value. if Any more deviation than that, can’t be tolerated or put in writing. >>
As I said, it's a matter of precision. In other words, how many decimals places do you need your UOM's to go out along with the conversion factors to ensure no deviation from the inputted value.
Usually precision is handled in two ways:
1. Ability to handle more decimal places. Six is usually more then adaquate for any mfg process.
2. Using a different UOM i.e. Kilograms vs grams or feet vs inches. Different UOM's allow you to shift the decimal point in a measurement.
It's hard to offer anything specific without knowing what's being measured. What I'd do is ask them to give you what they feel is a worst case situation, then make sure you have enough precision to cover it.
or come back and give us a sampling of things they measured/inputted and we'll help you figure the precision required.
Jim.
As I said, it's a matter of precision. In other words, how many decimals places do you need your UOM's to go out along with the conversion factors to ensure no deviation from the inputted value.
Usually precision is handled in two ways:
1. Ability to handle more decimal places. Six is usually more then adaquate for any mfg process.
2. Using a different UOM i.e. Kilograms vs grams or feet vs inches. Different UOM's allow you to shift the decimal point in a measurement.
It's hard to offer anything specific without knowing what's being measured. What I'd do is ask them to give you what they feel is a worst case situation, then make sure you have enough precision to cover it.
or come back and give us a sampling of things they measured/inputted and we'll help you figure the precision required.
Jim.
ASKER
Awesome Jim, you are one life saver, thank you for helping out :)
Respect
Respect
Code | description | length (meter) | length (foot)
0023 | pencil | 5 | 16
If there could be more then 2 units, you can make separate table.