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....
RayneAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
they really want to manually enter the different values due to the complexity of the dimensions
In that case, then you'd have to store them in your tables in some fashion.

You could add those 12 extra columns in the Material table. If you think those are the only additional measurements they'll ever need, this would be the simplest (hint: don't believe them when they say those are the only measurements they'll need :)  ).

You could add another table to store Measurements, and link it back to that table:

tMeasurements
-----------------------
ID
ProductCode
Length_Meter
Length_Feet
Width_Meter
Width_Feet
etc etc

Or you could break it down further:

tMeasurements
--------------------
ID
MeasurementValue
MeasurementType
ProductCode

Where MeasurementType would be a value like "Length-Feet" or "Width-Meter"

If they'll be adding more Measurements down the road, this would be the better way. The drawback I see would be if you are expected to perform some form of calculation on this data - for example, the have a RoundBar with Length and Circumference measurements, and they want you to calculate Cubic volume. If so, you'd have to store the formulas needed for the expected Products, and use those to create that data. It could get messy very quickly ...
0
 
als315Commented:
If you have only 2 kinds of measurement units, you can add 2 columns:
Code | description  | length (meter) | length (foot)
0023 | pencil           | 5                         | 16
If there could be more then 2 units, you can make separate table.
0
 
IrogSintaCommented:
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 

Open in new window

Ron
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
 
RayneAuthor Commented:
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..
0
 
RayneAuthor Commented:
...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?
0
 
RayneAuthor Commented:
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 ? :)
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.
0
 
RayneAuthor Commented:
That i awesome Jim. Thank you for the priceless feedback. I will ask. sure it rings a bell :)
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
RayneAuthor Commented:
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?
0
 
RayneAuthor Commented:
let me know if I need to open another question or follow up within this question is ok...

tHank you
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.
0
 
RayneAuthor Commented:
Awesome Jim, you are one life saver, thank you for helping out :)

Respect
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.