formula in a query?

[Leah]

I don't know where to start.
I have a multiple column table. One column in that table
determines the set of numbers I will use to perform
operations on other columns in that same table.
I need to do the operations on those columns and then take
the information and save it in another table.

Can someone help me?
Leah

 

[Leah]

In the table there is a column that will either have a "G"
or a "KD" as an entry. If this column has a G then I need
to multiply other columns (18bins, 15bins, 20bins, 38bins,
40bins) in that row by (25.2,21,28,53.2,56,
respectively). If that column contains a KD then I
multiply those columns in that row by
(22.5,18.75,25,47.5,50). I may not need a table, I just
don't know how to handle it when each row will be using a
different number to multiply. I will use these numbers in
subsequent queries.

Does this help?
thanks
leah

-----Original Message-----
Leah

Typically, relational database design rarely needs to "save [calculations]
in another table", which is what it sounds like you are trying to do

determines the set of numbers I will use to perform
operations on other columns in that same table.
I need to do the operations on those columns and then take
the information and save it in another table.

Could you provide just a bit more detailed information about what you are
doing? An example, with real values helps.

You might also explain what you want to accomplish, rather than, as you
have, how you are trying to accomplish something.

More info, please...

Jeff Boyce
<Access MVP>

.

 

[Jeff Boyce]

Leah

So, one approach would be to use the IIF() expression in a query, something
like:

YourNew18bins: IIF([YourColumn] = "G", 25.2, 22.5) * [18bins]

By the way, whenever I see field names like 18bins, 20bins, ... I wonder if
the data could benefit from further normalization -- embedding meaning in
field names is generally a give-away that data has been imported directly
from a spreadsheet, without considering that Access is a relational
database.

Good luck

Jeff Boyce
<Access MVP>

 

[Leah]

Jeff,
Thank you so much! It works perfectly!
I'm not sure what you mean by normalization, but those
numbers are input by a user into an access form. We deal
in sweet potatoes, and sometimes we dump different sizes
of bins in the dump tank and I need to know how many bins
of which sizes they dumped - 18 bushels, 20 bushels, etc.
Anyway, probably more than you wanted to know.
Thank you again for your help,
Leah

 

[Leah]

Thanks for the tip! I will make some free time to look at
that!
Leah

 

[Jeff Boyce]

de nada...

Jeff