Max Dates

[Elamranii]

Hi,
I have the following table. What I would like to show in the interval column is this:
If current then:
5/26/05 Minus 5/20/05
5/13/05 Minus 04/29/05
If Prior then
I Only Wish To See Max Of Prior DISCHDT in this case I would like to see 4/29/05.

Table MEMBID ADMITDT DISCHDT Interval

Current 04232925301 05/26/05 06/07/05
Current 04232925301 05/13/05 05/20/05
Prior 04232925301 04/18/05 04/29/05
Prior 04232925301 04/02/05 04/03/05
Can anyone help? I really appreciate it.
Ismail

 

[Blorf]

Hi.

If there are multiple current records, then you take the admit date of that record, minus the latest dischdate that is less than or equal to your admit date? Even if that date is in the prior table?

Can you post a tad more of your desired results? I'm not getting the whole picture.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[Elamranii]

Sorry to reply late, I solved this problem by using excel instead of access. Thanks for your help
Ismail