How to wk with all rcrds of a grp if one record matches a criterea ?

[posero]

I am using Crystal 6/7/or 8 and a relational btrieve database.&nbsp;&nbsp;<br><br>I want to be able to work with all records of a group if one record of that group meets a certain criterea.<br><br>e.g.: (all within one table:&nbsp;&nbsp;{Schedule.dat}) I would like to display all appointments of a patient if one of his appointments has a status of X on a certain day.&nbsp;&nbsp;(all within one table)<br><br>Many thanks !<br><br>

 

[MalcolmW]

Depends somewhat on your data structure.&nbsp;&nbsp;The trick is using a self join on your appointments table.<br>Assuming you have a table Patients, and another called Appts, and you are starting with a new report.<br>Add the table Appts.&nbsp;&nbsp;Give it an alias like ApptsStatusX.<br>Apply a record selection so that it has only appoints with Status = X.&nbsp;&nbsp;<br>Add the Patients table, linking on PatientID.&nbsp;&nbsp;<br>Add the table Appts again, giving it an alias AllAppts, linking to Patients on PatientID.<br>The fields in Patient and AllAppts should show all patients and all appoints for patients that have at least one status X appointment.&nbsp;&nbsp;If you are using version 8, make it the selection distinct to eliminate duplicates (patients with two status X appointments will show twice) <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[posero]

Thanks very very much for the fast response.&nbsp;&nbsp;I tried the solution in both Crystal 7 and 8.&nbsp;&nbsp;Whether I link through the Patient table or from Schedule directly to Schedule makes no difference.&nbsp;&nbsp;The report however, for a very tiny amount of data takes over 10 minutes to process. Using sub-reports takes me just 30 seconds for the same results.&nbsp;&nbsp;However I am still looking for a non-subreport solution because this creates a very nasty Crystal error using my Btrieve database.&nbsp;&nbsp;So unfortunately I'm back to square one and still desperately searching for a solution to the above problem !

 

[MalcolmW]

Is PatientID indexed on both tables?&nbsp;&nbsp;Is Status indexed?<br>Sounds like the joining process is seriously inefficient - you should get an extremely rapid response on a query of this sort. <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>