Data Report Data Display

[bubarooni]

I have a question on the data report. I have a recordset I want to display in the report in a particular manner that I just can't seem to do. I am using the native vb datareport.

The recordset is created with a sql string executed against an MS ACCESS db. It returns a patient name, the patient's doctor and the patient's primary insurance company.

I want the report to display the Doctor's Name first and then each Insurance Company that the doctor deals with below that. Under each Insurance company would be listed the Patients who have that Insurance company.

Currently my report is displaying each record with the doctor, insurance and patient name like this for each record:

Doctor
Insurance
Patient
Doctor
Insurance
Patient

I want it to look like this:

Doctor1
Insurance1
Patient1
Patient2
Patient3
Insurance2
Patient4
Doctor2
Insurance1
Patient5
Patient6
Insurance3
Patient7

I am pulling all the data successfully I just can't seem to get it to display correctly. I am appreciative to anyone who can shed any light on this or point me in the right direction.

Thanks in Advance

 

[aco2x]

I think you will need to introduce grouping into your data report and then pass a hierarchical record set as the data source to the Data Report.

Something like the following, but you will need to flesh it out for the three levels that you want to achieve.

'Make sure you specify a Shape provider in your connection
ReportSQLString = "SHAPE {SELECT DISTINCT Doctor FROM AccessTable Order By Doctor} As Parent " & _
"APPEND ({SELECT * FROM AccessTable Order By Doctor} As Data " & _
"RELATE Doctor TO Doctor)"
Set DataReportRS = DB_Connection.Execute (ReportSQLString, , adCmdText)
Set DataReport.DataSource = DataReportRS