Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table Conversion

    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.



    [img]/w3timages/icons/dizzy.gif[/img][img]/w3timages/icons/dizzy.gif[/img]

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Conversion

    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 ? [img]/w3timages/icons/smile.gif[/img]

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Conversion

    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.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Conversion

    I've got it now. Yep, that'll work, but there's got to be an easier way. [img]/w3timages/icons/crazy.gif[/img] 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 .... [img]/w3timages/icons/spook.gif[/img]

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Table Conversion

    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)
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Conversion

    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.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Table Conversion

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Table Conversion

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •