Comparing columns and doing something with the results in Access

[bcw802000]

Thanks ahead of time you’re your expert advice.
Here is the scenario: I have several Queries that finally end up like the following.

Filekey | Date | Last Name | First Name | Sum of dollars | Sum of dollars |
186 |1/1/2007| | testlast| |testfirst| |109.95| |97.50|

I would like to compare the last two columns and make a new table named transactions that stores the following:

Filekey | Date | Last Name | First Name | sum of largest dollars |
186 |1/1/2007| |testlast| |testfirst| |109.95|

[Rabbit]

Why do you need to store the result in a table, why not just use a query.

In either case, you're looking for the iif() function.

[bcw802000]

Why do you need to store the result in a table, why not just use a query.

In either case, you're looking for the iif() function.

OK Thanks.... I do not really need to store it. I fugure when the transactions get to over 10,000 it would be faster to pull up pree quired data then run a compare iif() statment against all 10,000. Either way can you give me a usage example please.

Thanks!

[Rabbit]

OK Thanks.... I do not really need to store it. I fugure when the transactions get to over 10,000 it would be faster to pull up pree quired data then run a compare iif() statment against all 10,000. Either way can you give me a usage example please.

Thanks!

Well, the problem with storing it is what happens if the original numbers get changed? You'll end up having to run the make table query over again anyways. You can't expect the numbers to be static once they've been entered and the table's been created. I suppose it's different if you're looking at a static data set but I don't think that's the case here.

iif(Conditional Statement, Value if True, Value if False)
 

[bcw802000]

Well, the problem with storing it is what happens if the original numbers get changed? You'll end up having to run the make table query over again anyways. You can't expect the numbers to be static once they've been entered and the table's been created. I suppose it's different if you're looking at a static data set but I don't think that's the case here.

iif(Conditional Statement, Value if True, Value if False)
 

That makes since and the initial queries that get us to this poing will narrow down the data to a few hundred rechords anyways. I dont believe I have ever used the iif() statment. In the seniro I gave you can yougive me an idea of how I would use this statment ina query please.

Thanks Alot!

would I do

SELECT * 
from query1
where iif(sum1 < sum2, ......

or what should I do?

[Rabbit]

Please use the code tags.

Not quite, you're looking to create a new column right?

SELECT *, iif(Sum1 > Sum2, Sum1, Sum2) AS LargestSum
FROM SomeQueryContainingSum1AndSum2;
 

[bcw802000]

Please use the code tags.

Not quite, you're looking to create a new column right?

SELECT *, iif(Sum1 > Sum2, Sum1, Sum2) AS LargestSum
FROM SomeQueryContainingSum1AndSum2;
 

Sorry about the code thing. You are so AWESOME!!!!! It worked great! Thanks so much. I am so amazed someone like me can get this kind of support for free. What can I do to give back?

[Rabbit]

It's not a problem. Where would any of us be without some help when we start out? If you wish to give back, try answering some of the other user's questions. It's a win/win because I have learned way more from answering questions by testing out my solutions than I have from asking questions.