Prev: Finding Posts
Next: date query criteria prompt
From: Mikhail Bogorad on 10 May 2010 10:11
Hi,
I have a query that brings a bunch of text fields from a table, each
fields value can be either Yes or No. It's a kind of an audit
questionnaire and No means an error. So i'm trying to count a number
of No's in my query.
Any idea how it could be done?

Thanks
From: John Spencer on 10 May 2010 10:48
You need to give a bit more information on the structure of your table.

Do you have multiple fields in one record that contain Yes or No?
If so, are you trying to count the number of No in the record or the Number of
No responses in all the records for each question?

Assuming you want a count of NO reponses in a record, you would need an
expression like the following in a field "cell" as a calculated value.

IIF(FieldA="no",1,0) + IIF(FieldB="no",1,0) + ... + IIF(FieldDD="no",1,0)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Mikhail Bogorad wrote:
> Hi,
> I have a query that brings a bunch of text fields from a table, each
> fields value can be either Yes or No. It's a kind of an audit
> questionnaire and No means an error. So i'm trying to count a number
> of No's in my query.
> Any idea how it could be done?
>
> Thanks
From: Mikhail Bogorad on 10 May 2010 11:46
On May 10, 10:48 am, John Spencer <spen...(a)chpdm.edu> wrote:
> You need to give a bit more information on the structure of your table.
>
> Do you have multiple fields in one record that contain Yes or No?
> If so, are you trying to count the number of No in the record or the Number of
> No responses in all the records for each question?
>
> Assuming you want a count of NO reponses in a record, you would need an
> expression like the following in a field "cell" as a calculated value.
>
> IIF(FieldA="no",1,0) + IIF(FieldB="no",1,0) + ... + IIF(FieldDD="no",1,0)
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
>
>
> Mikhail Bogorad wrote:
> > Hi,
> > I have a query that brings a bunch of text fields from a table, each
> > fields value can be either Yes or No. It's a kind of an audit
> > questionnaire and No means an error. So i'm trying to count a number
> > of No's in my query.
> > Any idea how it could be done?
>
> > Thanks- Hide quoted text -
>
> - Show quoted text -

Yes, each record has approx 30 fields that have Yes or No. It's going
to be a very long expression. Thanks a lot.
From: Duane Hookom on 10 May 2010 12:32
IMO, the reason it's "a very long expression" is your table structure is not
normalized.

Typically you should "count up data from different records" rather than
"count up data from different fields".

--
Duane Hookom
Microsoft Access MVP


"Mikhail Bogorad" wrote:

> On May 10, 10:48 am, John Spencer <spen...(a)chpdm.edu> wrote:
> > You need to give a bit more information on the structure of your table.
> >
> > Do you have multiple fields in one record that contain Yes or No?
> > If so, are you trying to count the number of No in the record or the Number of
> > No responses in all the records for each question?
> >
> > Assuming you want a count of NO reponses in a record, you would need an
> > expression like the following in a field "cell" as a calculated value.
> >
> > IIF(FieldA="no",1,0) + IIF(FieldB="no",1,0) + ... + IIF(FieldDD="no",1,0)
> >
> > John Spencer
> > Access MVP 2002-2005, 2007-2010
> > The Hilltop Institute
> > University of Maryland Baltimore County
> >
> >
> >
> > Mikhail Bogorad wrote:
> > > Hi,
> > > I have a query that brings a bunch of text fields from a table, each
> > > fields value can be either Yes or No. It's a kind of an audit
> > > questionnaire and No means an error. So i'm trying to count a number
> > > of No's in my query.
> > > Any idea how it could be done?
> >
> > > Thanks- Hide quoted text -
> >
> > - Show quoted text -
>
> Yes, each record has approx 30 fields that have Yes or No. It's going
> to be a very long expression. Thanks a lot.
> .
>
 | 
Pages: 1
Prev: Finding Posts
Next: date query criteria prompt