Combining fields from multiple rows in a single table


G

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
 
Ad

Advertisements

G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top