PC Review


Reply
Thread Tools Rate Thread

Count records with criteria specified

 
 
icsupt via AccessMonster.com
Guest
Posts: n/a
 
      30th Jun 2005
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
 
Reply With Quote
 
 
 
 
=?Utf-8?B?RGVubmlz?=
Guest
Posts: n/a
 
      30th Jun 2005
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
>

 
Reply With Quote
 
 
 
 
Jane higgs via AccessMonster.com
Guest
Posts: n/a
 
      30th Jun 2005
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.

--
Message posted via http://www.accessmonster.com
 
Reply With Quote
 
=?Utf-8?B?RGVubmlz?=
Guest
Posts: n/a
 
      30th Jun 2005
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

"Jane higgs via AccessMonster.com" wrote:

> 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.
>
> --
> Message posted via http://www.accessmonster.com
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
"CD Audio Error 26: The specified parameter is out of range for the specified command." scharf.steven@gmail.com Windows XP General 0 18th Jul 2005 10:42 PM
Re: how to access Specified cell in a Specified date? John Vinson Microsoft Access 0 20th Sep 2004 05:38 PM
how to access Specified cell in a Specified date? =?Utf-8?B?YW1yenp6eno=?= Microsoft Access 0 20th Sep 2004 04:17 PM
data type mismatch in criteria expression when no criteria specified JR Microsoft Access Queries 1 27th Jul 2004 03:47 AM
Re: Delete Query cannot delete specified from specified tables... Nikos Yannacopoulos Microsoft Access 0 8th Jul 2004 01:08 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:00 PM.