Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Counting only fields that match a certain parameter

Discussion in 'Information Technology' started by Groundhog, Jul 28, 2006.

  1. Groundhog

    Groundhog
    Expand Collapse
    Guest

    This should be an easy one but I've been working it for a week and...well...I
    guess I need a more current Access For Dummies Book. What I need to know is
    this:

    IS THERE A WAY OF PUTTING A COUNT FEATURE IN A REPORT THAT JUST COUNTS
    RECORDS THAT MATCH A CERTAIN CRITERIA.

    I have a database that tracks our help desk tickets and I'm trying to build
    a daily summary report. I have the records grouped on the correct field, (in
    this case it's the STATUS field) the problem is that it gives me the summary
    of EVERY entry in that field. I just want to get a count of records where
    STATUS = RESOLVED. Is there a way of limiting the count to a certain
    criteria like this?

    ***BONUS QUESTION***
    If this is possible that let me take it one step further. Is there a way of
    putting a count feature in a report that counts records where two fields have
    to match a certain criteria.

    For you oldsters out there who remember DBase III/IV, it would be something
    similar to the command COUNT FOR CATEGORY = ACTIVE .AND. STATUS = RESOLVED.
     
  2. Loading...

    Similar Threads Forum Date
    Gurmat Vichaar | Futility Of Counting Paaths | April 12, 2016 Gurmat Vichaar Apr 23, 2016
    Malaysia Malaysian 2013 Election - Vote Counting Underway Breaking News May 5, 2013
    No Counting Allowed in Gurmatt Jap Ji Sahib Dec 15, 2012
    Christianity Ye Olde King James Version, 400 Years and Counting Interfaith Dialogues Mar 27, 2011
    Will counting caste help to reduce inequality? Hard Talk May 11, 2010

  3. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    Guest

    Ok, if I understand the question, here's what I would do.

    Add a column to the Query that looks something like this ...

    MySpecialCounter: IIF([Status]='Resolved',1,0)

    Now, in the report, you have a field with 1s and 0s that may be summed in
    any footer and for the report as a whole. Create as many of these as you
    like, one for each status sum you need to count.
    --

    Danny J. Lesandrini
    dlesandrini@hotmail.com
    http://amazecreations.com/datafast


    "Groundhog" <Groundhog@discussions.microsoft.com> wrote ...
    > This should be an easy one but I've been working it for a week and...well...I
    > guess I need a more current Access For Dummies Book. What I need to know is
    > this:
    >
    > IS THERE A WAY OF PUTTING A COUNT FEATURE IN A REPORT THAT JUST COUNTS
    > RECORDS THAT MATCH A CERTAIN CRITERIA.
    >
    > I have a database that tracks our help desk tickets and I'm trying to build
    > a daily summary report. I have the records grouped on the correct field, (in
    > this case it's the STATUS field) the problem is that it gives me the summary
    > of EVERY entry in that field. I just want to get a count of records where
    > STATUS = RESOLVED. Is there a way of limiting the count to a certain
    > criteria like this?
    >
    > ***BONUS QUESTION***
    > If this is possible that let me take it one step further. Is there a way of
    > putting a count feature in a report that counts records where two fields have
    > to match a certain criteria.
    >
    > For you oldsters out there who remember DBase III/IV, it would be something
    > similar to the command COUNT FOR CATEGORY = ACTIVE .AND. STATUS = RESOLVED.
    >
    >
     
  4. Groundhog

    Groundhog
    Expand Collapse
    Guest

    I'll go ahead and try that one. The only thing that concerns me is my query
    is already pretty full and it took a bit of work to set it up to return the
    data I needed. If I add one more parameter to my query it may mess up my
    data. That's why I was looking to work this problem at the Report end on the
    process.

    "Danny J. Lesandrini" wrote:

    > Ok, if I understand the question, here's what I would do.
    >
    > Add a column to the Query that looks something like this ...
    >
    > MySpecialCounter: IIF([Status]='Resolved',1,0)
    >
    > Now, in the report, you have a field with 1s and 0s that may be summed in
    > any footer and for the report as a whole. Create as many of these as you
    > like, one for each status sum you need to count.
    > --
    >
    > Danny J. Lesandrini
    > dlesandrini@hotmail.com
    > http://amazecreations.com/datafast
    >
    >
    > "Groundhog" <Groundhog@discussions.microsoft.com> wrote ...
    > > This should be an easy one but I've been working it for a week and...well...I
    > > guess I need a more current Access For Dummies Book. What I need to know is
    > > this:
    > >
    > > IS THERE A WAY OF PUTTING A COUNT FEATURE IN A REPORT THAT JUST COUNTS
    > > RECORDS THAT MATCH A CERTAIN CRITERIA.
    > >
    > > I have a database that tracks our help desk tickets and I'm trying to build
    > > a daily summary report. I have the records grouped on the correct field, (in
    > > this case it's the STATUS field) the problem is that it gives me the summary
    > > of EVERY entry in that field. I just want to get a count of records where
    > > STATUS = RESOLVED. Is there a way of limiting the count to a certain
    > > criteria like this?
    > >
    > > ***BONUS QUESTION***
    > > If this is possible that let me take it one step further. Is there a way of
    > > putting a count feature in a report that counts records where two fields have
    > > to match a certain criteria.
    > >
    > > For you oldsters out there who remember DBase III/IV, it would be something
    > > similar to the command COUNT FOR CATEGORY = ACTIVE .AND. STATUS = RESOLVED.
    > >
    > >

    >
    >
    >
     
  5. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Groundhog,

    You would not be adding a parameter to your query, you would
    be adding a calculated field.

    You can also do this in a report or group footer/header:

    make textbox controls

    Name --> CountResolved
    ControlSource --> =Sum( IIF([Status]='Resolved',1,0) )

    Name --> CountActiveResolved
    ControlSource --> =Sum( IIF([Status]='Resolved' AND
    [Category = 'Active', 1,0) )

    This assumes that Status and Category are both controls on
    the report

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    remote programming and training
    strive4peace2006 at yahoo.com
    *
    Have an awesome day ;)


    Groundhog wrote:
    > I'll go ahead and try that one. The only thing that concerns me is my query
    > is already pretty full and it took a bit of work to set it up to return the
    > data I needed. If I add one more parameter to my query it may mess up my
    > data. That's why I was looking to work this problem at the Report end on the
    > process.
    >
    > "Danny J. Lesandrini" wrote:
    >
    >
    >>Ok, if I understand the question, here's what I would do.
    >>
    >>Add a column to the Query that looks something like this ...
    >>
    >>MySpecialCounter: IIF([Status]='Resolved',1,0)
    >>
    >>Now, in the report, you have a field with 1s and 0s that may be summed in
    >>any footer and for the report as a whole. Create as many of these as you
    >>like, one for each status sum you need to count.
    >>--
    >>
    >>Danny J. Lesandrini
    >>dlesandrini@hotmail.com
    >>http://amazecreations.com/datafast
    >>
    >>
    >>"Groundhog" <Groundhog@discussions.microsoft.com> wrote ...
    >>
    >>>This should be an easy one but I've been working it for a week and...well...I
    >>>guess I need a more current Access For Dummies Book. What I need to know is
    >>>this:
    >>>
    >>>IS THERE A WAY OF PUTTING A COUNT FEATURE IN A REPORT THAT JUST COUNTS
    >>>RECORDS THAT MATCH A CERTAIN CRITERIA.
    >>>
    >>>I have a database that tracks our help desk tickets and I'm trying to build
    >>>a daily summary report. I have the records grouped on the correct field, (in
    >>>this case it's the STATUS field) the problem is that it gives me the summary
    >>>of EVERY entry in that field. I just want to get a count of records where
    >>>STATUS = RESOLVED. Is there a way of limiting the count to a certain
    >>>criteria like this?
    >>>
    >>>***BONUS QUESTION***
    >>>If this is possible that let me take it one step further. Is there a way of
    >>>putting a count feature in a report that counts records where two fields have
    >>>to match a certain criteria.
    >>>
    >>>For you oldsters out there who remember DBase III/IV, it would be something
    >>>similar to the command COUNT FOR CATEGORY = ACTIVE .AND. STATUS = RESOLVED.
    >>>
    >>>

    >>
    >>
    >>
     

Share This Page