CrossTab Dates Query With blanks

**Jim Doherty** · Nov 26 '07, 03:30 AM

Originally posted by Dan2kx

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_Holid ays.Date) AS CountOfDate
SELECT tbl_Holidays.St affID
FROM tbl_MDCodes, tbl_Holidays
WHERE (((tbl_Holidays .Date)=CDate([DCode] & "/" & [MCode] & "/" & [YCode])))
GROUP BY tbl_Holidays.St affID
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

Code:

=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 :)

Code:

 
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

**Dan2kx** · Nov 26 '07, 07:40 PM

Ok thanks for the advice, not had the chance to test it yet, the boss has dropped a load more "requiremen ts" on me, he want the thing to output into am/pm for each date, but the biggest problem i face now is that he wants me to set up peoples shift patterns in minutes for 2 weeks (am and pm) so that if someone books off 2 weeks then it selects the correct ammounts of minutes for each am and pm per day, confusing i know,

anyways...

do you (or anyone else) know of any easy way to give odd and even weeks?

i will be storing the shift info in a big (monam, monpm, tueam, tuepm.......... ) with staff id and week number 1,2 so i want to select which week the day is and then return the values,

is there an easier way then listing week beginning dates next to 1,2??

thanks for ya help

CrossTab Dates Query With blanks

CrossTab Dates Query With blanks

Comment

Comment