Crystal Report - formula for next encounter date

I created a crystal report that shows a list of people that had a urine culture.  Here is sample data that is generating on the report:
Patient Name   Encounter Date     Culture results    Next Encounter Date
Patient 1           02/12/2013           No Growth          03/08/2012
Patient 2           03/08/2012           E. Coli                  06/28/2013
Patient 3           06/28/2013           No Growth          03/11/2013


What I need to see for 'Next Encounter Date' is the next encounter date in database for Patient 1....not the next encounter date for the records in report.   If Patient 1 came back for an encounter on 2/25/13 for a follow-up visit I need to see the 2/25/13 in Next Encounter Date column.  If Patient 1 hasn't been back for another visit I want to leave this blank for Patient 1.

Next (patient_encounter_enc_timestamp) is giving me the dates shown in column above.  

I tried Maximum (patient_encounter_enc_timestamp) but it shows me 6/28/2013 since that is the max date in the records shown.

Any help would be appreciated.
Thank you!
JenWhiteAsked:
Who is Participating?
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.

mlmccCommented:
How do you have the records grouped and sorted?

What filtering are you doing on the data?

mlmcc
0
JenWhiteAuthor Commented:
I am grouping on provider then sorting on patient name.  I have filters set up for specific lab ID, lab test description, and specific date range.
0
mlmccCommented:
You need to also sort on the date so the next record has the next date.

Also you only want the date if it is for the same person so you need to test if it is the same person

mlmcc
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

JenWhiteAuthor Commented:
Can you tell me how to 'test for the same person'?  Is this part of the formula for Next Date?
0
mlmccCommented:
Something like

If Next({PersonField}) = {PersonField} then
     Next(DateFIeld})

You probably want nothing to show if the next record is not for the same person

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
JenWhiteAuthor Commented:
Thank you!
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.