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

Could I use SQL "Select Case .. When..." in ADODB.Recordset().open

Is there any SQL Error?

Or I have to use Select case in VB code to control SQL instead.

Thank you for any ans.

NunoNuno (ranocha@.chula.com) writes:
> Is there any SQL Error?
> Or I have to use Select case in VB code to control SQL instead.
>
> Thank you for any ans.

I'm afraid that the question you have posted provides far too little
of information to be useful. Please post the code you are having problem
with.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Thank you Erland Sommarskog. I use VS.net webservice to connect SQL
server. Could I try with the following code (I'm not sure that I can use
"SELECT CASE" sql with this or not)
'Dim objADORS_ As New ADODB.Recordset()
'objADORS_.Open("SELECT CASE
DateDiff('d',[tblHeader].[start_Weekend]," & to_date & ")" & _
' " WHEN 0 THEN sum([tblTimeSheet].[sat]) +
sum[tblMiscellenous].[sat]" & _
' " WHEN 1 THEN sum([tblTimeSheet].[sun]) +
sum[tblMiscellenous].[sun]" & _
' " WHEN 2 THEN sum([tblTimeSheet].[mon]) +
sum[tblMiscellenous].[mon]" & _
' " WHEN 3 THEN sum([tblTimeSheet].[tue]) +
sum[tblMiscellenous].[tue]" & _
' " WHEN 4 THEN sum([tblTimeSheet].[wed]) +
sum[tblMiscellenous].[wed]" & _
' " WHEN 5 THEN sum([tblTimeSheet].[thu]) +
sum[tblMiscellenous].[thu]" & _
' " WHEN 6 THEN sum([tblTimeSheet].[fri]) +
sum[tblMiscellenous].[fri]" & _
' " ELSE 0 " & _
'" END as HrsUsed" & _
'" FROM(tblHeader, tblTimeSheet, tblMiscellenous)" & _
'" WHERE(tblHeader.HID = tblTimeSheet.HID and
tblHeader.HID=tblMiscellenous.HID) " & _
'" and ('" & to_date & "' between
[tblHeader].[start_weekend] and [tblHeader].[end_weekend])" & _
'" and tblHeader.initial = '" & ini & "'" & _
'" GROUP BY [tblHeader].[start_Weekend]")

thank you for your kindness ans.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||A Rugchatjaroen (ranocha@.chula.com) writes:
> Thank you Erland Sommarskog. I use VS.net webservice to connect SQL
> server. Could I try with the following code (I'm not sure that I can use
> "SELECT CASE" sql with this or not)
> 'Dim objADORS_ As New ADODB.Recordset()
> 'objADORS_.Open("SELECT CASE
> DateDiff('d',[tblHeader].[start_Weekend]," & to_date & ")" & _
> ' " WHEN 0 THEN sum([tblTimeSheet].[sat]) +
> sum[tblMiscellenous].[sat]" & _
> ' " WHEN 1 THEN sum([tblTimeSheet].[sun]) +
> sum[tblMiscellenous].[sun]" & _
> ' " WHEN 2 THEN sum([tblTimeSheet].[mon]) +
> sum[tblMiscellenous].[mon]" & _
> ' " WHEN 3 THEN sum([tblTimeSheet].[tue]) +
> sum[tblMiscellenous].[tue]" & _
> ' " WHEN 4 THEN sum([tblTimeSheet].[wed]) +
> sum[tblMiscellenous].[wed]" & _
> ' " WHEN 5 THEN sum([tblTimeSheet].[thu]) +
> sum[tblMiscellenous].[thu]" & _
> ' " WHEN 6 THEN sum([tblTimeSheet].[fri]) +
> sum[tblMiscellenous].[fri]" & _
> ' " ELSE 0 " & _
> ' " END as HrsUsed" & _
> '" FROM(tblHeader, tblTimeSheet, tblMiscellenous)" & _
> '" WHERE(tblHeader.HID = tblTimeSheet.HID and
> tblHeader.HID=tblMiscellenous.HID) " & _
> '" and ('" & to_date & "' between
> [tblHeader].[start_weekend] and [tblHeader].[end_weekend])" & _
> '" and tblHeader.initial = '" & ini & "'" & _
> '" GROUP BY [tblHeader].[start_Weekend]")

SELECT CASE is OK, but it seems you have an error with to_date. It's
not quoted in the SQL string.

I would suggest that it is better to use a parameterized query instead,
because then you don't have to bother about nested quotes, and the
embedded SQL code becomes cleaner.

I'm only an occassional ADO programmer, so this syntax may not be
entirely correct, but would do something like:

Dim cmd AS new ADODB.Command
cmd.CommandText = _
" SELECT CASE DateDiff('d', h.[start_Weekend], ?)" & _
" WHEN 0 THEN SUM(ts.[sat]) + ? " & _
" WHEN 1 THEN SUM(ts.[sun]) + ? " & _
" WHEN 2 THEN SUM(ts.[mon]) + ? " & _
" WHEN 3 THEN SUM(ts.[tue]) + ? " & _
" WHEN 4 THEN SUM(ts.[wed]) + ? " & _
" WHEN 5 THEN SUM(ts.[thu]) + ? " & _
" WHEN 6 THEN SUM(ts.[fre]) + ? " &_
" ELSE 0 " & _
" END as HrsUsed" & _
" FROM tblHeader h, tblTimeSheet ts, tblMiscellenous m " & _
" WHERE h.HID = ts.HID " & _
" AND h.HID = m.HID " & _
" AND ? between th.[start_weekend] AND th.[end_weekend] " & _
" AND h.initial = ? " & _
" GROUP BY h.[start_Weekend]"
cmd.Parameters.Append cmd.CreateParameter(, adDateTime,,, to_date)
cmd.Parameters.Append cmd.CreateParameter(, adInteger,,, & _
sum[tblMiscellenous].[sat])
...
cmd.Parameters.Append cmd.CreateParameter(, adDateTime,,, to_date)
cmd.Parameters.Append cmd.CreateParameter(, adChar,, 1, ini)
rs.Open(cmd)

In the SQL I have also introduced aliases to make it less verbose.

I should add that the query looks a little funny, but since I don't
what result you are looking for, I cannot tell whether it returns
the desired result or not.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog,

That's so cool. Thank you so much.... ^_^

Nuno