Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    4

    Unanswered: Help with transferring records

    Folks,

    Any ideas how I can separate certain records from a table into a new field in a new table.

    I have a table with one field, which contains many records. I want to be able to transfer records with a certain text string to a new field in a new table. E.g: Say a record containing the text of a month transferred to a 'date' field in the new table, a record with the text of a surname transferred to a 'surname' field in the new table...

    Any ideas would be greatly appreciated

  2. #2
    Join Date
    Mar 2003
    Location
    UK
    Posts
    71

    Re: Help with transferring records

    Originally posted by morcheeba
    Folks,

    Any ideas how I can separate certain records from a table into a new field in a new table.

    I have a table with one field, which contains many records. I want to be able to transfer records with a certain text string to a new field in a new table. E.g: Say a record containing the text of a month transferred to a 'date' field in the new table, a record with the text of a surname transferred to a 'surname' field in the new table...

    Any ideas would be greatly appreciated

    Are you saying that currebtly your records look like this:

    Data: JIM JONES 24/08/1978 etc?

    and you want:

    FName: JIM
    SName: JONES
    DOB: 24/08/1979
    etc: etc

    ????

  3. #3
    Join Date
    Aug 2003
    Posts
    4
    Kinda,

    Currently the data is like this: (all in one field)

    Feb 21, 2001
    Murphy, Paul
    ...
    ...
    ...
    Mar 05, 2002
    Murphy, Paul
    ...
    ...

    I want to be able to put this into a new table like:

    Date: Surname:
    Feb 21, 2001 Murphy
    Mar 05, 2002 Murphy

  4. #4
    Join Date
    Mar 2003
    Location
    UK
    Posts
    71
    ok.

    right, the best way i think to do this is to create a recordset of the old info and loop throug it adding the new info into variables and then into the new tabel.

    i've got some code that does this, i'll look it out for you and post it later

  5. #5
    Join Date
    Mar 2003
    Location
    UK
    Posts
    71
    here's a sub to do what you want. it doesn't have any error handeling for things like duplicate records etc, but you can add that in yourself.
    if you can't see how it works and what you need to change for your data let me know and i'll explain more. here's the code (you can just call this from the debug window and it will run):


    Public Sub addrecords()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld1 As String 'I'm using strings for the fields but set them to whatever your
    Dim fld2 As String ' fields are
    Dim fld3 As String
    Dim i As Integer
    Dim strSQL As String
    Dim q, c

    q = Chr(34) 'allows you to user " marks in the SQL statment
    c = Chr(44)

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tbTest") 'This opens the first table into a recordset

    i = 1 'set i as 1 so we can use it as a counter for which record we are at

    Do Until rs.EOF = True
    If i > 3 Then i = 1 '3 is how many test fields i had. increase this number if you have more

    Select Case i
    Case 1
    fld1 = rs.Fields(0)
    Case 2
    fld2 = rs.Fields(0)
    Case 3
    fld3 = rs.Fields(0)
    End Select

    If i = 3 Then 'when we are at the last record for this group we add the new record into the new table

    strSQL = "INSERT INTO tblTest (field1, field2, field3) VALUES (" _
    & q & fld1 & q & c & q & fld2 & q & c & q & fld3 & q & ")"

    MsgBox strSQL
    DoCmd.RunSQL strSQL

    End If


    i = i + 1
    rs.MoveNext

    Loop


    End Sub

Posting Permissions

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