Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB
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.
 

 

Please help
 
Sarfaraz
Posted 18-Dec-12 19:00pm
Edited 18-Dec-12 19:39pm
v2
Comments
Ashok19r91d at 19-Dec-12 1:21am
   
Nothing Understand, Improve Your Question for Getting Better Result.

1 solution

Rate this: bad
good
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
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
Do While Dr.Read
    Select case Ucase(Dr.GetValue(1))
    Case UCase("Male")
        DG.Rows.Add()
        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
 
  Permalink  
v4
Comments
sarfarazbhat at 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;
 
Please
Help.
Thanks
Sarfaraz
Ashok19r91d at 20-Dec-12 7:53am
   
ignore Column with name Expr1...
What's your DataSource? SQL or SQL Compact or Access or AnyOther?
sarfarazbhat at 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 at 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
0 Sergey Alexandrovich Kryukov 330
1 BillWoodruff 314
2 George Jonsson 274
3 CPallini 265
4 OriginalGriff 257
0 OriginalGriff 5,030
1 CPallini 4,115
2 Sergey Alexandrovich Kryukov 3,554
3 George Jonsson 2,826
4 Gihan Liyanage 2,386


Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 19 Dec 2012
Copyright © CodeProject, 1999-2014
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