Create Table

[geoffcodd]

Hi there,

I have a xls with data in the following format

JONES*CAITLIN*
LUMIÈRE, 17
08191 - RUBI - BARCELONA
Hazle un regalo...
936 973 241Ver mapaVer cómo irMi Agenda
JONES, A.**
RIERETA, 1
08001 - BARCELONA - BARCELONA
Hazle un regalo...
934 412 257Ver mapaVer cómo irMi Agenda

I import this into a table, what I now need to do is to put it in a table under the correct fields.

The first row and every 5th row should go in field 1, the 2nd and every 6th row in field 2 and so on.

Any help, always appreciated.

Thanks
Geoff

 

[raskew]

Geoff-

Here's a small example (A97 file attached) to play with. The following code creates a new table with five fields then populates it by looping thru the
original imported data (tblAgenda) and uses the Choose() function to select
the correct field to place the data.

Public Sub AgendaFix(pNewTable As String)
'*******************************************
'Re:        [url]http://www.access-programmers.co.uk/forums/showthread.php?t=71294[/url]
'Purpose:   Populate five fields
'Coded by:  raskew
'Inputs:    call agendafix("NewAgenda")
'Output:    Table NewAgenda created and
'           populated from data in tblAgenda
'*******************************************

Dim db            As Database
Dim rs            As Recordset
Dim rs2           As Recordset
Dim i             As Integer
Dim n             As Integer
Dim strSQL        As String

    Set db = CurrentDb
    
    'Step 1) Prepare temp table (may not be necessary if
    '           adding to an existing table)
    On Error Resume Next
    With db
        .Execute "DROP TABLE " & pNewTable & ";"
        .Execute "CREATE TABLE " & pNewTable & " ( FullName TEXT (50)," _
                 & " Address1 TEXT (50), Address2 TEXT (50), HazleStuff TEXT (50)," _
                 & " AgendaStuff TEXT (50) );"
    End With
    db.TableDefs.Refresh
    strSQL = "SELECT * from " & pNewTable & ";"
    Set rs2 = db.OpenRecordset(strSQL)
    
    'Step2) Loop thru tblAgenda, populating fields in table NewAgenda
    
    strSQL = "SELECT txtAgenda from tblAgenda;"
    n = 0
    Set rs = db.OpenRecordset(strSQL)
    Do While Not rs.EOF
       rs2.AddNew
       For i = 1 To 5
          rs2(Choose(i, "FullName", "Address1", "Address2", "HazleStuff", "AgendaStuff")) = rs!txtAgenda
          rs.MoveNext
          If rs.EOF Then Exit For
       Next i
       rs2.Update
   Loop
   
   rs.Close
   rs2.Close
   db.Close
   Set db = Nothing

End Sub

HTH - Bob

 

[geoffcodd]

thank you very much works a treat

thanks
geoff