Loop through a table and get a record for each date that fall between StartDT and End

[PJMCCS63]

My table holds all personnel which need to exempted from training
For Example:
Exempted Table
Name, Exempted Reason, StartDT, EndDT

Smith, J, Reassign to X Dept, 3/1/2010, 7/30/2010
Jones, B, Extended Leave, 5/15/2010, 6/15/2010

So I would want a New Table to look like the following
Exempted Revised
Name, ExemptedMonth
Smith, J., 3
Smith, J., 4
Smith, J., 5
Smith, J., 6
Smith, J., 7
Jones, B, 5
Jones, B, 6

[tasawer]

you will need work along the lines of setting up an array.
Extract the month part of dates and store
Use Sql to Create a table from the Array.

I think this should start you off

'St up Counters
    Dim lngCounter As Long
    Dim lngCount As Long

'Set Up arrays
    Dim MonthArray() as Long
    Dim NameArray() as String

'Month Counters
    Dim lngMonths as Integer 'No of Months
    Dim monthCount as Integer
    Dim intStartMonth as Integer 'First Month

'Configure Datbase and RecordSet
    Dim rs As Recordset
    Dim db As Database
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("ExemptedTable", dbOpenDynaset)

    'Count no. of Records
    rs.MoveFirst
    rs.MoveLast
    lngCount = rs.RecordCount

'ReDimension Arrays after you know how many records exist
    ReDim MonthArray(1 to lngCount) As Long
    ReDim NameArray(1 to lngCount) as String

'Store Values in Array
    rs.moveFirst
    For lngCounter=1 to lngCount
        intStartMonth=Format(me.StartDT, "m")-1
        lngMonths=Format(me.EndDT, "m")-Format(me.StartDT, "m")
        For MonthCount=1 to lngMonths
            NameArray(MonthCount)=rs!Name
            MonthArray(MonthCount)=intStartMonth + MonthCount
            Next MonthCount
        rs.MoveNext
        Next lngCounter

'Display Values of Array in msgbox
'Write SQL Code to Create Table based on values in Array

[PJMCCS63]

Awesome Help, still confused

I think what you given will work eventually, after I figure out how to translate this in to the database. With that being said it is unclear to me, being quite the novice, on how to get the Array into a table. My Access 2007 skill are limited. Please provide any further clues that you can. All your is greatly appreciated.

[tasawer]

Hi,

Please study the attached database that I created for you.
you can take further ideas from it to create your database, the way you want

Regards