Fill null fields from last record non-null in that field

[CS]

Access 2000 -- I have a database table which is built from an import from a
text file.

I want to fill blank fields in records from the last previous record that
has a value in that, and then when Access encounters a new record with a
value in it, have this pasted into the fields below it.

Now, it looks like this:
1 "0010099"
2
3
5 "0010100"
6
7
8
9
10 "0010101"
11
12

I want it to look like this:
1 "0010099"
2 "0010099
3 "0010099
5 "0010100"
6 "0010100"
7 "0010100"
8 "0010100"
9 "0010100"
10 "0010101"
11 "0010101"
12 "0010101"

I have the feeling this has been asked a million times, but that I'm not
phrasing it correctly in my web-search.

I know how to do an update query, but cannot figure out how this should be
constructed to do what I want. I can handle some VBA.

Thanks in advance for any help.

 

[Allen Browne]

You could use a subquery to find the previous non-null value.

The expression to use in the Update row of your query would be something
like this:
(SELECT TOP 1 [Field2]
FROM Table1 AS Dupe
WHERE (Dupe.ID <= Table1.ID)
AND ([Field2] Is Not Null)
ORDER BY Dupe.ID DESC)

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

 

[CS]

Thanks Allen! I did find something that got me the values I needed (I
turned it into a make table query, as I couldn't figure out how to make it
update the existing table). Here's the sql:

SELECT MyTableAlias.ID, MyTableAlias.ThreadID, IIf([ThreadID] Is
Null,DLookUp("[ThreadID]","MacroTestPlay6","[ID] = " &
DMax("[ID]","MacroTestPlay6","[ID]<" & MyTableAlias.ID & " AND [ThreadID] Is
Not Null")),[ThreadID]) AS Lookup, MyTableAlias.DateTime, MyTableAlias.Name,
MyTableAlias.Email, MyTableAlias.URL, MyTableAlias.IP, MyTableAlias.Comment
INTO ConsolidateThread
FROM MacroTestPlay6 AS MyTableAlias
ORDER BY MyTableAlias.ID;

Let me know if I'm using a sledgehammer on a carpet tack.

You could use a subquery to find the previous non-null value.

The expression to use in the Update row of your query would be something
like this:
(SELECT TOP 1 [Field2]
FROM Table1 AS Dupe
WHERE (Dupe.ID <= Table1.ID)
AND ([Field2] Is Not Null)
ORDER BY Dupe.ID DESC)

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Access 2000 -- I have a database table which is built from an import from
a text file.

I want to fill blank fields in records from the last previous record that
has a value in that, and then when Access encounters a new record with a
value in it, have this pasted into the fields below it.

Now, it looks like this:
1 "0010099"
2
3
5 "0010100"
6
7
8
9
10 "0010101"
11
12

I want it to look like this:
1 "0010099"
2 "0010099
3 "0010099
5 "0010100"
6 "0010100"
7 "0010100"
8 "0010100"
9 "0010100"
10 "0010101"
11 "0010101"
12 "0010101"

I have the feeling this has been asked a million times, but that I'm not
phrasing it correctly in my web-search.

I know how to do an update query, but cannot figure out how this should
be constructed to do what I want. I can handle some VBA.

Thanks in advance for any help.

 

[Allen Browne]

DMax() will work.

It's slower than a subquery, but this is probably a one-off so that may not
matter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen! I did find something that got me the values I needed (I
turned it into a make table query, as I couldn't figure out how to make it
update the existing table). Here's the sql:

SELECT MyTableAlias.ID, MyTableAlias.ThreadID, IIf([ThreadID] Is
Null,DLookUp("[ThreadID]","MacroTestPlay6","[ID] = " &
DMax("[ID]","MacroTestPlay6","[ID]<" & MyTableAlias.ID & " AND [ThreadID]
Is Not Null")),[ThreadID]) AS Lookup, MyTableAlias.DateTime,
MyTableAlias.Name, MyTableAlias.Email, MyTableAlias.URL, MyTableAlias.IP,
MyTableAlias.Comment INTO ConsolidateThread
FROM MacroTestPlay6 AS MyTableAlias
ORDER BY MyTableAlias.ID;

Let me know if I'm using a sledgehammer on a carpet tack.

You could use a subquery to find the previous non-null value.

The expression to use in the Update row of your query would be something
like this:
(SELECT TOP 1 [Field2]
FROM Table1 AS Dupe
WHERE (Dupe.ID <= Table1.ID)
AND ([Field2] Is Not Null)
ORDER BY Dupe.ID DESC)

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html