Subreport/Querying Problems

[lynchg]

I am working with Crystal 9 and using Access 97 queries as the source of data for several subreports which sit on one main report.

I have posted a few other specific questions about this project and have received some good advice. I have come to a point where I am not sure of the best way to solve the overall problem.

I have a report that needs to contain 14 different sets of information. It is a patient health history summary to be used by doctors to help them easily identify any areas of potential concern. I have up to 14 different types of info to display. If there is no data for a person for any one of theses areas, then that area is not displayed. An example of the output is this:

If a person with a particular health concern needs routine testing, I print the result and date of the most recent test, and if the date of that last test falls beyond the prescribed frequency time frame then I am to print a large bold **DUE** just below the test results.

I need to be able to do the same thing for each of the 14 sets of info. These 14 sets of info are grouped into 4 categories.

My first approach was to create 14 queries feeding 14 subreports. The advantages of this were that it allows me to format the report as I like, I can easily suppress any subreport that lacks data, and I can insert the **DUE** after any line of data as needed. I started down this path until I placed 7 subreports on the main report, and the report bogged down so badly it took 20 minutes to save it and about 10 minutes to appear in preview mode.

I have played around with trying to lump the data into the 4 groupings using union queries with just 4 subreports. This works as far as the data is concerned (I can produce the necessary result set), but I can't figure out how to insert overdue warnings as well as there being other formatting issues.

I know this is a long and complicated question but if someone has worked through issues like this in the past I would appreciate some help.

 

[MJRBIM]

14 sub-reports is going to run like a pig, no matter what you do. Compresssing the datasource into a handful of tables or views that you can group and supress as required within a MAIN report is your best solution.

Does this "patient health history" data come from 14 different tables, or is it all from one-or-two transaction or history tables?

I know you can't post actual data (since it's medical), but if you could post some FAKE sample data with headers and an example of how you would like it displayed, I'm pretty sure you would get some useful advice.

 

[lynchg]

The data comes from many different tables:
Patient List
Service Providers
Events Incidence (visits, tests, operations)
Tests Incidence
Tests Lookup
Prescription Incidence
Medication Lookup
Upcoming Appt Listing
Etc...
Each query uses multiple tables to gather it's info, many of the queries use common tables but link on different fields or have different search criteria.

I am not having trouble querying out the info I need, I am producing the data output I need, it is just that if I group the data with union queries I don't know how to diplay as needed. I produce one page of info per patient with general info in the page header. For example:

It needs to look like this:

Header:
John Doe Appt: 11/18/2003
Male 55 dob: 9/9/1948 Provider: Dr. Jones

Example of data from a subreport:

Cancer

Description Freq Results/Name Last Date Code
Test 1 2/year XYZ 1/5/03 12345
**DUE**

Test 2 yearly QQQQ 4/5/2003 6789

Med Type 1 3 mos Aspirin 5/17/2003 12XYZ
**DUE**

Med Type 2 Monthly Tylenol 11/9/2003 000567


Each of these 4 lines of data comes fro a different query. I can union these 4 queries, but in this case I would return a data set with 4 records that I would display in the detail section of this particular subreport. Then I can't insert **DUE** where I need to, there is additional data that fills out all of the horizontal space I have for each line and making major changes to the entire layout is not an option. It needs to match an existing layout of reports already in use. We don't want to make major changes to create a new learning curve for the users, the cost of making mistakes from misreading a report like this is too high. The existing report I am using as a template uses an older technology that creates output line by line.

 

[lbass]

Is the insertion of "**DUE**" the biggest issue at this point? How are you doing it currently?

You could create a formula like:

if {table.freq} = "2/year" then
(if datediff("d",{table.lastdate},currentdate) >= 182.625 then {table.lastdate} + chr(13) + "**DUE**&quot else
if {table.freq} = "yearly" then
(if datediff("d",{table.lastdate},currentdate) >= 365.25
then {table.lastdate} + chr(13) + "**DUE**&quot else
if {table.freq} = "3 mos" then
(if datediff("d",{table.lastdate},currentdate) >= 91.31 then {table.lastdate} + chr(13) + "**DUE**&quot else
if {table.freq} = "monthly" then
(if datediff("d",{table.lastdate},currentdate) >= 30.44 then {table.lastdate} + chr(13) + "**DUE**&quot else
{table.lastdate}

Then use this formula instead of {table.lastdate}. Then right click on the formula->format->common and check "Can Grow."

Not even sure if this is the issue here, so maybe this won't help...

-LB

 

[lynchg]

Thanks,

I figured out my main problem of how to display my data. I have 4 groupings of output so I am using 4 subreports fed by 4 union queries. Then on each subreport I am grouping on the description field and using formulas to determine whether to print the **DUE** alerts.

 

[CmdrGravy]

Would it be possible to to create something in Access which collates the data you need into one table ?

You could probably write a small bit of VB code or something to perform the collation and either schedule that to run before your report or just run it every day or so to collate that days data. This may seem like a lot of work but it may make things easier and quicker for you in Crystal Reports.

 

[lynchg]

I have actually done that. I created union queries which assemble all of the data in roughly the same order and format as required for the report. It is working well now.