Best (Easiest) to Multiply Columns by a Constant?

[SteveM]

I want to generate an Access query to multiply 3 columns by 3
normalizing constants to weight them. In other words, multiply by a
single constant value per column. I also want to maintain the
constants in a table to be able to adjust them using a form, so hard
coding values in the query is not sufficient.

Is there a "best practice" for doing this? I can think of some work-
arounds but I'm sure there must be a simple technique that I have not
figured out.

Thanks Much,

SteveM

 

[Arvin Meyer [MVP]]

An append query is the ticket if you plan on storing in a new table. If you
are planning on an existing table, use an Update query. Store the constants
in a table with a single row, or if you need multiple rows for history, use
a query to return a single row. Then use that table or query in your
Append/Update query. Using multiple rows from the constant table without a
join will create a Cartesian product.

 

[SteveM]

An append query is the ticket if you plan on storing in a new table. If you
are planning on an existing table, use an Update query. Store the constants
in a table with a single row, or if you need multiple rows for history, use
a query to return a single row. Then use that table or query in your
Append/Update query. Using multiple rows from the constant table without a
join will create a Cartesian product.

Arvin,

I see. Thanks. Great. I'm new to Access and thought the tables had
to be linked by common fields.

SteveM

 

[John W. Vinson]

I want to generate an Access query to multiply 3 columns by 3
normalizing constants to weight them. In other words, multiply by a
single constant value per column. I also want to maintain the
constants in a table to be able to adjust them using a form, so hard
coding values in the query is not sufficient.

Is there a "best practice" for doing this? I can think of some work-
arounds but I'm sure there must be a simple technique that I have not
figured out.

Thanks Much,

SteveM

I don't often disagree with my friend Arvin but I think I must here. His
suggestion will permanently replace the values in your table with the
normalized values, erasing any record of what they were before; and may cause
problems down the road if the normalization constants change. Perhaps that's
what you want to do, if so, go for it.

If on the other hand you want to *dynamically* weight the values, while
keeping the un-normalized values around, you can use a "Cartesian query".
Create a table only one record, with three fields, NormA, NormB and NormC
perhaps - your three normalization constants. Create a query by adding your
table and this normalization table to the grid with NO join line. Normally
this is A Bad Thing To Do since every record in your table will be matched up
with every record in the second table, giving a combinatorial explosion; but
in this case there is only one record so no explosion occurs.

In the Query put three calculated fields:

FieldANormalized = FieldA * NormA
FieldBNormalized = FieldB * NormB
FieldCNormalized = FieldC * NormC

with of course your own fieldnames.

The downside of this approach is that the calculated (normalized) values will
not be editable; but if you edit the underlying non-normalized values the
query will reflect the changes.

 

[SteveM]

I don't often disagree with my friend Arvin but I think I must here. His
suggestion will permanently replace the values in your table with the
normalized values, erasing any record of what they were before; and may cause
problems down the road if the normalization constants change. Perhaps that's
what you want to do, if so, go for it.

If on the other hand you want to *dynamically* weight the values, while
keeping the un-normalized values around, you can use a "Cartesian query".
Create a table  only one record, with three fields, NormA, NormB and NormC
perhaps - your three normalization constants. Create a query by adding your
table and this normalization table to the grid with NO join line. Normally
this is A Bad Thing To Do since every record in your table will be matched up
with every record in the second table, giving a combinatorial explosion; but
in this case there is only one record so no explosion occurs.

In the Query put three calculated fields:

FieldANormalized = FieldA * NormA
FieldBNormalized = FieldB * NormB
FieldCNormalized = FieldC * NormC

with of course your own fieldnames.

The downside of this approach is that the calculated (normalized) values will
not be editable; but if you edit the underlying non-normalized values the
query will reflect the changes.

John,

Thanks for the bit of elegance. I'm actually creating a normalized
sum (sum to up to 100%) in a separate field so the simple solution
works. But I like your idea for the more general case.

Appreciate the additional words of wisdom.

SteveM

 

[Arvin Meyer [MVP]]

I think either I wasn't clear, or you misunderstood. I did suggest a table
with a single record. Or for a history of the constants, multiple rows.

I assumed, perhaps incorrectly, that the constants were being multiplied
against current data, not all data. The update query, should change values
of the affected data only.

Apparently, you assumed (perhaps correctly <g>) that all values were
constants.

 

[John W. Vinson]

Thanks for the bit of elegance. I'm actually creating a normalized
sum (sum to up to 100%) in a separate field so the simple solution
works. But I like your idea for the more general case.

That would seem to call for a simple division calculation:

NormA: Nz([FieldA]) / (Nz(FieldA]) + Nz([FieldB]) + Nz([FieldC])

with appropriate precautions for the case that NONE of the fields have nonzero
data (to prevent Divide By Zero error annoyances).

 

[John W. Vinson]

I think either I wasn't clear, or you misunderstood.

I did misunderstand - sorry, Arvin!