Newbie help: Extract a multiplication of two fields

[steve]

Hi,

I am beginning to learn some sql since I need to construct the strings
through a VB interface.

Everything seems to be going OK except for one problem:

I have a few fields (columns) of measurements . One column in one table is a
measurement (decimal number) and another one in another table is a
multiplication factor.
Example:

tblSize
Length Width
120 30
190.5 50.3
.... etc

tblMultFactors
codeMeas Factor
length 0.1
width 0.3
.... etc

so in my queries I do not want to extract the actual value in the (e.g.)
length measurement I want to extract the value *multiplied* with its
corresponding Multiplication factor (120 x 0.1 = 12 )

would I just say in the SELECT statement tblSize.Length *
tblMultFactors.Factor ?????

I tried to do this in the visual editor and it doesnt work, maybe it would
work in a pure SQL statement??

Any help would be appreciated.
TIA

-steve

 

[Marshall Barton]

I am beginning to learn some sql since I need to construct the strings
through a VB interface.

Everything seems to be going OK except for one problem:

I have a few fields (columns) of measurements . One column in one table is a
measurement (decimal number) and another one in another table is a
multiplication factor.
Example:

tblSize
Length Width
120 30
190.5 50.3
... etc

tblMultFactors
codeMeas Factor
length 0.1
width 0.3
... etc

so in my queries I do not want to extract the actual value in the (e.g.)
length measurement I want to extract the value *multiplied* with its
corresponding Multiplication factor (120 x 0.1 = 12 )

would I just say in the SELECT statement tblSize.Length *
tblMultFactors.Factor ?????

I tried to do this in the visual editor and it doesnt work, maybe it would
work in a pure SQL statement??

Regardless of where you do it, you have to explicitly
retrieve the values from the tables.

I VBA you could use the DLookup function:

AdjustedLen = varLength * DLookup("Factor", _
"tblMultFactors", "codeMeas= 'Length'")

In SQL you can use either DLookup (as above) or a subquery:

SELECT tblSize.Length * (SELECT Factor FROM tblMultFactors
WHERE codeMeas= 'Length')