Combining fields from multiple rows in a single table

[Guest]

Given the following data set:
Circuit Speed Linktype ContractNum
------- ------- ---------- --------------
A 256k 1111
B NAL 2222
A NAL 3333
B 512k NAL

I would like to write a Make Query that creates a new table with one row for
each 'Circuit' value. The rows in the original table are ordered with the
first row being the most current. In the new table, I only want to fill in
the blanks in a previous row with data from a later row, but leave any
non-null values from the earlier row intact. So, I want the new table to
look like this:

Circuit Speed Linktype ContractNum
------- ------- ---------- --------------
A 256k NAL 1111
B 512k NAL 2222

Any ideas how I would do this? I appreciate any and all ideas!

Regards, Glenn

 

[Guest]

Try running this function to fill the new table, using the data from the Old
table

Function FillNewTable()
Dim MyDb As DAO.Database, OldTable As DAO.Recordset, NewTable As DAO.Recordset
Set MyDb = CurrentDb
' Open record source with the old table
Set OldTable = MyDb.OpenRecordset("Select * From TableName")
While Not OldTable.EOF
' Checking if the record exist, if it does it will check for empty fields
Set NewTable = MyDb.OpenRecordset("select * From TableName1 Where
Circuit = '" & OldTable!Circuit & "'")
If NewTable.EOF Then
NewTable.AddNew
NewTable!Circuit = OldTable!Circuit
NewTable!Speed = OldTable!Speed
NewTable!Linktype = OldTable!Linktype
NewTable!ContractNum = OldTable!ContractNum
Else
NewTable.Edit
If IsNull(NewTable!Speed) Then
NewTable!Speed = OldTable!Speed
End If
If IsNull(NewTable!Linktype) Then
NewTable!Linktype = OldTable!Linktype
End If
If IsNull(NewTable!ContractNum) Then
NewTable!ContractNum = OldTable!ContractNum
End If
End If
NewTable.Update
OldTable.MoveNext
Wend
End Function