IIf Statement used for Counting

1 Jul 2009 7:45 PM

Vonda

I have written the following statement to count the number of records in a
true statement:

=IIf([Levy]="yes",1,0)

But, what I need to do is expand this statement so it will not count those
records which are true because the record is a duplicate client's name. Does
this make sense?

Can anyone help me?

1 Jul 2009 8:14 PM

KARL DEWEY

You have to run a query to separate the dupes first or use a subquery.

Show quoteHide quote

"Vonda" wrote:

> I have written the following statement to count the number of records in a
> true statement:
>
> =IIf([Levy]="yes",1,0)
>
> But, what I need to do is expand this statement so it will not count those
> records which are true because the record is a duplicate client's name. Does
> this make sense?
>
> Can anyone help me?

1 Jul 2009 9:57 PM

Vonda

I am not familiary with the subquery process. What would such a subquery
look like? And, where would the subquery go? Within the report?

Show quoteHide quote

"KARL DEWEY" wrote:

> You have to run a query to separate the dupes first or use a subquery.
>
> "Vonda" wrote:
>
> > I have written the following statement to count the number of records in a
> > true statement:
> >
> > =IIf([Levy]="yes",1,0)
> >
> > But, what I need to do is expand this statement so it will not count those
> > records which are true because the record is a duplicate client's name. Does
> > this make sense?
> >
> > Can anyone help me?

1 Jul 2009 11:38 PM

KARL DEWEY

Ok, run a query to separate the dupes first.

Show quoteHide quote

"Vonda" wrote:

> I am not familiary with the subquery process. What would such a subquery
> look like? And, where would the subquery go? Within the report?
>
> "KARL DEWEY" wrote:
>
> > You have to run a query to separate the dupes first or use a subquery.
> >
> > "Vonda" wrote:
> >
> > > I have written the following statement to count the number of records in a
> > > true statement:
> > >
> > > =IIf([Levy]="yes",1,0)
> > >
> > > But, what I need to do is expand this statement so it will not count those
> > > records which are true because the record is a duplicate client's name. Does
> > > this make sense?
> > >
> > > Can anyone help me?

2 Jul 2009 2:56 PM

Klatuu

Here is an example of a query that will filter by unique name and count only
those with a True value. If you are using the text value yes, you will need
to change it a bit, but this is the method:

SELECT [_tblClientSave].MainName, Count([_tblClientSave].IsCorporate) AS
CountOfIsCorporate
FROM _tblClientSave
GROUP BY [_tblClientSave].MainName, [iscorporate]=True
HAVING ((([iscorporate]=True)=True));

--
Dave Hargis, Microsoft Access MVP

Show quoteHide quote

"Vonda" wrote:

> I have written the following statement to count the number of records in a
> true statement:
>
> =IIf([Levy]="yes",1,0)
>
> But, what I need to do is expand this statement so it will not count those
> records which are true because the record is a duplicate client's name. Does
> this make sense?
>
> Can anyone help me?