Count and display the Number of "Yes" in various fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello. Not sure if this is a report question, or query question, but here's
my problem.

I have several yes/no fields in a table, for example, Quote Declined, Quote
Issued, and Quote Bound. I need to report on the number of Quotes Declined,
Issued and Bound over a year period, grouped by month (I have a Quote
Effective Date I'm grouping by). But honestly, I'm stumped on how to even
proceed, because in a query where I have a criteria of "Yes" for Declined,
I'm omitting other records for Issued and Bound that have "No" for Declined.
And if I don't exclude the "No" fields for bound, my count won't be accurate
in my Report, etc. Can I simply return all records and somehow use the Count
function for each field with a criteria on my report? Any ideas? I
appreciate any help I could get! Thanks.
 
Hi there!

I'm not sure if I understand you correctly but what I
think I'm hearing is that you have to count different
records based on different criteria and dispaly the
counts in the same report. If this is the case, you
should probably write a private function in your report
that does the calculating (counting) and put the count
value into a textbox somewhere appropriate in your
report. Let me know if you need more help!

PSIAccess
 
Thanks for replying. You got it right, that's exactly what I need to do.
Unfortunately, I'm not sure where to start with the private function idea.

How would I go about writing that? Where would I store it? For starters,
let's say I want to count "Yes" fields in [Quote Declined] in qryStatistics.
If you could guide me with that, I could see if can take if from there.
Thanks in advance for any help!!!
 
MVPs, please jump in-- I'm just nosing in on this question because I find it
an interesting challenge, but I'm still pretty inexperienced, especially in
complicated SQL stuff...

I would think you could get your values in a nested query. Maybe you could
start with a SQL statement like this:

SELECT * FROM
(SELECT Count(1) AS [Quotes Declined] FROM MyTable
WHERE [Quote Declined]=True) fld1,
(SELECT Count(1) AS [Quotes Issued] FROM MyTable
WHERE [Quote Issued]=True) fld2,
(SELECT Count(1) AS [Quotes Bound] FROM MyTable
WHERE [Quote Bound]=True) fld3;

The aliases "fld1, fld2," are necessary to keep Access from complaining of
repeating the table name in the FROM clause. For each sub query,that
doesn't specify, Access supplies an alias, but it is the same alias each
time. You can omit one of the aliases in your SQL statement, and the query
will still work, because the Access alias will be different the\an the ones
you provide ('fld1', for example).

The problem with this query is that it isn't grouped by months. To make a
single query statement work, you would need to have each of the sub queries
specify the date range:

SELECT * FROM
(SELECT Count(1) AS [Quote Declined] FROM MyTable
WHERE [Quote Date] BETWEEN #1/1/2004# And #3/31/2004#
AND [Quote Declined]=True) fld1,
(SELECT Count(1) AS [Quotes Issued] FROM MyTable
WHERE [Quote Date] BETWEEN #1/1/2004# And #3/31/2004#
AND [Quote Issued]=True) fld2,
(SELECT Count(1) AS [Quotes Bound] FROM MyTable
WHERE [Quote Date] BETWEEN #1/1/2004# And #3/31/2004#
AND [Quote Bound]=True) fld3;

Very cumbersome, unless you change the SQL string using VBA, and that's
cumbersome too.

So, this has been an interesting diversion, but it would probably be much
better to create separate saved queries, and then refer to them in a higher
query. That should make the grouping by month much more accessible. Let
the Access query design interface help you there.

HTH
Paul Johnson
 
ehale said:
Hello. Not sure if this is a report question, or query question, but here's
my problem.

I have several yes/no fields in a table, for example, Quote Declined, Quote
Issued, and Quote Bound. I need to report on the number of Quotes Declined,
Issued and Bound over a year period, grouped by month (I have a Quote
Effective Date I'm grouping by). But honestly, I'm stumped on how to even
proceed, because in a query where I have a criteria of "Yes" for Declined,
I'm omitting other records for Issued and Bound that have "No" for Declined.
And if I don't exclude the "No" fields for bound, my count won't be accurate
in my Report, etc. Can I simply return all records and somehow use the Count
function for each field with a criteria on my report?
Click to expand...


I think the easiest way is to use a text box with an
aggregate function. Here are three of the many ways to do
this:

=Count(IIf([Quote Declined], 1, Null))
=Sum(IIf([Quote Declined], 1, 0))
=-Sum([Quote Declined])
 
This is how I have done what you want.
In a new query add a column(s) for each field value you
want to count grouped by effective date.
Ex
DeclinedYes: Sum(IIf([declined]="Yes",1,0))
Add this query to your report query and join by Effective Date.

In your report add this new field to the Effective Date footer.

Jim
 
Thanks to everyone who responded. I ended up going with Jim's suggestion --
it worked great!!

Jim/Chris said:
This is how I have done what you want.
In a new query add a column(s) for each field value you
want to count grouped by effective date.
Ex
DeclinedYes: Sum(IIf([declined]="Yes",1,0))
Add this query to your report query and join by Effective Date.

In your report add this new field to the Effective Date footer.

Jim
-----Original Message-----
Hello. Not sure if this is a report question, or query question, but here's
my problem.

I have several yes/no fields in a table, for example, Quote Declined, Quote
Issued, and Quote Bound. I need to report on the number of Quotes Declined,
Issued and Bound over a year period, grouped by month (I have a Quote
Effective Date I'm grouping by). But honestly, I'm stumped on how to even
proceed, because in a query where I have a criteria of "Yes" for Declined,
I'm omitting other records for Issued and Bound that have "No" for Declined.
And if I don't exclude the "No" fields for bound, my count won't be accurate
in my Report, etc. Can I simply return all records and somehow use the Count
function for each field with a criteria on my report? Any ideas? I
appreciate any help I could get! Thanks.
.
Click to expand...
Click to expand...
 
Back
Top