Counting only fields that match a certain parameter

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

 

[Danny J. Lesandrini]

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.

 

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

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
(e-mail address removed)
http://amazecreations.com/datafast

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.

 

[strive4peace]

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

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.

:

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
(e-mail address removed)
http://amazecreations.com/datafast

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.