Paradox table & Field grouping

[5lights]

Here's the jist:
One-to-many relationship PATIENT to ASSESSMENT tables.
ASSESSMENT Table has eighteen fields(ie: WALKING, STANDING, EATING, SLEEPING...)
Each Patient has two or three assessments(Admit, Discharge, Followup).

I need my report to look something like:

Walking Admit Disch Foll'p
Patient info... Score Score Score
Patient info... Score Score Score
....
Walking Summary Totals & Goal accomplishment%

Standing Admit Disch Foll'p
Patient info... Score Score Score
Patient info... Score Score Score
....
Standing Summary Totals & Goal accomplishment%

etc etc

Since the Fields are not catagorized elsewhere I cant find a way to group by them.
In order to get the Admit/Disch/foll'p data on the same line I had to use two subreports linked into main via w/NumberVar's.
So I end up with running eighteen seperate reports to simulate the grouping.
If this were in SQL...no problem, but its in Native File Paradox tables....which I still cant get an ODBC connection too.
Maybe I'm missing something obvious but this is a brainteaser. Aarrgghh

 

[lbass]

Could you rethink the entire report layout? I'm guessing that "Admit","Discharge", and "Followup" are from the same patient field, e.g., {Patient.Status}, and therefore, for each patient there is one record related to admission, one for discharge, and one for followup, each containing results for the 18 Assessment fields. If you grouped on {Patient.Status} and then laid your report out like the following:

Walking Standing Eating Sleeping //etc.
Admit
Pt1
Pt2
Pt3
Summaries by Assessment Field----------------------
Discharge
Pt1
Pt2
Pt3
Summaries by Assessment Field----------------------
Followup
Pt1
Pt2
Pt3
Summaries by Assessment Field----------------------
Report Level Summaries by Assessment Field------------------

...I think you would get the same info without subreports. You could also suppress the patient data to see direct comparisons between admission, discharge, and followup.

I am second-guessing your needs here, so I apologize if this is way off-base, but I thought it was worth suggesting on the off-chance that it might be an option for you.

-LB

 

[5lights]

(Response wouldn't upload yesterday...trying again)
Regretfully, this report replaces one from a previous data system & users/Mgmt wants it to look the same.
I did howerver solve the issues.
1. Wrote 18 subreports(1 for ea grouping)
2. Re-wrote detail section in SubReports to show data without needing subreport...by Supressing duplicate patients with a (PatID=Next(PatID) condition....then using a formula to pull the Assessment score needed up from the supressed record by:
If {PATIENT.patientid}= Previous({PATIENT.patientid})and Previous({ASSESSMENT.reasonforassessment})="1"
then ToNumber(Previous({ASSESSMENT.eating}));

I'm glad this is a small database...even with 18 subreports, it only takes @1min to run the report pulling a years data(@800 records ea subrpt)

Thanks for being there.