flag succeeding records 1

[markbrum]

Hi,
I have a table which has a initial record with a name, followed by a variable number of records without a name, then another record with a name etc. I would like to fill in the records without names with the preceding name eg:-

BEFORE
id,name
1,'dave'
2,''
3,''
4,''
5,'john'
6,''
7,''
8,'chris'
9,''
10,''
11,''
12,''

AFTER
id,name
1,'dave'
2,'dave'
3,'dave'
4,'dave'
5,'john'
6,'john'
7,'john'
8,'chris'
9,'chris'
10,'chris'
11,'chris'
12,'chris'

Can anyone please tell me how to get started? Thanks Mark.

 

[KirbyWallace]

I have this feeling something is wrong with the way you are going about this... Almost like you are using a table to do something it ought not to do...

What I'm thinking is your solution should look more like:

Dave,1,2,3,4
John,5,6,7
Chris,8,9,10,11,12

Names in one table, values in another.

But I'll see what we can do about this arrangement. It could only work if your IDs were sequential for each name. ie, this would not be workable:

1,dave
2,dave
3,dave
4,chris
5,dave
6,chris

Your approach is going to be something like:

open recordset order by ID

do while not rs.eof

cTemp = name
do while name = ctemp or name=""
if rs("name")="" Then
rs("name") = cTemp
end if
rs.movenext
cTemp = rs("name")
loop
rs.movenext
loop

That's just off the cuff. haven't even synax checked it. Just to give you an idea of what it will take. You can fine tune it for your needs.

 

[dhookom]

I named the table "Before" and changed the field "Name" to "TheName" since Name is a reserved word. Try this in a query:

SELECT Before.ID, (Select Top 1 TheName FROM Before b WHERE b.ID <= Before.ID AND theName is not Null ORDER BY ID DESC) AS AfterName
FROM Before;

[tt][blue]
ID AfterName
1 Dave
2 Dave
3 Dave
4 Dave
5 John
6 John
7 John
8 Chris
9 Chris
10 Chris
11 Chris
12 Chris
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

My solution is based on a null in theName field as opposed to a zero-length-string. If you truly have a-l-s then try:

SELECT Before.ID, (Select Top 1 TheName FROM Before b WHERE b.ID <= Before.ID AND theName & "" <> "" ORDER BY ID DESC) AS AfterName
FROM Before;

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Another way:

SELECT A.ID, B.Name
FROM yourTable AS A, yourTable AS B
WHERE B.ID=(SELECT Max(ID) FROM yourTable WHERE Trim(Name & '')<>'' AND ID<=A.ID)

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

 

[markbrum]

Thanks everyone for your responses. I used the first reply from dhookom and it worked great.

Thanks again, Mark.