[AccessD] A2K CrossTab Q

On 13 Feb 2003 at 15:41, Darren Dick wrote:

> Hello all
> I have managed to get a crosstab query working (hooray) That's the good bit.
> It is designed to show bookings over a week and also their start times
> A Bit like a roster
> 
> 	1/1/2003	2/1/2003	3/1/2003 etc
> 08:00  Booking No3
> 09:00			Booking No2
> 10:00	Booking No1
> 11:00					Booking No6
> 12:00
> etc
> 
> The problem is... The XTab only shows the dates or time only where a booking
> exists.
> EG if there are only 4 start times for all the bookings for a week then only
> those 4 times appear
> on the left hand side. Same goes with the dates. If only 2 dates in the
> whole week have 
> bookings in them then only those 2 dates will appear as column headers.
> I know that is what the Crosstab is designed to do, but how can I get the
> result to show all times
> from say 7:00am to 6:00pm and from 1/1/2003 to 7/1/2003 even though only
> some of them will have a value
> 

Assuming your tblBookings contains:
BDate (Date/Time)
BTime (Date/Time)
Comments (Text)


Create two extra Tables
==================

tblTimes - Contains one Column sTime (Date/Time) 
tblDates - Contains one Column sDate (Date/Time)


Before running the crosstab:
=====================
1. Fill tblTime with a series of records containing the times,  07:00,.08:00 etc
(you can probably populate this table once and then leave it there permanently)

2. Clear tblDates and then populate it with all of the dates between the start and end dates.
(there are many ways to do this in code)

Now all you need is two queries:
=========================
Query QPartA  (Builds the full set of dates and times):
-------------------
SELECT tblDates.sDate, tblTimes.sTime
FROM tblDates, tblTimes
GROUP BY tblDates.sDate, tblTimes.sTime;

Query XTab  (Crosstabs the full set of dates and times and inserts any booking info):
-------------------
TRANSFORM Max(tblBookings.Comment) AS Booking
SELECT qPartA.sTime
FROM qPartA LEFT JOIN tblBookings ON (qPartA.sTime = tblBookings.BTime) AND (qPartA.sDate = 
tblBookings.BDate)
GROUP BY qPartA.sTime
PIVOT qPartA.sDate;


-- 
Stuart McLachlan
Lexacorp Ltd
Application Development,  IT Consultancy
http://www.lexacorp.com.pg