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...
Code:
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