Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Unanswered: Converting form info to database

    I have a list of info from form dictation that needs to become a searchable database.
    I have set up normal table before but do not know how to conver the data into a normal table.

    The format it will come in is:

    IDCode FieldName FeildValue
    24643134 FirstName Tim
    24643134 DOB 10/7/75
    24643134 Height 1.78
    24643135 FirstName Sarah
    24643135 DOB 1/8/00
    24643135 Height 0.79

    One table with 10 different feild codes and eventually a few thousand ID codes.
    I tried using quereis to pull the information together for each IDCode but they got too slow with multiple feild names.

    Any ideas greatly appreciated.

    Thanks!

    Belinda

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Converting form info to database

    Good Morning Belinda!

    Sorry but I don't know what you mean by "form dictation"... What kind of file is the data coming in?

  3. #3
    Join Date
    Jan 2004
    Posts
    4

    Re: Converting form info to database

    The information is being dictated (using Dragon and a 3rd party-add-on) into a Microsoft Word based form. The feilds from the form will then be a file with the three columns of information as above. The file will be a text file.


    Originally posted by Trudi
    Good Morning Belinda!

    Sorry but I don't know what you mean by "form dictation"... What kind of file is the data coming in?

  4. #4
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Converting form info to database

    Originally posted by belinda
    The information is being dictated (using Dragon and a 3rd party-add-on) into a Microsoft Word based form. The feilds from the form will then be a file with the three columns of information as above. The file will be a text file.
    Okay... So we have a text file that contains the data just like you've shown in your first post...

    Is this a delimited text file, or fixed width?... Have you tried importing the file directly into the database as is?... If you do that you could then write code to open and iterate through that recordset, and append the data to your other table in the correct fields...

  5. #5
    Join Date
    Jan 2004
    Posts
    4

    Re: Converting form info to database

    Originally posted by Trudi
    Okay... So we have a text file that contains the data just like you've shown in your first post...

    Is this a delimited text file, or fixed width?... Have you tried importing the file directly into the database as is?... If you do that you could then write code to open and iterate through that recordset, and append the data to your other table in the correct fields...
    It is a delimited text file.
    I can import as is but then I get stuck. I tried using quiereis to reorganise it but they had ambigous joins.

    What sort of code could I write to append the data? (I have tended to avoid code and use queries and simple macros).
    But this time I'm stuck with a project started by someone else.

    Thanks
    Belinda

  6. #6
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Converting form info to database

    Originally posted by belinda
    It is a delimited text file.
    I can import as is but then I get stuck. I tried using quiereis to reorganise it but they had ambigous joins.

    What sort of code could I write to append the data? (I have tended to avoid code and use queries and simple macros).
    But this time I'm stuck with a project started by someone else.

    Thanks
    Belinda
    It's okay Belinda... One more question as I work on this code... Is there always going to be three records per IDCode in the imported data?... and always in the same order... FirstName, DOB and then Height... and then the next IDCode?

  7. #7
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Converting form info to database

    Okay.. This is just an example...

    Say the imported data is in a table named "tblImport"... and I'm assuming that the fields in the table are named as your first post suggests... [IDCode],[FieldName],[FieldValue]

    The new table is named "tblFixedImport"... and the fields we are dealing with are [IDCode], [FirstName],[DOB],[Height]...

    It looks like one [IDCode] refers to the data for one record in the new table... so sounds like we need a simple control break... My code would look something like this...

    Code:
    Private Sub FixImportData()
    'declare your object variables...
    Dim db As DAO.Database
    Dim rstOld As DAO.Recordset
    Dim rstNew As DAO.Recordset
    
    'declare the variables to hold values
    Dim varID As Variant
    Dim strFName As String
    Dim datDOB As Date
    Dim dblHeight As Double
    
    'set object variables
    Set db = CurrentDb
    Set rstOld = db.OpenRecordset("tblImport")
    Set rstNew = db.OpenRecordset("tblFixedImport")
    
    'Move to the first record of the imported data table... 
    rstOld.MoveFirst
    'Assign first IDCode in variable that holds the current IDCode we are working on 
    varID = rstOld![IDCode]
    
    'Loop until the end of the imported data
    Do While rstOld.EOF = False
    
        Do While rstOld![IDCode] = varID   'Loop until IDCode changes
            Select Case rstOld![FieldName]
                Case "FirstName"
                    strFName = rstOld![FieldValue]
                Case "DOB"
                    datDOB = rstOld![FieldValue]
                Case "Height"
                    dblHeight = rstOld![FieldValue]
                Case Else
                    MsgBox "Unknown field name for IDCode = " & varID
                    Exit Sub
            End Select
            rstOld.MoveNext
        Loop
            
        'append the record to the new table
        rstNew.AddNew
        rstNew![IDCode] = varID
        rstNew![FirstName] = strFName
        rstNew![DOB] = datDOB
        rstNew![Height] = dblHeight
        rstNew.Update
    
        'since the IDCode changed, re-initialize variables
        varID = rstOld![IDCode]
        strFName = ""
        datDOB = Null
        dblHeight = 0
    
    Loop
    
    'release the resources used for the object variables
    Set rstOld = Nothing
    Set rstNew = Nothing
    Set db = Nothing
    
    End Sub
    Let me know if there's something that I was assuming incorrectly... I haven't tested this, so make sure you make a back up of your data before playing with it...

  8. #8
    Join Date
    Jan 2004
    Posts
    4
    Trudi,

    Thank you.

    I'm trying it out now and it seems to be working. I'll have a play with it.

    THANK YOU again

    Belinda

Posting Permissions

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