Dont count negatives...???/

  • Thread starter Thread starter MarieG
  • Start date Start date
M

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
 
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
 
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



Jeff Boyce said:
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 said:
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
Click to expand...
Click to expand...
 
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.


MarieG said:
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



Jeff Boyce said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top