Finding "firstimers" 1

[whimple]

I have one table which has a patient's name, and another table which contains a record from each time a patient visited our clinic. Each visit record includes the date. The tables are joined via an ID number - the patient table is the "one", and the visit table is the "many". I need to write a report that will display only the patient names of those who have visited for the first time since a specified date (the specified date to be determined by a parameter).
Any ideas on the easiest way to do this?

 

[Ngolem]

"I need to write a report that will display only the patient names of those who have visited for the first time since a specified date (the specified date to be determined by a parameter)."

I find this statement confusing....do you mean that you want to identify patients that have not visited since a specified date. (ie. they may have visited 100 times before but not since)

 

[chelseatech]

To get the correct data into your report you have to select on all the records in your joined tables.

Group your data by Patient ID and calculate the minumum visit date for each patient. This is the first time they visit.

Then use a group Selection formula of
Minimum({table.visitdate},{table.PatientID}) > {?SelDate}

This is how I'd do in a pure CR solution. If there is a lot of records, you might need a SQL solution with a view in the DB.
Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[whimple]

chelseatech - thank you for your answer - most helpful!

Ngolem - let me clarify - I am aiming for a list of new patients (patients that are visiting the clinic for the first time). The date parameter would allow me to determine the time period I am selecting these new patients from - say, from now to one week ago.