Return one row per Member comparing multiple episodes for each member

[laurastreng]

Hi- I'm trying to bring in the next record for a particular member, and while I've seen "LIMIT 1" used in sql within Access, I'm trying to use the Query Builder and want to know if it's possible to apply "LIMIT 1" here somehow from within the Query window itself. Or, some other way to bring in the next record for the member into Case2, then the next into Case3, etc. So I only have one row per MemberID.

I'm comparing 4 copies of the same table in order to bring in MemberID, Case1, Case1_StartDt, Case1_EndDt, Case2_StartDt, Case2_EndDt,
Case3_StartDt, Case3_EndDt, etc. (a member will at least have two cases)

So far, I have the first case, but then my next column lists all of the cases after the first case. What I'm working towards is having additional columns that read Case3, Case3_StartDt, Case3_EndDt...

SELECT DISTINCT RT.I_CONTRACT, RT.I_TRIMED_SYS_KEY AS Case1, RT.D_TMR_BEGIN AS Case1_StartDt, RT.D_DISCHARGE AS Case1_EndDt, First(IIf([RT.I_CONTRACT]=[RT_1.I_CONTRACT] And [RT_1.D_TMR_BEGIN]>=[RT.D_TMR_BEGIN] And [RT.I_TRIMED_SYS_KEY]<>[RT_1.I_TRIMED_SYS_KEY],[RT_1.I_TRIMED_SYS_KEY])) AS Case2, [RT_1.D_TMR_BEGIN] AS Case2_StartDt, [RT_1.D_DISCHARGE] AS Case2_EndDt
FROM ((((RT RIGHT JOIN CaseCountbyMember ON RT.I_CONTRACT = CaseCountbyMember.I_CONTRACT) LEFT JOIN RT AS RT_1 ON CaseCountbyMember.I_CONTRACT = RT_1.I_CONTRACT) LEFT JOIN RT AS RT_2 ON CaseCountbyMember.I_CONTRACT = RT_2.I_CONTRACT) LEFT JOIN RT AS RT_3 ON CaseCountbyMember.I_CONTRACT = RT_3.I_CONTRACT) LEFT JOIN RT AS RT_4 ON CaseCountbyMember.I_CONTRACT = RT_4.I_CONTRACT
GROUP BY RT.I_CONTRACT, RT.I_TRIMED_SYS_KEY, RT.D_TMR_BEGIN, RT.D_DISCHARGE, [RT_1.D_TMR_BEGIN], [RT_1.D_DISCHARGE]
HAVING (((First(IIf([RT.I_CONTRACT]=[RT_1.I_CONTRACT] And [RT_1.D_TMR_BEGIN]>=[RT.D_TMR_BEGIN] And [RT.I_TRIMED_SYS_KEY]<>[RT_1.I_TRIMED_SYS_KEY],[RT_1.I_TRIMED_SYS_KEY])))<>""))
ORDER BY RT.D_TMR_BEGIN;

I_CONTRACT	Case1	Case1_StartDt	Case1_EndDt	Case2	Case2_StartDt	Case2_EndDt
23119	4002205	2014-01-01	2014-01-06	7144088354	2014-02-27	2014-03-06
695	4003769	2014-01-01	2014-01-08	7144018925	2014-01-14	2014-01-19
1761	4001838	2014-01-01	2014-01-04	7144028888	2014-01-20	2014-01-21
83919	4000271	2014-01-01	2014-01-06	7144150434	2014-04-08	2014-04-12
4579	4001856	2014-01-01	2014-01-08	7144008621	2014-01-08	2014-01-18
4579	4001856	2014-01-01	2014-01-08	7144048333	2014-02-02	2014-02-05
65392	4000211	2014-01-01	2014-01-03	7144005601	2014-01-03	2014-01-23
65392	4000211	2014-01-01	2014-01-03	7144045030	2014-01-30	2014-02-02
65392	4000211	2014-01-01	2014-01-03	7144078364	2014-02-21	2014-03-11
65392	4000211	2014-01-01	2014-01-03	7144135258	2014-04-01	2014-04-04
65392	4000211	2014-01-01	2014-01-03	7144170690	2014-04-22	2014-04-27
57760	4114269	2014-01-01	2014-01-04	7144148924	2014-04-08	2014-04-11
16770	4038904	2014-01-01	2014-01-03	7144038903	2014-01-03	2014-01-09

Thanks for any assistance. Let me know if I need to clarify anything.
LauraS

[June7]

Never heard of Limit 1.

It is relatively easy to pull 'first' and 'last' records but isolating the records in the middle is difficult. If the source data has a field that assigns a sequence to the records (Case1, Case2, Case3, Case4) for each contract, a CROSSTAB could be utilized, otherwise, denormalizing will not be simple.

Options I see:

VBA code and a temp table, review http://forums.aspfree.com/microsoft-...ry-322123.html

VBA code and query to concatenate records, review http://allenbrowne.com/func-concat.html

[laurastreng]

Never heard of Limit 1.

It is relatively easy to pull 'first' and 'last' records but isolating the records in the middle is difficult. If the source data has a field that assigns a sequence to the records (Case1, Case2, Case3, Case4) for each contract, a CROSSTAB could be utilized, otherwise, denormalizing will not be simple.

Options I see:

VBA code and a temp table, review http://forums.aspfree.com/microsoft-...ry-322123.html

VBA code and query to concatenate records, review http://allenbrowne.com/func-concat.html

Hello-
Thought I'd share what I came up with to at least give me something to work with:
This is what I came up with to compare admissions:

AdmitsDiff: IIf(DLookUp("[I_CONTRACT]","ReadmitsBase","[ID]=" & [ID]-1)=DLookUp("[I_CONTRACT]","ReadmitsBase","[ID]=" & [ID]),DateDiff("d",DLookUp("[D_DISCHARGE]","ReadmitsBase","[ID]=" & [ID]-1),[D_TMR_BEGIN]))

I added other columns just so I’d be sure I was comparing apples to apples (Contract to contract, and I ended up using Last Name instead of I_CONTRACT just so it was easier to read):

PreviousDCDt: IIf(DLookUp("[I_CONTRACT]","ReadmitsBase","[ID]=" & [ID]-1)=DLookUp("[I_CONTRACT]","ReadmitsBase","[ID]=" & [ID]),[N_LAST] & "  " & DLookUp("[D_DISCHARGE]","ReadmitsBase","[ID]=" & [ID]-1))

And to see the DateDiff number along with the discharge date the current case was being compared to:

AdmitsDiff3: IIf(DLookUp("[I_CONTRACT]","ReadmitsBase","[ID]=" & [ID]-1)=DLookUp("[I_CONTRACT]","ReadmitsBase","[ID]=" & [ID]),DateDiff("d",DLookUp("[D_DISCHARGE]","ReadmitsBase","[ID]=" & [ID]-1),[D_TMR_BEGIN]) & " d,  " & DLookUp("[D_DISCHARGE]","ReadmitsBase","[ID]=" & [ID]-1))

Now I'll figure out the filtering to remove the rows where the DateDiff came up with >30.

Laura

[June7]

You have a solution?