Spreadsheet Style Report

[limpetsfolly]

I’m trying to produce a room booking system where I can produce a
“spreadsheet” type report showing booked and free rooms on a weekly
basis:

[image: http://www.redpoppy.co.uk/nmoffice_bookings.gif]

I have a table for Rooms, Bookings and Dates:
Rooms contains RoomID, RoomDesc
Bookings contains Client ID, Client Name, Date From, Date To
Dates contains all dates for the year by day

I’ve tried using a cross-tab query, but this only gives me data where
there are bookings for a room in a particular week. How can I produce a
report in the format shown above, regardless of whether there are any
bookings for a particular week – ie: still show the room and the W/C
even if there are no bookings for those dates/rooms?

 

[Evi]

How did you manage to make a crosstab query from this since there isn't a
'Values' bit in the middle, just the client's names?

If you can tell me that then the date bit is comparatively easy. Create one
query that has all the details you want including the dates. It won't have
enough records though.

Make a second query. Add the first query to the grid. Add your Date table.
Break one of the joins so it is only joined by DateFrom. Double click on the
join and in the options that appear, make it a join that Shows all the Data
from the Dates table.

Put the Date from your Dates table into the grid. You may get grumbles that
there are ambiguous matches. If this happens, have a look in the Sql view
and where it says BookDate or whatever you call the Date field type
TblDate!BookDate or QryBookings!BookDate depending on where that field comes
from. You will have to do this in any formulae in the table too eg MyDay =
Day([TblDate]![BookDate]).

Now, I'm looking forward to your reply. I've just wrestled with this
monstrous multicolumn subreport to do something similar and it would be
bliss to use a crosstab.
Evi

 

[Duane Hookom]

You can set the criteria under the Dates.DateOf field to:
Between Bookings![Date From] And Bookings![Date To]
This will create a returned value for each date of the booking.

As Evi states, you can modify a join to include all dates in the result.

There are crosstab and calendar report examples that might help at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
MS Access MVP

How did you manage to make a crosstab query from this since there isn't a
'Values' bit in the middle, just the client's names?

If you can tell me that then the date bit is comparatively easy. Create one
query that has all the details you want including the dates. It won't have
enough records though.

Make a second query. Add the first query to the grid. Add your Date table.
Break one of the joins so it is only joined by DateFrom. Double click on the
join and in the options that appear, make it a join that Shows all the Data
from the Dates table.

Put the Date from your Dates table into the grid. You may get grumbles that
there are ambiguous matches. If this happens, have a look in the Sql view
and where it says BookDate or whatever you call the Date field type
TblDate!BookDate or QryBookings!BookDate depending on where that field comes
from. You will have to do this in any formulae in the table too eg MyDay =
Day([TblDate]![BookDate]).

Now, I'm looking forward to your reply. I've just wrestled with this
monstrous multicolumn subreport to do something similar and it would be
bliss to use a crosstab.
Evi

I'm trying to produce a room booking system where I can produce a
"spreadsheet" type report showing booked and free rooms on a weekly
basis:

[image: http://www.redpoppy.co.uk/nmoffice_bookings.gif]

I have a table for Rooms, Bookings and Dates:
Rooms contains RoomID, RoomDesc
Bookings contains Client ID, Client Name, Date From, Date To
Dates contains all dates for the year by day

I've tried using a cross-tab query, but this only gives me data where
there are bookings for a room in a particular week. How can I produce a
report in the format shown above, regardless of whether there are any
bookings for a particular week - ie: still show the room and the W/C
even if there are no bookings for those dates/rooms?