locked
ms Access QUERY RRS feed

  • Question

  • Hello everyone
    I  have a question about MS ACCESS , i a,m designing a warehouse management system, and I want to calculate the balance of materials in the warehouse
    But the  units  are different
    For example, the user entered 2 tons of material x
    And then  enters 25 kg of the same material 
    Then pull put  700 kg  (from x )from the warehouse 
    !!!
    I have an Operation details Table  (
    material- quantity - the unit - the unit price -..)
    I have a query to calculate the balance but it does not take into account the units ,

    it   calculates the balance only  by numbers !!
    Any ideas ?!

    Monday, May 25, 2015 8:03 AM

Answers

  • You can write a simple little function which accepts the value and the unit as its arguments and returns the value as a common unit, in which you want the balance returned.  The function can then be called in a query which computes the current balance per material type on the basis of the functions return values, not the values stored in the table.  A suitable function to return a value in kilograms for instance would be along these lines:

    Public Function ConvertToKg(dblQuantity As Double, strUnit As String) As Double

        Dim dblFactor As Double
        
        Select Case strUnit
            Case "kg"   'kilogram
            dblFactor = 1
            Case "lb"   'pound
            dblFactor = 0.45359237
            Case "ton"  'US ton
            dblFactor = 907.18474
            Case "tonne"  'metric tonne
            dblFactor = 1000
           ' and so on
        End Select
        
        ConvertToKg = dblQuantity * dblFactor
        
    End Function

    For a more flexible solution which converts from any unit to any other unit you could firstly set up a table Units and then an 'adjacency list' table which is related two instances of the Units table by having columns UnitFrom, UnitTo and Factor.  To obtain the conversion factor for any combination of units would then merely be a case of looking up the row with the appropriate UnitFrom and UnitTo values.

    Ken Sheridan, Stafford, England

    Tuesday, May 26, 2015 11:53 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Office, your issue need some Query codes, I'll move your question to the MSDN forum for Access

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=accessdev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Tuesday, May 26, 2015 2:01 AM
  • You're going to have to give us the information of the Table, like what is the data type, ...etc.

    If assuming you just input a column/field as a number, you have to properly identify the Units on the input Form of the field by limiting or control the user input correctly.

    Use the Form's Textbox or a Label to indicate the Units so that the User doesn't or get confused about the Units.

    2 Tons and 2 Kg is a lot of difference. You'll have to define the method of Input to the Control's event. 

    Tuesday, May 26, 2015 2:32 AM
  • You can write a simple little function which accepts the value and the unit as its arguments and returns the value as a common unit, in which you want the balance returned.  The function can then be called in a query which computes the current balance per material type on the basis of the functions return values, not the values stored in the table.  A suitable function to return a value in kilograms for instance would be along these lines:

    Public Function ConvertToKg(dblQuantity As Double, strUnit As String) As Double

        Dim dblFactor As Double
        
        Select Case strUnit
            Case "kg"   'kilogram
            dblFactor = 1
            Case "lb"   'pound
            dblFactor = 0.45359237
            Case "ton"  'US ton
            dblFactor = 907.18474
            Case "tonne"  'metric tonne
            dblFactor = 1000
           ' and so on
        End Select
        
        ConvertToKg = dblQuantity * dblFactor
        
    End Function

    For a more flexible solution which converts from any unit to any other unit you could firstly set up a table Units and then an 'adjacency list' table which is related two instances of the Units table by having columns UnitFrom, UnitTo and Factor.  To obtain the conversion factor for any combination of units would then merely be a case of looking up the row with the appropriate UnitFrom and UnitTo values.

    Ken Sheridan, Stafford, England

    Tuesday, May 26, 2015 11:53 AM