Many to many crystal report problem

I have the following tables:

screening table
---------------------
screeningID
patientID
screeningDate
etc.

patient table
----------------
patientID
firstName
lastName
etc

address table
------------------
addressID
patientID
address
etc.

I would like to create a report which shows each screening along with the patients info and the patients address.  A patient can have multiple screenings and a patient can have multiple addresses.  I would just like to show the latest address for the patient.  
I'm not sure whether to use subreports or handle some of this in the table links.  

I tried with a subreport to get the patients info and address but it shows all the addresses and I cant seem to do a TOP 1 to get the latest single address.
Any help getting me going in the right direction would be greatly apprectiated.
chamlightAsked:
Who is Participating?
 
mlmccCommented:
Group on the patient id field
Sort the data by the date field descending
Put the fields in the group header
Suppress the details

mlmcc
0
 
peter57rCommented:
How do you identify the latest address for a patient?
0
 
UnifiedISCommented:
You can order your detail by whatever it is that determines the address is the most recent and then put the address value in the patient group header or footer  Suppress the detail so you only see the one in the group.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
chamlightAuthor Commented:
Thanks to you both for responding-


peter57r-
latest address is determined by a dateEntered field.  So the most recent dateEntered is the address record I would like to show

UnifiedIS-
First I need to display each screening one per page.  Then I would like to display on the same page the patient info associated with the screening using the common patientID field.  I'm not sure where you suggest I place/structure your advice?
0
 
mlmccCommented:
Try it this way

Group by Screening ID
   Format screening group header for NEW PAGE BEFORE
Sort by address entered date descending
Suppress the details section
Put the address information in the group footer

Data table
Screening Table
  INNER JOIN on screening ids
Patient Table
  INNER JOIN
Address Table on Patient Ids

mlmcc
0
 
chamlightAuthor Commented:
Thanks MLMCC.  That worked.  And if I could ask a follow up question.  
I would like to add the latest entry for the same patientID in another table called detoxHistory. Its structure is:
detoxHistoryID, patientID, dateEntered, etc.
When I add the new table to the report and include the fields I want it bumps up the number of records viewed by alot.  
0
 
mlmccCommented:
The problem is there are probably one or more records for some patients in the detoxHistory table.

Are you using the detail section of the report?

mlmcc
0
 
chamlightAuthor Commented:
No.  You told me to supress the details section
0
 
mlmccCommented:
Are you doing any summaries?

It is probably easier to use a subreport to get the data you need from the new table.

mlmcc
0
 
chamlightAuthor Commented:
So I get how to pass the patientID parameter to a subreport of the new detoxHistory table.  But if I have multiple detoxHistory records how do I limit it to a single most recently entered record.   I have a field called dateEntered but I cant seem to create the select in the select expert to show the single most recent record.
0
 
mlmccCommented:
You "pass" the patientID by linking the subreport to the main report on that field.

In the subreport group by patientID
Sort ascending or descending by the date
DIsplay the record in the group header (descending sort) or group footer (descending sort)

mlmcc
0
 
vastoCommented:
What is your database type?
0
 
chamlightAuthor Commented:
mlmcc

i did as you said and linked the subreport to the main report throught the patientid .  But i have multiple Detox records per patient and I only want to display the most recent by dateAdded.  Its displaying more than 1.  How can I tell the subreport to just display the 1 record.  I cant seem to do it in the select expert.  do i have to create a special query
thanks
0
 
chamlightAuthor Commented:
many thanks mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.