Look Up last Previous Value where condition Met.. 1

[Chance1234]

I have a table called investors which looks like the below

ID, Investor New Record , Investor Name, Contact
1 ,Yes ,PS1 ,John
2 ,No , ,Jack
3 ,No , ,Joe
4 ,No , ,Chris
5 ,No , ,Kate
6 ,Yes ,PS2 ,John
7 ,No , ,Lisa
8 ,No , ,Kate
9 ,No , ,Jack
10 ,No , ,Jim
11 ,Yes ,PS3 ,Joe
12 ,No , ,Jim
13 ,No , ,Jack
14 ,No , ,John
15 ,No , ,Kate

The way this feed has come in is, If we look at investor PS1 , they have 5 Contacts associated with them. Indicated by the Yes/No column.

What I would like to show is


ID, ,Investor Name, Contact
1 ,PS1 ,John
2 ,PS1 ,Jack
3 ,PS1 ,Joe
4 ,PS1 ,Chris
5 ,PS1 ,Kate

Chance,

F, G and Skipper

 

[JBinQLD]

g'day

a - isolate the records you're interested in - rsInvestors (Those with investor name)

b - create vba code that grabs the id of the current investor and the next one

c - iterate through the original table setting investor name to the current investor variable

d - if original table id reaches next investor update current and repeat loop

Hope this makes sense,

JB

 

[PHV]

a brute force method in Jet SQL:

SELECT A.ID, B.[Investor Name], A.Contact 
FROM investors A, investors B
WHERE B.ID=Dmax("ID","investors","[Investor New Record]='Yes' AND ID<=" & A.ID)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886