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
