Count with clauses

[sir_dan_mitchell]

hi guys,

im looking to count certain values in my report. I have a field that contains 4 values, Emergency, Elective, Maternity and other. I want to use a count facility to count how many emegency, elective etc.

I have created a text box yadayada and the count works, but not with a WHERE or it seems any other type of clause.

=count([FIELD]) WHERE ([FIELD]) = "Emergency" dont work and ive tried many other ways too!

Help meee

 

[Hayley Baxter]

Use a count query and base your report on the query. In the query grid do the following:

your field name
group by

your field name
count

Criteria (Count *)

HTH
Hay

 

[sir_dan_mitchell]

im being really stupid here, but how can you have group by and count in the same grid?

my field name is FIELD1

 

[Hayley Baxter]

You will have two columns the first one holds your field name then the group by the 2nd column holds the field name then count, criteria count *

Does that help?

Check out this thread for an example

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=30930&highlight=count
Hay

 

[sir_dan_mitchell]

it kind of helps yeah,

however I have this field that contains 4 different things. I want a total of EACH! if i put a where condition in, then only one of those fields can be worked with!

also in the report would i then do another count of all those that match my criteria, as currently it only gives the value of '1'.

Dan

 

[Hayley Baxter]

I use Dcount for the same thing in a db I am currently working on it looks something like

=DCount("[CategoryID]","tblProspects","[CategoryID] = 1")

I can send you the report which uses a combination of the count query via subreports and Dcount?

 

[sir_dan_mitchell]

yes please !

 

[Hayley Baxter]

Hi Dan

Can you give me a second as I'll need to take out all the confidential stuff (not that I don't trust you or anything) but the company might have something to say about that.

What version of access are you using?

Hay

 

[sir_dan_mitchell]

2000,

understand bout the data!!

 

[Hayley Baxter]

Hi Dan

I hope this is what you are looking for

Hay

 

[Rich]

=Sum(Iif([MyField]="Emergency",1,0))