Department wise Data Reports in VB6

Question

3.00/5 (1 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.

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

sarfarazbhat

Updated 18-Dec-12 19:39pm

v2

Add a Solution

Comments

Ashok19r91d 19-Dec-12 1:21am

Nothing Understand, Improve Your Question for Getting Better Result.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Ashok19r91d · Answer 1 · 2012-12-18T19:23:00

Solution 1

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

SQL

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.

VB

' 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

Posted 18-Dec-12 19:23pm

Ashok19r91d

Updated 18-Dec-12 19:49pm

v4

Comments

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;

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