Show All Dates

[Kerry Purdy]

Hiya

I have 3 queries pulling data from a few tables to make one new table. This
table is then used in a Cross Tab query to look like a calendar.

Each of the 3 queries uses a date range from my switchboard (StartDateEntry
and EndDateEntry)

The answers only show those dates that have data against them. I need each
day between those dates to show so that when displayed in my cross tab query
each day has a column.

I have managed to do this with the room names - note I am showing Room ID in
each query then introduced the Room Data to show the room name in the
crosstab query and chaging the join.

Hope this makes sense.

Many thanks for your time

Kerry

Query 1 (Make Table "tbl schedules" Query)
SELECT "Room Hire " & [Client Name] AS [Booking Type], [tbl RH Rm Req].[Room
ID], [tbl RH Rm Req].[Hire Date] AS [Date] INTO [Tbl Schedules]
FROM ([tbl Client Data] INNER JOIN [tbl RH Bookings] ON [tbl Client Data].[C
ID] = [tbl RH Bookings].[Client ID]) INNER JOIN [tbl RH Rm Req] ON [tbl RH
Bookings].RBID = [tbl RH Rm Req].RBID
WHERE ((([tbl RH Rm Req].[Hire Date]) Between [Forms]![Frm Main
SB]![StartDateEntry] And [Forms]![Frm Main SB]![EndDateEntry]))
ORDER BY [tbl RH Rm Req].[Hire Date];

Query 2 (Append to "tbl schedules" Query)
INSERT INTO [Tbl Schedules] ( [Booking Type], [Room ID], [Date] )
SELECT "Closed Course- " & [Name] & " - " & [version] & "-" & [Level] & " ("
& [Client Name] & ")" AS [Booking Type], [tbl RH Rm Req].[Room ID], [tbl RH
Rm Req].[Hire Date] AS [Date]
FROM [tbl Training Available] INNER JOIN (([tbl TB Schedule] INNER JOIN [tbl
RH Rm Req] ON [tbl TB Schedule].TSID = [tbl RH Rm Req].TSID) INNER JOIN ([tbl
Client Data] INNER JOIN [tbl TB Bookings] ON [tbl Client Data].[C ID] = [tbl
TB Bookings].[Client ID]) ON [tbl TB Schedule].TSID = [tbl TB Bookings].TSID)
ON [tbl Training Available].TAID = [tbl TB Schedule].TAID
WHERE ((([tbl RH Rm Req].[Hire Date]) Between [Forms]![Frm Main
SB]![StartDateEntry] And [Forms]![Frm Main SB]![EndDateEntry]) AND (([tbl TB
Schedule].[Open Course?])=No) AND (([tbl TB Schedule].[Closed Course])=Yes))
ORDER BY [tbl RH Rm Req].[Hire Date];

Query 3(Append to "tbl schedules" Query)
INSERT INTO [Tbl Schedules] ( [Booking Type], [Room ID], [Date] )
SELECT "Open Schedule- " & [Name] & " - " & [version] & " " & [Level] AS
[Booking Type], [tbl RH Rm Req].[Room ID], [tbl RH Rm Req].[Hire Date] AS
[Date]
FROM [tbl Training Available] INNER JOIN ([tbl TB Schedule] INNER JOIN [tbl
RH Rm Req] ON [tbl TB Schedule].TSID = [tbl RH Rm Req].TSID) ON [tbl Training
Available].TAID = [tbl TB Schedule].TAID
WHERE ((([tbl RH Rm Req].[Hire Date]) Between [Forms]![Frm Main
SB]![StartDateEntry] And [Forms]![Frm Main SB]![EndDateEntry]) AND (([tbl TB
Schedule].[Open Course?])=Yes) AND (([tbl TB Schedule].[Closed Course])=No))
ORDER BY [tbl RH Rm Req].[Hire Date];

Cross Tab Query
TRANSFORM First([Tbl Schedules].[Booking Type]) AS [FirstOfBooking Type]
SELECT [tbl Room Data].[Room name]
FROM [Tbl Schedules] RIGHT JOIN [tbl Room Data] ON [Tbl Schedules].[Room ID]
= [tbl Room Data].[Room ID]
GROUP BY [tbl Room Data].[Room name]
ORDER BY [tbl Room Data].[Room name], [Tbl Schedules].Date
PIVOT [Tbl Schedules].Date;

 

[Michel Walsh]

Hi,


You can force a crosstab to create a list of fields with the Column Headings
crosstab query property, or, in SQL view, with an IN list after the PIVOT
expression:

TRANSFORM ...
PIVOT someExpression IN("1", "2", "3", "4", "5", ... , "31")



Note that if someExpression does not generate the value "1", a field of that
name will still be created, with a null under it for all its rows; if
someExpression generates a value not in the list, such as "32", then such
field will NOT be created. In other words, fields in the list will be
created, and only fields listed will be created.


Hoping it may help,
Vanderghast, Access MVP

Hiya

I have 3 queries pulling data from a few tables to make one new table.
This
table is then used in a Cross Tab query to look like a calendar.

Each of the 3 queries uses a date range from my switchboard
(StartDateEntry
and EndDateEntry)

The answers only show those dates that have data against them. I need
each
day between those dates to show so that when displayed in my cross tab
query
each day has a column.

I have managed to do this with the room names - note I am showing Room ID
in
each query then introduced the Room Data to show the room name in the
crosstab query and chaging the join.

Hope this makes sense.

Many thanks for your time

Kerry

Query 1 (Make Table "tbl schedules" Query)
SELECT "Room Hire " & [Client Name] AS [Booking Type], [tbl RH Rm
Req].[Room
ID], [tbl RH Rm Req].[Hire Date] AS [Date] INTO [Tbl Schedules]
FROM ([tbl Client Data] INNER JOIN [tbl RH Bookings] ON [tbl Client
Data].[C
ID] = [tbl RH Bookings].[Client ID]) INNER JOIN [tbl RH Rm Req] ON [tbl RH
Bookings].RBID = [tbl RH Rm Req].RBID
WHERE ((([tbl RH Rm Req].[Hire Date]) Between [Forms]![Frm Main
SB]![StartDateEntry] And [Forms]![Frm Main SB]![EndDateEntry]))
ORDER BY [tbl RH Rm Req].[Hire Date];

Query 2 (Append to "tbl schedules" Query)
INSERT INTO [Tbl Schedules] ( [Booking Type], [Room ID], [Date] )
SELECT "Closed Course- " & [Name] & " - " & [version] & "-" & [Level] & "
("
& [Client Name] & ")" AS [Booking Type], [tbl RH Rm Req].[Room ID], [tbl
RH
Rm Req].[Hire Date] AS [Date]
FROM [tbl Training Available] INNER JOIN (([tbl TB Schedule] INNER JOIN
[tbl
RH Rm Req] ON [tbl TB Schedule].TSID = [tbl RH Rm Req].TSID) INNER JOIN
([tbl
Client Data] INNER JOIN [tbl TB Bookings] ON [tbl Client Data].[C ID] =
[tbl
TB Bookings].[Client ID]) ON [tbl TB Schedule].TSID = [tbl TB
Bookings].TSID)
ON [tbl Training Available].TAID = [tbl TB Schedule].TAID
WHERE ((([tbl RH Rm Req].[Hire Date]) Between [Forms]![Frm Main
SB]![StartDateEntry] And [Forms]![Frm Main SB]![EndDateEntry]) AND (([tbl
TB
Schedule].[Open Course?])=No) AND (([tbl TB Schedule].[Closed
Course])=Yes))
ORDER BY [tbl RH Rm Req].[Hire Date];

Query 3(Append to "tbl schedules" Query)
INSERT INTO [Tbl Schedules] ( [Booking Type], [Room ID], [Date] )
SELECT "Open Schedule- " & [Name] & " - " & [version] & " " & [Level] AS
[Booking Type], [tbl RH Rm Req].[Room ID], [tbl RH Rm Req].[Hire Date] AS
[Date]
FROM [tbl Training Available] INNER JOIN ([tbl TB Schedule] INNER JOIN
[tbl
RH Rm Req] ON [tbl TB Schedule].TSID = [tbl RH Rm Req].TSID) ON [tbl
Training
Available].TAID = [tbl TB Schedule].TAID
WHERE ((([tbl RH Rm Req].[Hire Date]) Between [Forms]![Frm Main
SB]![StartDateEntry] And [Forms]![Frm Main SB]![EndDateEntry]) AND (([tbl
TB
Schedule].[Open Course?])=Yes) AND (([tbl TB Schedule].[Closed
Course])=No))
ORDER BY [tbl RH Rm Req].[Hire Date];

Cross Tab Query
TRANSFORM First([Tbl Schedules].[Booking Type]) AS [FirstOfBooking Type]
SELECT [tbl Room Data].[Room name]
FROM [Tbl Schedules] RIGHT JOIN [tbl Room Data] ON [Tbl Schedules].[Room
ID]
= [tbl Room Data].[Room ID]
GROUP BY [tbl Room Data].[Room name]
ORDER BY [tbl Room Data].[Room name], [Tbl Schedules].Date
PIVOT [Tbl Schedules].Date;