Using data in the import to selectively insert

[mrbaggins]

Alright, I'm updating an old database (which is a mess) into a nice neat new relational one.



Unfortunately they want the 40,000 rows of old data brought across.

NB: I've cut the irrelevant fields for ease of explaining.

What it HAS is:
AutoNumber - Name - OldActivityNumber - OldActivityName

EG:
1 - Steve - 252 - Skiing
2 - Mary - 252 - Skiing
3 - Steve - 333 - Walking
4 - Mary - 699 - Running

The problem being that one person can do many activities and being flatfile, there was heaps of duplicate data on each person, instead of just being a link between an ID for a person and an ID for an activity.

I've brought all the separate activities over. I've already added all the names. What I need to do is add entries to a linking table to link each name to each activity.

The new one has:
TABLE Person: ID - Name
This has all the people's names, and an Autonumber ID field.

EG:
1 - Steve
2 - Mary

TABLE Activity: NewActivityNumber, ActivityName, OldActivityNumber
This contains the activity details, with an AutoNumber ID called NewActivityNumber. I've also brought in all the old numbers for this import. EG: Skiiing might be "61" in the new database, but 252 in the old one. These two numbers are stored.

EG:
61 - Skiing - 252
62 - Walking - 333
63 - Running - 699

TABLE Registration: ID - PersonID - ActivityID
This links people to activities.

EG:
1 - 1 - 61
2 - 2 - 61
3 - 1 - 62
4 - 2 - 63

All I need is to populate this Registration table.

The problem is I need to check an entry in the old table, take the ActivityNumber out, match it to the new Activity table row, pull the relevant NEWActivityNumber out, Check the entry in the Person table, pull the relevant personID out, then INSERT a row with the personID and the NewActivityID.

How do I make a loop or something that works through the old table, pulls out the details then does an SQL insert?

I'm thinking this is gonna have to be VBA, and I'm fine using it, just want to make sure it's possible before I spend days trying.

If someone can help me write the VBA it'd be great too....

[weekend00]

Populate person table:
insert into person (name) select distinct name from oldregister

Populate activity table:
insert into activity (activity, oldid) select distinct activity,oldid from oldregister

populate register table:
insert into register (personid,activityid) select person.id,activity.id from person inner join (oldregister inner join activity on oldregister.oldid = activity.oldid) on person.name=oldregister.name

[ssanfu]

If I understand right, TABLE Registration is not populated.

I called the old table (AutoNumber - Name - OldActivityNumber - OldActivityName) "OldTable"

Here is the VBA I used to populate the Registration table:

Public Sub UpdateActivity()
   Dim D As DAO.Database
   Dim O As DAO.Recordset   'OLD name TABLE
   Dim L As DAO.Recordset   ' New ACTIVITY TABLE
   Dim P As DAO.Recordset   ' New PERSON TABLE

   Dim sSQL As String
   Dim OldNum As String
   Dim sPersonName As String

   Dim k As Long

   Set D = CurrentDb
   'open recordset to be modified (source)
   '- Name -
   Set O = D.OpenRecordset("SELECT PersonName , OldActivityNumber FROM OldTable ORDER BY AutoNumber;")

   ' Open recordset on TABLE Activity
   Set L = D.OpenRecordset("SELECT NewActivityNumber, OldActivityNumber FROM Activity ORDER BY OldActivityNumber;")

   ' Open recordset on New Person table
   Set P = D.OpenRecordset("SELECT ID, TheName FROM Person ORDER BY ID;")

   If Not O.BOF And Not O.EOF Then
      O.MoveFirst
      'counter
      k = 0

      Do Until O.EOF

         OldNum = "OldActivityNumber = " & O.Fields("OldActivityNumber")
         
         'Function ConvertQuotesSingle() handles names like O'Neil
         sPersonName = "TheName = '" & ConvertQuotesSingle(O.Fields("PersonName")) & "'"

         ' find OldActivityNumber to get NewActivityNumber
         L.FindFirst OldNum

         ' find PersonName to get New ID Number
         P.FindFirst sPersonName

         If L.NoMatch Or P.NoMatch Then
            '
            ' You should add Error message here
            ' because ActivityID  or Name was not found
            ' use  msgbox or write Actto file to handle later
            '
         Else
            k = k + 1

            sSQL = "INSERT INTO Registration ( PersonID, ActivityID ) VALUES (" & P.Fields("ID") & ", " & L.Fields("NewActivityNumber") & ");"
            D.Execute sSQL, dbFailOnError
         End If


         O.MoveNext
      Loop
   End If


   MsgBox "Done.... Updated " & k & " records"

   O.Close
   L.Close
   P.Close

   Set P = Nothing
   Set L = Nothing
   Set O = Nothing
   Set D = Nothing

End Sub

'converts a single quote to two single quotes
Function ConvertQuotesSingle(InputVal)
   ConvertQuotesSingle = Replace(InputVal, "'", "''")
End Function

BTW, "Name" is a reserved word in Access and should not be used for object names....

[mrbaggins]

Yeah, I didn't think of the name being reserved word while typing this up. I've culled a lot of fields and combined FirstName and Surname just into Name for the sake of explaining the problem.

Dead right on the Table Registration being the one I want to fill.

Both of these look promising, will give them a go over the weekend and get back to you.

Thanks a heap.

Quick question: Is there a naming convention I'm not aware of that caused you to pick D O L & P as the recordSet names? Or was it just "D"atabase, "O"ld "P"erson and something else?

[ssanfu]

Quick question: Is there a naming convention I'm not aware of that caused you to pick D O L & P as the recordSet names? Or was it just "D"atabase, "O"ld "P"erson and something else?

Pretty much what I did

Dim D As DAO.Database 'Database
Dim O As DAO.Recordset 'OLD name TABLE
Dim L As DAO.Recordset ' New ACTIVITY TABLE
Dim P As DAO.Recordset ' New PERSON TABLE


I used "L" as a lookup table, but didn't change the comment . Tried to use letters that I could remember that they were being used for..... and I'm lazy.

Queries are always faster than code (in my experience), but code allows you to have more control if/when there are exceptions or errors that need to be handled. In converting a flat file db to a relational db, usually there are lots of exceptions/errors. There are *many* John Smiths... you have to have a way to uniquely identify which John Smith you are converting activities for.

And try the code on a back up first !!