Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Location
    Greensboro, North Carolina, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add New records (version 2002)

    Hi all, I am importing a .csv file into my database that contains three fields: employee name, work date, # of days worked (ex. 'Sue' , '05/10/03' , '2'). What I need to end up with is a record for each day the employee worked, i.e. I would need a record for Sue for May 10th and one for May 11th). Is there a relatively simple way to use the # of days worked to add a record for each day in addition to the "date worked" field? Thanks so much!!

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Add New records (version 2002)

    I think you will be up for some VBA code to do this. What you will need is something like:

    Dim dbs as DAO.Database, rs as DAO.Recordset, rsOut as DAO.Recordset
    Set dbs = CurrentDB
    Set rs = dbs.OpenRecordset("ImportedTable")
    Dim iDays as Integer, dteDate as Date
    Set rsOut = dbs.OpenRecordset("NewTable")
    Do While Not rs.EOF
    dteDate = rs!WorkDate
    For iDays = 1 to rs!NumDaysWorked
    rsOut.Addnew
    rsOut!EmployeeName = rs!EmployeeName
    rsOut!WorkDate= dteDate
    rsOut.Update
    dteDate = dteDate + 1
    Next iDays
    rs.MoveNext
    Loop
    Set rsOut = Nothing
    Set rs = Nothing
    Set dbs = Nothing

    I have assumed that NewTable (choose your own name here) is a table that has already been setup with at least 2 fields (EmployeeName and WorkDate).
    I have used the DAO object model, make sure that a reference is set to Microsoft DAO 3.6 (or something like that)..

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Location
    Greensboro, North Carolina, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add New records (version 2002)

    It worked perfectly. Thanks Pat, you saved me hours of time and lots of headaches!

Posting Permissions

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