12,500,761 members (45,381 online)
Rate this:
See more:
Dear friends
I have a project which is running absolutely fine but now i have a small issue in it. Actually i want to generate a Data report of all the departments but Gender wise. Eg
i have a table patients which has the following fields
Reg_No,Patientname, Residence,Age,Sex,Department, Date,Time

Now I want to generate reports of all the departments on left side and display all the patients of individual departments but showing Male and female separately.

DEPARTMENT MALE FEMALE
ENT 20 15
Dental 45 34

20 and 15 are male and female patients of ENT Department.
Similarly 45 and 34 are male and female patients of Dental Department.

Sarfaraz
Posted 18-Dec-12 19:00pm
Updated 18-Dec-12 19:39pm
v2
Ashok19r91d 19-Dec-12 1:21am

Nothing Understand, Improve Your Question for Getting Better Result.

Rate this:

## Solution 1

If 20, 15, 45, 34 is the Count of Patients in Particular Department than, here's the Query to get your result...

`SELECT Department, Sex, COUNT(*) FROM [My Patient] GROUP BY Department ORDER BY SEX DESC`

You Get Results like,
```Department  Sex   Expr1
ENT        Male     20
ENT        FEMALE   15
DENTAL     Male     45
DENTAL     FEMALE   34 ```
Process this Results in Your Program...
Here's VB Code for Setting Results in Grid.

```' Let DG as DataGridView where you displaying Results
' Dr is the DataReader Which Holds above Result
Select case Ucase(Dr.GetValue(1))
Case UCase("Male")
DG.Rows(DG.RowCount - 1).Cells(0).Value = Dr.GetValue(0)
DG.Rows(DG.RowCount - 1).Cells(1).Value = Dr.GetValue(2)
Case UCase("Female")
DG.Rows(DG.RowCount - 1).Cells(2).Value = Dr.GetValue(2)
End Select
Loop
```
v4
sarfarazbhat 20-Dec-12 7:46am

Hello sir
I have an issue now . How to write Expr1 field in my data report vb6 as it says that expr1 field does not exist. This query is not working unless you write
SELECT Department, Sex, COUNT(*) FROM patients GROUP BY Department,sex ORDER BY SEX DESC;

Help.
Thanks
Sarfaraz
Ashok19r91d 20-Dec-12 7:53am

ignore Column with name Expr1...
What's your DataSource? SQL or SQL Compact or Access or AnyOther?
sarfarazbhat 20-Dec-12 10:26am

Access 2007. Actually i want exactly on my data report as you mentioned at You Get Results like,(above). But instead of Expr1 i want Totalpatients. I am getting that with
SELECT Department, Sex, COUNT(*) FROM patients GROUP BY Department,sex ORDER BY SEX DESC;

But after placing fields on data report it shows error for Expr1 field which is not in my table.
Ashok19r91d 20-Dec-12 23:56pm

Just Execute SQL Query From Access 2007, Note the Column Name which displays Patients count... Replace Expr1 with that Column Name... Comment Your Response...

Top Experts
Last 24hrsThis month
 David_Wimbley 245 OriginalGriff 238 Richard MacCutchan 85 Karthik Bangalore 80 Garth J Lancaster 70
 OriginalGriff 6,548 ppolymorphe 3,669 Maciej Los 3,283 David_Wimbley 2,647 Karthik Bangalore 2,569