Click here to Skip to main content
13,052,870 members (77,446 online)
Rate this:
Please Sign up or sign in to vote.
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.

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.

Please help

Posted 18-Dec-12 19:00pm
Updated 18-Dec-12 19:39pm
Ashok19r91d 19-Dec-12 1:21am
Nothing Understand, Improve Your Question for Getting Better Result.

1 solution

Rate this: bad
Please Sign up or sign in to vote.

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

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
Do While Dr.Read
    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
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;

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...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 19 Dec 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100