how do i multiply data in a field by a constant then output produ.

[Guest]

I am trying to design a db for technical analysis results. i need to be able
to multiply the values in a regularly updated record and show the results in
a graph. can anybody help me please?

 

[Keith]

I am trying to design a db for technical analysis results. i need to be
able
to multiply the values in a regularly updated record and show the results
in
a graph. can anybody help me please?

Is the multiplier to be constant or variable?

Regards,
Keith.
www.keithwilby.com

 

[Guest]

the multiplier is a constant

 

[Ed Warren]

You can put the 'constant' in a query field:
MathProduct: [Field1] * 10 will multiply by 10.
upside: simple and easy
downside: can become a maintenance nightmare if you use it in a lot of
queries and then decide to change the value of the 'constant'. E.g. $ per
mile traveled.

or

you can have a table with one row, one field 'Constant' and use it to
multiply the values

MathProduct: [Field1]*[Constant]

Query:
SELECT Values.Value, [value]*[constant] AS Product
FROM [Values], Constant;

upside: easy to change the value of the constant
downside: another table to maintain.

or you can write a little function in code to provide the result.

Function MultiplyConstant (passedValue as double) as double
MultiplyConstant = nz(passedValue)* 10
End Function


Then in your query:
MathProduct:MultiplyConstant([Field1]) will give you the result you want.

Upside: fairly easy to maintain, just go into the function and change the
value of the constant.
Downside: some loss in performance due to having to 'calculate the value'
for each row using vba code.


Mess about with it for a while and you will have it figured out.

Ed Warren.

 

[Ed Robichaud]

Create a query that contains your recorded values, then add a new column (in
the query design view) that has the calculated expression:
NewValue: [OldValue]*[myConstant]

subsitute your own names, of course. Use this query as the record source of
your graph. Try the Chart wizard and/or PivotChart wizard to get you
started.

BTW, if you're frequently overwriting many fields in the same record, you
should think about redesigning your data tables so that you have an
historical list of all changes for each related record.
-Ed

 

[Guest]

fantastic - thank you

Create a query that contains your recorded values, then add a new column (in
the query design view) that has the calculated expression:
NewValue: [OldValue]*[myConstant]

subsitute your own names, of course. Use this query as the record source of
your graph. Try the Chart wizard and/or PivotChart wizard to get you
started.

BTW, if you're frequently overwriting many fields in the same record, you
should think about redesigning your data tables so that you have an
historical list of all changes for each related record.
-Ed

the multiplier is a constant

 

[Guest]

fantastic - thank you

You can put the 'constant' in a query field:
MathProduct: [Field1] * 10 will multiply by 10.
upside: simple and easy
downside: can become a maintenance nightmare if you use it in a lot of
queries and then decide to change the value of the 'constant'. E.g. $ per
mile traveled.

or

you can have a table with one row, one field 'Constant' and use it to
multiply the values

MathProduct: [Field1]*[Constant]

Query:
SELECT Values.Value, [value]*[constant] AS Product
FROM [Values], Constant;

upside: easy to change the value of the constant
downside: another table to maintain.

or you can write a little function in code to provide the result.

Function MultiplyConstant (passedValue as double) as double
MultiplyConstant = nz(passedValue)* 10
End Function


Then in your query:
MathProduct:MultiplyConstant([Field1]) will give you the result you want.

Upside: fairly easy to maintain, just go into the function and change the
value of the constant.
Downside: some loss in performance due to having to 'calculate the value'
for each row using vba code.


Mess about with it for a while and you will have it figured out.

Ed Warren.

the multiplier is a constant