Hello
i have a holiday table with staff ID's and single entry dates (for holidays skipping weekends)
I need a crosstab to show the dates as the column, and the staff id as the row, and count the holidays respective to each person, so it will show a "1" or be empty, i want to be able to specify the range that the dates show (ie month by month) but i can only get it to header for dates where there is a holiday if that makes sense.
i have a table of dates which builds "from" and "to" to supply the headers
please help
Crosstab SQL so far
TRANSFORM Count(tbl_Holidays.Date) AS CountOfDate
SELECT tbl_Holidays.StaffID
FROM tbl_MDCodes, tbl_Holidays
WHERE (((tbl_Holidays.Date)=CDate([DCode] & "/" & [MCode] & "/" & [YCode])))
GROUP BY tbl_Holidays.StaffID
PIVOT CDate([DCode] & "/" & [MCode] & "/" & [YCode]);
From my understanding of what you want ...you want a query that will show as a column header a 'date' irrespective of whether there is any staff on holiday on that particular day and in which case the value should show null or where there is a holiday date assigned to a particular staff ID during the period to show itself as a value of 'one' for that date. All of this to be done as a
crosstab.
you realise of course that to show a separate columns of dates like this you are likely to run out of columns if your expectation for instance was to display an entire year. My guess is though, you don't need a
full year but something that displays a total range period inclusive of all dates including those where no one is on holiday.
OK well having played with this heres one method (
I don't know what you have specifically there with the tbl_MDCodes table but I can sense the logic:) however I'm going to suggest a slightly different method.
I have created a function for you to look at and play with that takes a DateFrom parameter and a DateTo parameter It then builds a table called tblMyDates and inserts into it all dates for that period. This table is then used in a crosstab query that is also created in which a comparison is made using your tbl_Holidays table.
All dates in the created table
tblMyDates are displayed as columns along with the StaffID and the StaffID is used to count thus returning a value for the crosstab.
In order to display all dates as columns that have no comparison value against holidays it is necessary to raise a
'placeholder' value for the matrix display which you will see when you run the crosstab query (if this placeholder row doesn't give you any undue concern merely ignore that row. It really is only there in order to ensure all dates get displayed)
Now the method you can use to run this function is to place two textboxes on a form call them txtDateFrom and txtDateTo and also place a command button on the form. Then in the command buttons 'On Click' property simply type this
- =HolidayPeriod([txtDateFrom],[txtDateTo])
The function tests to ensure you have a valid date range by calculating the difference between the two dates and rejecting anything less than zero or greater than 254 (remember column limitation)
Paste the following function into a module and give it a go. Its not the only way to do this by the way but it gives you a flavour or what you could do. If you don't like the place holder row you could always create a table to store the results and delete the placeholder row its up to you
Hope this helps
Regards Jim :)
-
-
Function HolidayPeriod(DateFrom As Date, DateTo As Date)
-
On Error Resume Next
-
'I'll leave you to do your own error handling
-
-
Dim db As DAO.Database
-
Dim tbl As DAO.TableDef
-
Dim qry As DAO.QueryDef
-
Dim mysql As String, myins As String, intdays As Long
-
Set db = CurrentDb
-
-
'calculate the difference in days for the period
-
intdays = DateDiff("d", DateFrom, DateTo)
-
'if invalid ie:less than zero or greater than total columns
-
'that can be created in one xtab recordset then exit out
-
If intdays < 0 Or intdays > 254 Then
-
msg = "You must supply a valid date range and it must not exceed 254 days"
-
MsgBox msg, vbExclamation, "System Message"
-
Exit Function
-
End If
-
-
'lets get rid of any pre-existing table
-
'this can be amended to delete from table and leaving the
-
'once its on your system
-
'turn of system messages and raise the hourglass
-
DoCmd.SetWarnings vbFalse
-
DoCmd.Hourglass vbTrue
-
DoCmd.DeleteObject acTable, "tblMyDates"
-
-
'create the tbldates table
-
Set tbl = CurrentDb.CreateTableDef("tblMyDates")
-
With tbl
-
.Fields.Append .CreateField("MyDates", dbDate)
-
End With
-
CurrentDb.TableDefs.Append tbl
-
'and throw into it a sequence of dates commensurate with the period
-
-
For i = 0 To intdays
-
myins = "INSERT INTO tblMyDates ( MyDates )" _
-
& "SELECT #" & Format(DateAdd("d", i, DateFrom), "mm/dd/yyyy") & "# AS MyDates;"
-
DoCmd.RunSQL myins
-
Next i
-
-
'define the SQL for the relevant crosstab query
-
mysql = "TRANSFORM Count(tbl_Holidays.StaffID) AS CountOfStaffID" _
-
& " SELECT nz([StaffID],'Placeholder') AS Staff_ID" _
-
& " FROM tblMyDates LEFT JOIN tbl_Holidays ON tblMyDates.MyDates = tbl_Holidays.Date" _
-
& " GROUP BY nz([StaffID],'Placeholder')" _
-
& " ORDER BY nz([StaffID],'Placeholder')" _
-
& " PIVOT tblMyDates.MyDates;"
-
-
'and then create the query!! you can comment out the next line but one once its been
-
'created on your system. if you do remember to comment out the line following it
-
DoCmd.DeleteObject acQuery, "qryHolidayDates"
-
Set qry = db.CreateQueryDef("qryHolidayDates", mysql)
-
DoCmd.OpenQuery "qryHolidayDates", acNormal, acReadOnly
-
-
'turn system messages back on drop the hourglass and refresh the database window
-
DoCmd.SetWarnings vbTrue
-
DoCmd.Hourglass vbFalse
-
RefreshDatabaseWindow
-
End Function
-
-