Dont count negatives...???/

[MarieG]

I am querying from tables from another program.. When a user of the
program adds an item, then deletes it, the programs writes to the table like
this:

Item Count RVU Value Fee
Consult 1 1.50 $5.00
Consult 1 1.50 ($5.00)


My problem is that I need to Sum the RVU Values... I Summed the count by
counting the fee's and using: iif([fee]<0,-1,1) But I don't know
how to Sum the RVU's without counting them twice.. because if I tell my
query to show me everything >0, that takes out the negatives (that shouldn't
be counted), but the original charge shouldn't be counted either...


Ideas?

Thanks so much!!!

Marie

 

[Jeff Boyce]

Are you saying that you want to include only rows that have [Fee] >0? If
so, just include [Fee] as a field in your query, but uncheck the "Show"
checkbox, and remember to put ">0" (without the quotes) in the Selection
under [Fee].

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[MarieG]

No, not really... If there is a [fee]<0, I need to exclude that AND the
original fee that was charged... but I just now figured out how to do it!!
I added another column with this formula: iif([fee]<0,-[RVU],[RVU])
It worked great!!! It just makes sense.. Man, I love this junk!!

Thanks for your help though!!

Marie

Are you saying that you want to include only rows that have [Fee] >0? If
so, just include [Fee] as a field in your query, but uncheck the "Show"
checkbox, and remember to put ">0" (without the quotes) in the Selection
under [Fee].

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am querying from tables from another program.. When a user of the
program adds an item, then deletes it, the programs writes to the table
like
this:

Item Count RVU Value Fee
Consult 1 1.50 $5.00
Consult 1 1.50 ($5.00)


My problem is that I need to Sum the RVU Values... I Summed the count
by
counting the fee's and using: iif([fee]<0,-1,1) But I don't know
how to Sum the RVU's without counting them twice.. because if I tell my
query to show me everything >0, that takes out the negatives (that
shouldn't
be counted), but the original charge shouldn't be counted either...


Ideas?

Thanks so much!!!

Marie

 

[KARL DEWEY]

Try this --
SELECT Item, Sum(IIF([Fee] <0, -([RVU Value] * [Count]), ([RVU Value] *
[Count]))) as Total_RVU_Value
FROM YourTable;

--
Build a little, test a little.

No, not really... If there is a [fee]<0, I need to exclude that AND the
original fee that was charged... but I just now figured out how to do it!!
I added another column with this formula: iif([fee]<0,-[RVU],[RVU])
It worked great!!! It just makes sense.. Man, I love this junk!!

Thanks for your help though!!

Marie

Are you saying that you want to include only rows that have [Fee] >0? If
so, just include [Fee] as a field in your query, but uncheck the "Show"
checkbox, and remember to put ">0" (without the quotes) in the Selection
under [Fee].

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am querying from tables from another program.. When a user of the
program adds an item, then deletes it, the programs writes to the table
like
this:

Item Count RVU Value Fee
Consult 1 1.50 $5.00
Consult 1 1.50 ($5.00)


My problem is that I need to Sum the RVU Values... I Summed the count
by
counting the fee's and using: iif([fee]<0,-1,1) But I don't know
how to Sum the RVU's without counting them twice.. because if I tell my
query to show me everything >0, that takes out the negatives (that
shouldn't
be counted), but the original charge shouldn't be counted either...


Ideas?

Thanks so much!!!

Marie