fisher1977
New member
- Local time
- Yesterday, 20:56
- Joined
- Feb 27, 2009
- Messages
- 6
Hi, need your help! below is part of my raw data which contain about 2m records. I need to do in Access instead of excel.
row/columnABCDEFGH1IDStartEndGroupPeopleNewStartNewEnd21HIHI166TomHIHI32BYEBYE166MikeBYEBYE43HIHI167ETBYEBYE54BYEBYE168TomBYEBYE65HIHI168MikeHIHI76HIHI168JackHIHI87HIBYE168JennHIBYE98BYEHI169TomBYEHI109BYEBYE169CathyBYEBYE1110BYEBYE169JackBYEBYE1211BYEBYE169JackBYEBYE1312HIHI170ETBYEBYE1413HIHI171ETBYEBYE1514HIHI172ETBYEBYE1615HIHI173ETBYEBYE1716HIHI174ETBYEBYE1817HIHI175ETBYEBYE1918HIHI176ETBYEBYE2019HIHI177ETBYEBYE2120HIHI178ETBYEBYE2221BYEHI179TomBYEHI2322BYEBYE179JackBYEBYE
I need to create two new columns G and H which are “NewStart” and “NewEnd”. The rules are:
NewStart
- If column E (People) <> “ET”, then Column G = Column B, for example: cell G2=B2;
- If column E = “ET”, then Column G = column C where the FIRST PRIOR People <> “ET”, for instance, cell G4=C3, G13=C12, G14=C12……G21=C12.
NewEnd
- If column E (People) <> “ET”, then Column H = Column C, for example: cell H2=C2;
- If column E = “ET”, then Column H = column B where the FIRST NEXT People <> “ET”, for instance, cell H4=B5, H13=B22, H14=B22……H21=B22.
I tried to use Dlookup(“[End]”,”table”,”[ID]=” & [ID]-1), but it only works for the single “ET” row, for the consecutive “ET” rows like row 13 to row 21, it doesn’t work obviously. And I don’t know what the max number of consecutive “ET” rows in the rest of my data.
I can use Retain in SAS to do this but I just want to know how to do it in MS Access (2007) SQL. Thanks!
Any input would be appreciated!!!
row/columnABCDEFGH1IDStartEndGroupPeopleNewStartNewEnd21HIHI166TomHIHI32BYEBYE166MikeBYEBYE43HIHI167ETBYEBYE54BYEBYE168TomBYEBYE65HIHI168MikeHIHI76HIHI168JackHIHI87HIBYE168JennHIBYE98BYEHI169TomBYEHI109BYEBYE169CathyBYEBYE1110BYEBYE169JackBYEBYE1211BYEBYE169JackBYEBYE1312HIHI170ETBYEBYE1413HIHI171ETBYEBYE1514HIHI172ETBYEBYE1615HIHI173ETBYEBYE1716HIHI174ETBYEBYE1817HIHI175ETBYEBYE1918HIHI176ETBYEBYE2019HIHI177ETBYEBYE2120HIHI178ETBYEBYE2221BYEHI179TomBYEHI2322BYEBYE179JackBYEBYE
I need to create two new columns G and H which are “NewStart” and “NewEnd”. The rules are:
NewStart
- If column E (People) <> “ET”, then Column G = Column B, for example: cell G2=B2;
- If column E = “ET”, then Column G = column C where the FIRST PRIOR People <> “ET”, for instance, cell G4=C3, G13=C12, G14=C12……G21=C12.
NewEnd
- If column E (People) <> “ET”, then Column H = Column C, for example: cell H2=C2;
- If column E = “ET”, then Column H = column B where the FIRST NEXT People <> “ET”, for instance, cell H4=B5, H13=B22, H14=B22……H21=B22.
I tried to use Dlookup(“[End]”,”table”,”[ID]=” & [ID]-1), but it only works for the single “ET” row, for the consecutive “ET” rows like row 13 to row 21, it doesn’t work obviously. And I don’t know what the max number of consecutive “ET” rows in the rest of my data.
I can use Retain in SAS to do this but I just want to know how to do it in MS Access (2007) SQL. Thanks!
Any input would be appreciated!!!
Anyone knows how to do that first? thanks!