COUNTIF equivalent

  • Thread starter Thread starter Sue Compelling
  • Start date Start date
S

Sue Compelling

Hi

(Using ACCESS2000)

I have a field [Doc1] which is populated with either 1, 2 or 3 (this is
derived from an option group). (NOTE - my select query incl fields Doc1 to
Doc26)

I want to create an expression that counts only those records with 1.

Have tried a number of variations though keep getting an error or a count of
all records)

TIA ...
 
Hi

(Using ACCESS2000)

I have a field [Doc1] which is populated with either 1, 2 or 3 (this is
derived from an option group). (NOTE - my select query incl fields Doc1 to
Doc26)

I want to create an expression that counts only those records with 1.

Have tried a number of variations though keep getting an error or a count of
all records)

TIA ...
Click to expand...

CountOnes:Sum(IIf([Doc1] = 1,1,0))
 
Hi

(Using ACCESS2000)

I have a field [Doc1] which is populated with either 1, 2 or 3 (this is
derived from an option group). (NOTE - my select query incl fields Doc1 to
Doc26)
Click to expand...

Then your table structure IS WRONG, if those are table fields. What happens
when you need to add docs 27 and 28???
I want to create an expression that counts only those records with 1.
Click to expand...

In what context? In a query, on a form, in a report...?
Have tried a number of variations though keep getting an error or a count of
all records)
Click to expand...

One way (among many depending on the context):

DCount("*", "[tablename]", "[Doc1] = 1")

This can be used as a calculated field in a query, or (preceded with an =
character) as the Control Source of a textbox on a form or report.
 
Hi Fred

Brilliant -

I couldn't get the expression to work in the query (came back with an
aggregate error) though I did get

Sum(IIf([Doc1] = 1,1,0))

to work in my report - thanks so much ...

Sue
--
Sue Compelling


fredg said:
Hi

(Using ACCESS2000)

I have a field [Doc1] which is populated with either 1, 2 or 3 (this is
derived from an option group). (NOTE - my select query incl fields Doc1 to
Doc26)

I want to create an expression that counts only those records with 1.

Have tried a number of variations though keep getting an error or a count of
all records)

TIA ...
Click to expand...

CountOnes:Sum(IIf([Doc1] = 1,1,0))
Click to expand...
 
Hi John

Appreciate your reply and alternative expression to Fred's.

FWIW - I'll never want to add Doc27+.

This is a DB to QA a program of work constructing cellsites. To 'sign off'
each cell site there are 26 documents that need to be received (only 26 -
this was determined after much negotiation and consultation).

Cheers
 
Hi John

Appreciate your reply and alternative expression to Fred's.

FWIW - I'll never want to add Doc27+.

This is a DB to QA a program of work constructing cellsites. To 'sign off'
each cell site there are 26 documents that need to be received (only 26 -
this was determined after much negotiation and consultation).

Cheers
Click to expand...

Nevertheless... your queries will be MUCH simpler if you model this many
(cellsites) to many (documents) relationship as a properly normalized table:

Documents
DocID <primary key>
<information about the document as an entity>

Cellsites
SiteID <primary key>
<information about the site itself>

Signoffs
SiteID <link to Cellsites, which site is being signed off>
DocID <which document was received>
DateReceived
<other info about THIS site and THIS document>

This will let you (e.g.) get counts of all the documents in a single query
(rather than 26 queries or a single query with 26 DCounts).

And I've heard entirely too many bureaucrats say "There will *never* be
another signoff needed" - and hearing the phrase usually means that the next
requirement is in the planning stages and will be sprung on you at the most
inconvenient possible time!
 
LOL - on bureacrats - it being a short term project - let's hope they don't
have time to set up another sub committee.

And yep - on the two table structure. I'm a pure amateur on ACCESS and I
did try that to start with but couldn't get it (ie the forms to display) as I
wanted - so gave up and blundered my way through this structure - which apart
from the ... "(rather than 26 queries or a single query with 26 DCounts)"
seems to work OK.

Cheers
 
Back
Top