One to Many Report Not Using Subreports

[shelby55]

Hi

I have Crystal 9.0 reporting from an Access db.

I have a table of patients (determined by unique identifier of health card number) that I need to show specific visit history for. I created a table for these patients because the volume is much too large to enter all in a record select statement. The main report is based on this "patients" table which only has health card number and total visits in it (because I am reporting on patients who have a combined total visit of 6 or greater).

I have two databases of visits - one inpatient and one outpatient. The visit information is in a subreport (one for outpatient and one for inpatient) and linked to HCN on the main report.

The problem is with the inpatient subreport. The user wants to see all procedures, all diagnoses and all doctors so there is a main table (one) linking to diagnoses(many), procedures(many) and doctors(many). Normally I would have the "many" tables in subreports linking on visit unique ID number but I can't do this because the inpatient report is already in a subreport.

So now when I put the data elements of diagnosis, procedure and doctor in the report some are repeating themselves to match the maximum number of occurrences i.e. if there were 8 doctors then procedure and diagnosis repeat to 8.

The procedure, doctor and diagnoses tables all have the unique visit data element as well as occurrence number. So I tried to suppress the repeating ones by using the formula in the suppress format editor of:
dxacctno=mainacctno and
dxoccur=previous(dxoccur)

but it didn't work. Any suggestions greatly appreciated. Thanks.

 

[lbass]

Don't the procedures, diagnoses, and doctors relate to each other? If the user really wants to seem these individually, then just use one table per subreport linked to the main table on the unique ID. If you add these in a group header based on the unique ID, they won't repeat.

If instead you need to see how these three relate to each other, you could use one subreport and you could group on a formula that concatenates the three values so that you only see unique combinations, and then suppress the detail section. Again, this sub would be placed in a group section of the main report.

It might help to see a sample of the expected report result.

-LB

 

[shelby55]

Hi LB

Thanks for replying.

Yes the data is all related to each other by account number of visit but the table used in the main report doesn't have account information in it.

So subreport inpatient = main inpatient table (one line of data per visit).

diagnosis, doctor and procedure table = includes account # but each diagnosis/doctor/procedure per account number is on its own line with occur # and total occurence

i.e.
123567 J400 1 3
123567 I210 2 3
123567 K521 3 3
556677 C000 1 2
556677 C99 2 2

I want the data to look like
acct# LOS disdate diagnoses procedures doctor
123567 3.0 5/1/2009 J400 xray of head Dr. Smith
I210 Dr. Jones
K521 Dr. Bass
Dr. Brown

556677 8.0 5/15/2009 C000 removal tumor Dr. Black
C99 biopsy Dr. White
chemotherapy

Note that the above is all within a subreport which is where the problem lies - if the main inpatient table was the main report then these three subsets could be subreports but I can't do it that way.

Thanks.

 

[shelby55]

HI LB

For further clarification we are talking about 2 different "unique identifiers". The main report is based on the patient unique identifier of health card number. Each visit has its own unique identifier too which is how to link diagnoses, procedures, doctors but they aren't in the main report table thus the need to include in the subreport.

I hope this makes sense? Note that the entry above was supposed to have the diagnoses lining up below each other.

 

[lbass]

If I understand you correctly, the main report is using your custom table that included HCN and # of visits and then you are linking to two subreports, inpatient and outpatient. If you only had to concern yourself with the inpatient table, you could link from your custom table to the main inpatient table directly with an equal join, and then use subreports for each of doctor, diagnoses, and procedures. So the question is how the outpatient date is supposed to fit into the report--is this supposed to appear per patient or could it be presented separately?

-LB