Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2013

    Unanswered: Weights and Measure Query???

    This is my first database since the 90's when Access first came out.

    The purpose of my database is to keep track of an inventory. The items are stored in different locations and in containers at those locations. I need to know what items, quantities and sizes are in the containers. I also need to know all the different locations/containers any specific item is located when I do a search or report. I also need to know the total amount of a specific item is in stock I.E. How many total pounds/ounces of rice are in stock.

    Items are in fluid wt, dry weight, lengths, ect.
    For example I need a query that will add up my total weight of Molasses 3- 14oz + 2- 4 lb 2oz + 6- 128.5 oz and they are all in different containers.

    Am I asking Access to do too much?

    My test database

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    no you are not asking too much of the db

    to get round the different units of measurement problem you need a table that resolves each measurement type into so many base units. when you store a row you store the quantity and the measurement unit type.

    any query that needs overall stock then converts from that measurement unit into base units, and then if required converts from the base unit into whatever unti of measurement you need for the report.

    ferisntance a table MeasurementUnits
    Code a simple alphanumeric code that uniquely identiifes a unit of measure
    UnitType (eg M:Mass,T:Time,V:Volume,L:Length,A:Area)
    IsBaseUnit Boolean indicates if this unit is the base unit
    AsBaseUnits double (the amount of this item expressed in base units)

    g Gram M T 1
    code	DEscriotion	Type	IsBase	NoInBaseUnits
    g	Gram		M	T	1
    Kg	Kilogram	M	F	1000
    mg	Microgram	M	F	0.000001
    L	Litre		V	T	1
    ml	Millilitre	V	F	0.001
    gall	Gallon		V	F	4.54609
    gal(US)	US Gallon	V	F	3.785411784
    ..and so on.

    to find the number of a product in base units
    select P.SKU, P.Description, P.QTY, P.MUnit, P.UoM, M.Description, P.Qty * M.NoInBaseUnits as BaseQuantity, MB.UoM, MB.Description from Products as P, MeasurementUnits as MB
    Join MeasurementUnits as M on P.UoM = M.Code
    where M.UnitType = MB.UnitType AND MB.IsBaseUnit = true

    you could revise that query to covert directly to your end (display) units by converting through the base unit into another unit (assuming they were the same unit type).
    (P_Qty * M.NoInBaseUnit) / TargetUnits.NoInBaseUnit
    .. you'd need to extend the where clause to pull the TargetUnits conversion factor which presumably you'd specify
    where M.UnitType = MB.UnitType AND MB.IsBaseUnit = true
    AND TU.NoInBaseUnits = 'Gall'

    in the past Ive used the code to be also the short form of the unit eg g,m,Kg; in,ft,mm,cm,M,Km and so on. the only real problem area is the odd weights and measures adopted by the US where ferinstance a gallon is not a real gallon. A US gallon is 3.785411784 litres, whereas an Imperial gallon is 4.54609 litres. So you may need to use gall, gall(US) and so on to differentiate between imperial volumes and us volumes. how you'd label that in the boonies I don't know, whether you'd call the US gallon "gall", and the real gallon "gall imp" is up to you. you could just use an autonumber ID and then define the display code as an additional column. up to you.

    of course you could just switch to the SI / Metric system
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts