Solved

Access unit of M? for products

Posted on 2014-02-02
14
394 Views
Last Modified: 2014-02-06
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....
0
Comment
Question by:Rayne
  • 7
  • 3
  • 2
  • +2
14 Comments
 
LVL 39

Expert Comment

by:als315
ID: 39827376
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39827514
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
 
LVL 84
ID: 39827525
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
 

Author Comment

by:Rayne
ID: 39827596
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39827600
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
 

Author Comment

by:Rayne
ID: 39827608
...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
 

Author Comment

by:Rayne
ID: 39827631
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 57
ID: 39827673
<<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
 

Author Comment

by:Rayne
ID: 39828611
That i awesome Jim. Thank you for the priceless feedback. I will ask. sure it rings a bell :)
0
 
LVL 84
ID: 39829286
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
 

Author Comment

by:Rayne
ID: 39838135
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
 

Author Comment

by:Rayne
ID: 39838138
let me know if I need to open another question or follow up within this question is ok...

tHank you
0
 
LVL 57
ID: 39838674
<<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
 

Author Comment

by:Rayne
ID: 39839123
Awesome Jim, you are one life saver, thank you for helping out :)

Respect
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now