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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chamlightAuthor Commented:
many thanks mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.