Count records with criteria specified

Discussion in 'Microsoft Access Reports' started by icsupt via AccessMonster.com, Jun 30, 2005.

  1. 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
     
    icsupt via AccessMonster.com, Jun 30, 2005
    #1
    1. Advertisements

  2. icsupt via AccessMonster.com

    Guest Guest

    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
    >
     
    Guest, Jun 30, 2005
    #2
    1. Advertisements

  3. 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
     
    Jane higgs via AccessMonster.com, Jun 30, 2005
    #3
  4. icsupt via AccessMonster.com

    Guest Guest

    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
    >
     
    Guest, Jun 30, 2005
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    count records based on criteria

    Guest, Apr 12, 2004, in forum: Microsoft Access Reports
    Replies:
    1
    Views:
    177
    Marshall Barton
    Apr 12, 2004
  2. PizzaBoy

    Count records in report which fit a criteria

    PizzaBoy, Oct 19, 2004, in forum: Microsoft Access Reports
    Replies:
    2
    Views:
    374
    PizzaBoy
    Oct 21, 2004
  3. news.microsoft.com

    running count of certain records based of criteria/conditions

    news.microsoft.com, Mar 7, 2005, in forum: Microsoft Access Reports
    Replies:
    1
    Views:
    580
    Marshall Barton
    Mar 7, 2005
  4. Guest
    Replies:
    1
    Views:
    607
    fredg
    Apr 8, 2005
  5. Guest
    Replies:
    1
    Views:
    305
    Marshall Barton
    May 1, 2006
Loading...

Share This Page