Table Conversion

Topic

New Reply

Hello All: Converting a FoxPro 2.5 “database” to Access97. One of the tables has 8 unique fields and 3 fields that are repeated 20 times. Need to split this table into 2 tables with a 1 to many relationship. The main table will have the 8 fields and the many table will have a foreign key from the main table and the 3 fields. I’m looking at @17000 records in the main table and probably 50,000 in the many table. If anyone has any ideas at all on how to quickly do this, please step forward. If not, hours of keystroking for someone. TIA.

Bill K.

Reply | Quote

Replies

1. Import the FoxPro table. If there is no unique ID create one, Autonumber should work.
2. Create a make table query based on the import, moving fields to the Many table along with the unique ID from the main table.
3. Delete the moved fields from the Main table
4. Create a relationship One to Many between the Main and Many table based on the unique ID

Does that make sense ?

Reply | Quote

Brian: Thanks for the response. I was probably a little more vague than I should have been in the original post. The 3 fields repeated 20 times are treated as an array, which means that 1 set of three will contain data, the remaining 19 sets of three may or may not contain data. This is my dilemma. How to assign the foreign key to a variable # of records. Looks like a series of Make Table Queries and Append Table Queries.

Reply | Quote

I’ve got it now. Yep, that’ll work, but there’s got to be an easier way. I’m willing to bet someone is going to jump right in here and post some code that will loop through the table and append records to a new table only if field Is Not Null. Wish I could help but, I’m seriously code deficient ….

Reply | Quote

Hi Bill,
Am I right in thinking that your main table has 68 fields then? Or have I missed the point? If it does, do any of the records have more than 3 of the optional fields populated? If so, how would you want to distinguish between them (i.e. you might have two records in the new table with the same foreign key)? Oh, and are the 3 fields always next to each other (might make the code a little simpler)

Reply | Quote

Hi Rory: The Old main table has 68 fields; the first 8 are unique; the next 60 are in sets of 3. The first set of 3 will always contain data, the remaining 19 sets may or may not contain data. I envision a new main table with a primary key and the eight fields, a new sub-table with a primary key, a foreign key from the main table, and the 3 fields of info. Each record in the main table may have from 1 to 20 records in the sub-table. Right now I am looking a 50-60K records in the sub-table and yes I will have multiple records in the sub-table with the same foreign key. No problem. I have begun the series of multiple extracts and will soon be working on the append query. This procedure, though tedious, seems to be working to my satisfaction. Thanks for the replies.

Reply | Quote

Bill,
Don’t know if this is too late but I think this would move all your records across:
Sub MoveRecs()
Dim dbs As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim lngRecord As Long
Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset(“tblMoveAcross”)
Set rst2 = dbs.OpenRecordset(“tblMoveTo”)
With rst1
.MoveFirst
Do Until .EOF
For lngRecord = 8 To .Fields.Count – 1 Step 3
If .Fields(lngRecord) “” And Not IsNull(.Fields(lngRecord)) Then
rst2.AddNew
rst2.Fields(1) = .Fields(0)
rst2.Fields(2) = .Fields(lngRecord)
rst2.Fields(3) = .Fields(lngRecord + 1)
rst2.Fields(4) = .Fields(lngRecord + 2)
rst2.Update
End If
Next ‘lngrecord
.MoveNext
Loop
End With
Set rst2 = Nothing
Set rst1 = Nothing
Set dbs = Nothing
End Sub
You’d then just need to delete the unwanted fields from the main table.
Hope it helps.

Reply | Quote

I should have mentioned that the code assumes that there’s a PK field as the first field in the main table and an autonumber (or whatever) field as the first field in the second table.

Reply | Quote