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

"icsupt via AccessMonster.com" wrote:

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

>

> --

> Message posted via http://www.accessmonster.com

>