Filling in empty data fields

I

Iman

I have the table below, pn is the index, I need to have the data repeated in
the blank fields with the data above it.

Can some one help.


pn cd ea attrib document
50001170 01 26200 DR STIP
STIP
STIP
50001288 01 27681 BDR SHOPP
50001314 01 29030 BDR STIP
STIP
50001317 01 29080 DR STIP
 
J

John Vinson

I have the table below, pn is the index, I need to have the data repeated in
the blank fields with the data above it.

Can some one help.


pn cd ea attrib document
50001170 01 26200 DR STIP
STIP
STIP
50001288 01 27681 BDR SHOPP
50001314 01 29030 BDR STIP
STIP
50001317 01 29080 DR STIP
Click to expand...

Well... You've got REAL trouble, if this is in an Access Table. Tables
*have no order* - they're unordered "buckets" of data. There is - to
Access - absolutely no distinction between the second, third, and
sixth rows above. No Update query you can write would do what you
describe.

Are these records coming from some external (ordered) source such as a
spreadsheet or a text file?


John W. Vinson[MVP]
 
I

Iman

yes ODBC to FoxPro

John Vinson said:
Well... You've got REAL trouble, if this is in an Access Table. Tables
*have no order* - they're unordered "buckets" of data. There is - to
Access - absolutely no distinction between the second, third, and
sixth rows above. No Update query you can write would do what you
describe.

Are these records coming from some external (ordered) source such as a
spreadsheet or a text file?


John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

yes ODBC to FoxPro
Click to expand...

I'm not an expert in FoxPro but is there a RowNumber in the Foxpro
table?

What you may need to do is open a Recordset and step through it row by
row, setting the NULL values to the previous row's value: untested air
code -

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim pn As Variant
Dim cd As Variant
Dim ea As Variant
Dim attrib As Variant
Set rs = db.OpenRecordset("attached ODBC table")
Set rsOut = db.OpenRecordset("local Access table")
rs.MoveFirst
' assume the first record has data
pn = rs!pn
cd = rs!cd
ea = rs!ea
attrib = rs!attrib
Do Until rs.EOF
rsOut.AddNew
' write the record to the output table
rsOut!pn = pn
rsOut!cd = cd
rsOut!ea = ea
rsOut!attrib = attrib
rsOut!Document = rs!Document
rsOut.Update ' write the first record
rs.MoveNext
' set the memory variables to the new values from the
' input table if there is a value, otherwise keep the old
pn = nz(rs!pn, pn)
cd = nz(rs!cd, cd)
ea = nz(rs!ea, ea)
attrib = nz(rs!attrib, attrib)
Loop
rsOut.Close
rs.Close
Set rsOut = Nothing
Set rs = Nothing


John W. Vinson[MVP]
 
Top