Count of criteria like Male and Female in One field data

Hi,

Please solve this problem using instead of subreport in Crystal Reports

I think using only running filed.
My Porblem is .......

In One field data has Male and Female in different rows ( in different dates)
I want calculate the count of Male and female
Here we are using database is in Access.

Display Format is

Date count of Male Count of Female

Here i want display the count of male or female depend on data ( so Date is group field)

Please Help me......Hi,

You could create a formula to your report as follows. This should be placed in the details section.

shared numbervar cntfem;
shared numbervar cntmale;

If {table.field} = 'Male' Then
cntmale := cntmale + 1;

If {table.field} = 'Female' Then
cntfem := cntfem + 1;

Create a formula for both male and female in the summary section showing the results

e.g. formula male
shared numbervar cntmale;

and forula female
shared numbervar cntfem;

If you are not interested in the grand total, but a sub total use a function to clear clear the count at a section header. Use the supress property on the function to prevent it from printing on your report.

shared numbervar cntfem := 0;
shared numbervar cntmale := 0;

This works if you are displaying the detailed data in your report, i.e. you dont have a query like
select c, d, sum(a), sum(b)
from table
group by c, d

- Jukkasql

Count if Conditions Met

I am trying to do a summary SQL query. I have 3 fields. If one filed is
null and the other is not null, I want to count how many records there
are. I also want to count the opposite way then count both fields if
they are both not null. Can I do this within the same query? Help
appreciated.

Thanks,
Steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You could use a CASE expression inside SUM/COUNT etc. Here's an example:

CREATE TABLE #x(i int, j int)
INSERT #x SELECT NULL, 1
INSERT #x SELECT 1, 2
INSERT #x SELECT 2, 3
INSERT #x SELECT NULL, 4
INSERT #x SELECT NULL, 5

SELECT SUM(CASE WHEN i IS NULL THEN 0 ELSE j END) FROM #x

Next time, please post your table structures and some sample data, along
with your desired output.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/

"Steve Bishop" <steveb@.viper.com> wrote in message
news:411a5f99$0$14487$c397aba@.news.newsgroups.ws.. .
> I am trying to do a summary SQL query. I have 3 fields. If one filed is
> null and the other is not null, I want to count how many records there
> are. I also want to count the opposite way then count both fields if
> they are both not null. Can I do this within the same query? Help
> appreciated.
> Thanks,
> Steve
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You could use a CASE expression inside SUM/COUNT etc. Here's an example:

CREATE TABLE #x(i int, j int)
INSERT #x SELECT NULL, 1
INSERT #x SELECT 1, 2
INSERT #x SELECT 2, 3
INSERT #x SELECT NULL, 4
INSERT #x SELECT NULL, 5

SELECT SUM(CASE WHEN i IS NULL THEN 0 ELSE j END) FROM #x

Next time, please post your table structures and some sample data, along
with your desired output.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/

"Steve Bishop" <steveb@.viper.com> wrote in message
news:411a5f99$0$14487$c397aba@.news.newsgroups.ws.. .
> I am trying to do a summary SQL query. I have 3 fields. If one filed is
> null and the other is not null, I want to count how many records there
> are. I also want to count the opposite way then count both fields if
> they are both not null. Can I do this within the same query? Help
> appreciated.
> Thanks,
> Steve
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!