Count records with criteria specified

  • Thread starter Thread starter icsupt via AccessMonster.com
  • Start date Start date
I

icsupt via AccessMonster.com

I have the following fields:
ClientName
RevType
DollarValue

In this report there are many lines per clientname. The revtype could either
be a loss or a gain. Sometimes there is a loss and a gain to zero each other
out, so there will be a loss for -$100 and a gain for $100.

I am trying to count how many customers there are, but I want to filter out
any gains and losses that zero each other. If there are two entries for loss
for -$100 and only one entry for gain for $100, then I do want to include
just the one $100.

Is there an easy way to do this?

Thanks in advance.
 
Create a 2 column query. In the first column put the ClientName, in the
second column put the following below. (This is if the DollarValue is always
a positive amount and you use the RevType field to determine a Loss or Gain).
If the DollarAmount is a negative for a loss and a positive for a gain then
simply put the Dollarvalue field in the second column.
Click on the totals button on the toolbar and leave column 1 as group by but
change column 2 to Sum. In the criteria of column 2, put <>0

Amt: IIf([RevType]="Loss",[DollarValue]*-1,[DollarValue])

This query gives you the return rows that you are looking to count. You can
therefore get this number on a form or report by creating a text box with the
control source property set to
=DCount("ClientName","YourQueryName")
 
You said "If the DollarAmount is a negative for a loss and a positive for a
gain then simply put the Dollarvalue field in the second column."

Yes, the DollarAmount is a negative for a loss and a positive for a gain, but
I don't understand what you mean by "put the Dollarvalue field in the second
column".

Sorry, can you explain?

Thanks again.
 
In the same way as you put the ClientName field from your table in column 1,
then put your DollarValue field from your table in column 2.
Your query columns should look something like this

Field: ClientName DollarValue
Table: YourTablename YourTableName
Total: Group By Sum

Criteria: <>0
 
Back
Top