There's a couple of steps to get this working. Firstly, we have to generate
a record for each date (using a Cartesian product query.) Then we have to
transform the days of the month into columns (using a crosstab query.)
The steps:
1. Create a table with just one field named (say) TheDate, of type Date.
Save the table with the name tblDate. Enter records for all the dates you
need to cover. You can use the function at the end of this reply to
programmatically add dates if you wish.
2. Create a query using both tblDate and your existing table. In the upper
pane of table design, there will be no line joining the 2 tables. Drag
tblDate.TheDate into the query design grid, and in the Criteria row under
this, enter:
Between [Leave From] And [Leave To]
Add the other fields you want to see (ID, Name, and [Leave Type].)
Test the query: it should give a record for every date in each leave record.
Save the query as (say) qryLeave.
Now we have the job of transforming that into a crosstab presentation.
3. Create a new query using qryLeave as an input "table."
Change it to a Crosstab query (Crosstab on Query menu.)
Access adds Total and Crosstab rows to the query design grid.
4. Drag ID and Name fields into the design grid.
Accept Group By in the Total row.
Choose Row Head in the Crosstab row.
5. In the next column, type this into the Field row:
TheYear: Year([tblDate].[TheDate])
and in the next free column:
TheMonth: Month([tblDate].[TheDate])
Accept Group By in the Total row, and Row Head in the Crosstab row.
6. In the next column, type this into the Field row:
TheDay: Day([tblDate].[TheDate])
Choose Column Head in the Crosstab row.
7. Drag [Leave Type] into the design grid.
In the Total row under this field, choose First.
In the Crosstab row, choose Value.
8. Open the properties box. Beside the Column Heads property, enter the
numbers 1 to 31:
1,2,3,...
And here's the function to populate the table of dates programmatically:
Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
