Count Problem

[itsoookkk]

Hi, I am new to MS Access , just facing a problem,
I have a report in which I am counting all the records at the end . Total Records =Count([id]) where id is autonumber.

Now I have to count a specific field which is a text field , it has only three possible values, lets say its a text field "country" in a table "employee". , there are only three possible values in this field i.e "America" , "UK" , "Japan"

now in the end of report where is i counted total records I have to mention that:

Total Employees from America:
Total Employees from UK:
Total Employees from Japan:

Can any one guide me what should I write in the control source?

Any help will be highly appreciated,

Thanks in advance

 

[PHV]

A starting point:
=Sum(IIf([country]='America',1,0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[itsoookkk]

Thanks a lot dear It worked !

 

[itsoookkk]

One more question , any body will can just plz explain this code to me ? It would more helpful for me to use it in future, thanks

 

[rjoubert]

You can read up on the IIF function here...

http://www.techonthenet.com/access/functions/advanced/iif.php

The IIF statement that PHV posted returns a value of 1 if the [country] field is "America"...if it's not it returns a value of 0. Then, the Sum is wrapped around that, so it sums up all of the values, giving you a count of employees where [country] is "America". You can then use the same function for the other countries by simply replacing "America" with "UK" or "Japan".

 

[traingamer]

If you are 'Grouping by' country, you can simply put the totals by country in the group footers instead.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill