calculated fields within a table?

[Effie]

I am putting together data from a variety of sources -- these sources don't
necessarily use the same units. Within my table, I would like to have one
column listing the original value in the original units, one column listing
the original units, and then a 3rd column where the value is in standard
units -- I'd like this 3rd column to be calculated from the original values
and the original units (e.g., if the original units are in km, then the
original value will be divided by 1000 to give the value in meters). Any way
to do this just within the table? If not, could I set up some query to do
it? Would I have run the query again and make sure I haven't changed my
table name any time I correct an error in one of the original values?

 

[Jerry Whittle]

You can't do it a table level. Actually you don't even want to store such a
derived value in a table. Rather you want to do the calculation every time
it's needed in a query, form, or report. That way you don't need to worry
about doing updates when you fix an error.

 

[Dale Fye]

Effie,

Agree with Jerry that you should not store this value.

Create a conversions table (tbl_Conversions) which contains fields (UnitID,
UnitType, UnitName, ConvFactor) and populate it with values (the following
assumes that your standard unit of distance measure is meters):

ID Type Name Factor
1 Distance km 1000
2 Distance mile 1609.215
3 Distance nm 1851.852
.... (you get the idea)

You can use this table to populate the combo boxes where the individuals
select the type of unit and the unit name. Then, create a simple function to
compute the "standard units":

Public Function StdUnits(UnitType as string, _
UnitName as string, _
UnitValue as variant = Null) as variant

Dim strCriteria as string
Dim varFactor as variant

'I've used variants to make sure I can address NULL values
'that get passed to the function
if ISNULL(unitValue) then
StdUnits = NULL
else
strCriteria = "[UnitType] = '" & UnitType & "' " _
& "AND [UnitName] = '" & UnitName & "'"
varFactor = DLOOKUP("ConvFactor", "tbl_Conversions", strCriteria)
IF ISNULL(varFactor) then
StdUnits = "Unknown"
Else
StdUnits = UnitValue * varFactor
endif
endif

End Function

If you wanted to use this in a query, you would need to change the line that
includes "unknown" to return a negative value or something like -999 to
indicate an invalid entry.

Then, on your form, you could call this function to fill in an unbound
textbox based on the values in your UnitName combo and the UnitValue textbox.

me.txt_StdUnits = fnStdUnits("Distance", me.cbo_UnitName, me.txt_UnitValue)

In a query you might have

StdUnits: fnStdUnits([UnitType], [UnitName], [UnitValue])

HTH
Dale