one record into multiple records based on from and to date

[June7]

This code is behind form? Form has a button named Command0?

[June7]

Dim rs As DAO.Recordset, intMons As Integer, x As Integer, dteDate As Date
Set rs = CurrentDb.OpenRecordset("SELECT HSSN, EffDate, ToDate FROM tablename")
With rs
    .MoveLast
    .MoveFirst
    While Not .EOF
        intMons = DateDiff("m", !EffDate, !ToDate) + 1
        dteDate = DateSerial(Year(!EffDate), Month(!EffDate), 1)
        For x = 1 To intMons
            CurrentDb.Execute "INSERT INTO EmpHistory(HSSN, NewDate) VALUES('" & !HSSN & "', #" & dteDate & "#)"
            dteDate = DateAdd("m", 1, dteDate)
        Next
        .MoveNext
    Wend
End With

[kwooten]

Dim rs As DAO.Recordset, intMons As Integer, x As Integer, dteDate As Date
Set rs = CurrentDb.OpenRecordset("SELECT HSSN, EffDate, ToDate FROM tablename")
With rs
    .MoveLast
    .MoveFirst
    While Not .EOF
        intMons = DateDiff("m", !EffDate, !ToDate) + 1
        dteDate = DateSerial(Year(!EffDate), Month(!EffDate), 1)
        For x = 1 To intMons
            CurrentDb.Execute "INSERT INTO EmpHistory(HSSN, NewDate) VALUES('" & !HSSN & "', #" & dteDate & "#)"
            dteDate = DateAdd("m", 1, dteDate)
        Next
        .MoveNext
    Wend
End With

Thanks, I ran a one record test where the EffDate is 10/4/2010 and the program only gave me one record where the NewDate is 10/1/2010 instead of 10/1/2010, 11/1/2010, etc... Also, if we still have the HSSN as active then the ToDate is set to 12/30/2099.. Is there anything that can be added where if the Todate is greater than Date() then set the Todate to Date()?

[June7]

intMons = DateDiff("m", !EffDate, IIf(!ToDate > Date(), Date(), !ToDate)) + 1

I don't see why code would not create multiple records. What was the ToDate value?

Step Debug. Refer to link at bottom of my post for debugging guidelines.

[kwooten]

the ToDate value was 12/30/2099.. I will update the if statement and re-run

[kwooten]

I hope it is ok that I uploaded a small sample of the dummy data

[June7]

HSSN field is designated primary key. Primary key cannot have duplicate values, therefore can save only one record for each HSSN.

Do you want to populate the other 2 fields? Include them in the UPDATE action.

[kwooten]

ah ok.. I changed the index and re-ran and it looks to have run correctly.. thanks so much for the help

[kwooten]

the file is running HSSN and NewDate.. What if I want to see EFFDATE, ToDate and a new piece of data called HSTATUS? I tried putting it in the "Insert Into" line but it didnt seem to like it.

[June7]

Show me what it doesn't like. What is error message? Did you create field in table for HSTATUS?

[kwooten]

Show me what it doesn't like. What is error message? Did you create field in table for HSTATUS?

I got it worked out.. I had:

CurrentDb.Execute "INSERT INTO EmployeeHistory_tbl(HSSN, EffDate, ToDate, HSTATUS, NewDate) VALUES('" & !HSSN & "', '" & !HSTATUS & "', #" & dteDate & "#)"" and I do have HSTATUS in the FROM table

instead of:

CurrentDb.Execute "INSERT INTO EmployeeHistory_tbl(HSSN, HSTATUS, NewDate) VALUES('" & !HSSN & "', '" & !HSTATUS & "', #" & dteDate & "#)""

This is giving me a new problem, however.. I have instances where the status changes so I may have a 001 code from 7/1/2010 to 12/1/2014 then the code changes to 302 from 12/1/2014 to 3/11/2015 so I am getting a duplicate record for 12/1/2014..

[June7]

Then records are not correct. 12/1/2014 should not be in both records.

001 should be from 7/1/2010 to 11/30/2014 and 302 from 12/1/2014 to 3/11/2015.

or

001 should be from 7/1/2010 to 12/1/2014 and 302 from 12/2/2014 to 3/11/2015.

[kwooten]

i would tend to agree but I dont maintain this data.. it is just what I have to work with.. is there a way to normalize it?

[June7]

Bad data in means bad data out.

I guess you will have to decide which record to delete. Access cannot determine unless you can program a rule. So on what criteria will you base your decision? Keep the first or the latest? The code can do a DLookup and if data combination already in table, skip the INSERT code.

If IsNull(DLookup(...)) Then CurrentDb.Execute ...

Or maybe if data is found, delete record in favor of the newer.

[kwooten]

If the 001 has an end date of 12/1/2014 and the 302 has a start of 12/1/2014 then I would prefer to keep the latest; 302 since that is the date of the new status...

you have been a tremendous help with this. I really appreciate all your efforts.