Update null records based on non-null records above

[Guest]

If I have a table sample shown below, how can I update the State values if
Null, to the State value of the closest record above it that isn't null. For
example, I want the state for ID 3 and 4 to be populated with "CA". Also I
would want the state for ID 6 to be populated with "IL". (Purpose: I created
a table from importing a text document that had this format. But I can not
run the queries I need with out all the States being populated, since I need
to join by State to get the results I need)


ID State City
1 NY New York City
2 CA Los Angelas
3 San Diego
4 Sacramento
5 IL Chicago
6 Peoria

 

[John Spencer]

Try the following UPDATE query on your table. I suggest you test it on a
COPY of the table so you can check the results. It will probably be slow.

UNTESTED SQL Statement follows.

UPDATE YourTable
Set State = DLookup("State","YourTable", "ID =" &
DMin("ID","YourTable","State Is Not Null AND ID <" & YourTable.ID))
WHERE State Is Null

 

[David C. Holley]

1. How are you capturing the data? If a person is entering the data into
a continuous form, it is possible to alter the properties of the
controls to capture the value entered and propigate it to new records.

2. How do you know *for certain* that the city is in the same state as
the city above it?

 

[Guest]

I actually have more than 3 fields in the table do I need to list all of them
in the select statement below or is there a shortcut?
Thanks!

Set rec = db.OpenRecordset("SELECT id, state, city FROM tablename ORDER BY
ID;", dbOpenDynaset)

 

[Guest]

Also, once I have the code in a module. What do I need to do to run the
code? I would also like to run this same logic onto other tables I have.
How can I call the function so I can just name which table I would like to
run the function on? I assume I would also have to use a variable for the
tablename in the select statment?

 

[Guest]

Jojo,

Put this code in a module and run it by typing Populate_State in the
immediate window. However, before you run the code, you will need to make
sure that I used the same field names and table name.

Let me know if you have any questions.

Andrea

Here's the code:

Sub Populate_State()

Dim db As Database
Dim rec As Recordset
Dim strState As String

Set db = CurrentDb
Set rec = db.OpenRecordset("SELECT id, state, city FROM tablename ORDER BY
ID;", dbOpenDynaset)

rec.MoveFirst
' finds the first record with a state filled in
Do
If IsNull(rec("state")) Then rec.MoveNext
Loop Until Not IsNull(rec("state"))
' loops through records. Uses strState to hold the last state found and
uses this to add
' states to the records that don't have one entered.
strState = rec("state")
rec.MoveNext
Do
If IsNull(rec("state")) Then
rec.Edit
rec("state") = strState
rec.Update
Else
strState = rec("state")
End If
rec.MoveNext
Loop Until rec.EOF
End Sub

 

[Guest]

David,
In response to your questions...
1. I am loading the data from a report which is in a text file. Users are
not entering any data.
2. I know for certain that the city is in the same state as the city above
it because that is how the report is formatted. (I am using "state" and
"city" as example field names, since it is easier to picture than trying to
explain my issue with my actual field names. Same concept.)

Thanks.

 

[Guest]

In response to David's questions:
1. I am importing the data from a report which is from a text file. Users
are not entering in the data.
2. I know for certain that the city is in the same state as the city above
it because that is how the report is formatted before I import the data into
the table.

Thanks.

 

[Guest]

I was able to resolve this by using Andrea M's code.
Thank you all for your help!

 

[Guest]

Jojo,

Your first question about whether you need to include other fields, if you
haven't discovered this already, is "No." This is because you only need to
work with those fields. As a matter of fact, you really don't need to pull
in the city field either.

In answer to your second question about wanting to use the code for other
tables, you would need to change the first line to:

Sub Populate_State(strTableName As String)

You would need to add a Dim:

Dim strSQL As String

And you would need to change the Set rec line to two lines:

strSQL = "SELECT id, state, city FROM " & strTableName & " ORDER BY ID;"
Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)


So, then when you call the subroutine you would type:
Populate_State ("tablename")

I'm sure there are other ways to do this to. I'm glad this worked for you.