Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2004
    Posts
    18

    Unanswered: creating sort of grid

    Hi,

    I want to create something like this for a reservation-overview:

    Date: 01/01/2005

    Hours | Room1 | Room2 | Room3
    ____________________________
    08:00 | | |
    08:30 | x | |
    09:00 | x | x |
    09:30 | x | x | x
    10:00 | | | x
    10:30 | | x |
    11:00 | x | x |
    etc...
    23:00 | | x | x

    ..thus for a selected date, the reservations for every room should be showed in a grid. For example: Room1 is booked between 08:30 - 10:00 and between 11:00 - 11:30
    Does somebody have an idea how to do this?
    I have a table tblReservations with:
    ReservId, MemberId, RoomId, ReservDate, MeetingDate, StartTime, EndTime, blnCancelled

    Thank you for your help..
    deef

  2. #2
    Join Date
    Nov 2004
    Posts
    18
    unfortunately, the spaces in the example are disappeared.
    but I hope you guys got the meaning

    deef

  3. #3
    Join Date
    Dec 2004
    Posts
    41
    As long as you have the data, this will be easy to implement using a report, I don't see where the problem is.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    This is a fairly straightforward crosstab report. Google around for crosstab reports and you should get some good direction.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Not as easy as it looks I think. The record is a start time and end time so, as I see it, there are no records for the in between half hours. I see this as a two stage affair.
    - Create the intermediate records e.g. in a temporary table
    - perform the cross tab

    I suspect the first stage means going into code.

    Chris

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by howey
    Not as easy as it looks I think. The record is a start time and end time so, as I see it, there are no records for the in between half hours. I see this as a two stage affair.
    - Create the intermediate records e.g. in a temporary table
    - perform the cross tab

    I suspect the first stage means going into code.

    Chris
    Good catch, I didn't think of that.

    You may have to create a temporar (or permanent if you want) table with all of the half hour increments in a given day. Otherwise you may run into issues where the table is distorted because there are no reservations for a given timeblock.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Nov 2004
    Posts
    18
    Hey.. thanks for your posts!
    However I think I wasn't clear enough.. I don't want to create a report. The overview should be in a form because on basis of that overview I would be able to fill in new reservations. So I don't want to create a reports first and then go back to the reservation-form every time.
    Euhmzz.. about the hours: I have 2 combo's with Start- en EndTime filled with half-hours (e.g. 08:30 - 09:00 - 09:30 ....) so maybe this way I don't have to create that temporary table. Am I right???
    I am just a beginner at this and still have to learn very much.. but that's what I'm trying to do here
    Thank you guys for being so helpfull!

    deef
    PS. If somebody knows any good sites or articles about this issue, please post them here so (maybe, LOL) I don't have to ask a question here every time. And yes, I google around a lot, but since my internetconnection is so slow (temporarily, but...) it takes me hours to find something usefull... *sigh*

  8. #8
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    deef

    Whether you want to show the grid as a report of on a form is rather irrelevant. The issue here is that your base data structure is not quite the right shape to be displayed as you require.

    Unless I completely misunderstand you, my point is still valid. You need a way of filling in the missing hal hours so they can be displayed. Teddy's idea is good I think.

    Create a new table called tblTimeSlots with a single field called TimeSlot. The fill the table will all the time slots you are interested in.

    Next you need to create a cartesian join query. Here's my effort:

    Code:
    TRANSFORM Sum(IIf([TimeSlot]>=[StartTime] And [TimeSlot]<[EndTime],1,0)) AS Booked
    SELECT tblTimeSlots.TimeSlot
    FROM tblReservations, tblTimeSlots
    GROUP BY tblTimeSlots.TimeSlot
    PIVOT tblReservations.RoomID;
    This creates a view similar to what you want but with 1's and 0's instead of X's.

    Run it and see what you get. This can be easily incoporated into a form (as a subform) and adapted slightly if you want to see X's. So you can see the current situation and make a booking. The situation will then be updated. The big plus about using 1's and 0's in the above query is that you can use the iif statement to make them look like null's, X's and also another character to signify that a slot has been double booked i.e. where value>1.

    Chris

  9. #9
    Join Date
    Nov 2004
    Posts
    18
    Wonderful Chris, this is exactly what I'm looking for!
    Don't really know what "cartesian join queries" mean or if they eat animals but it works perfect! I'll google around on this item to learn more about it!

    Thank you so much for your help
    Have a nice day!
    deef

  10. #10
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Good to hear it works. I forgot to mention that the query is also a cross-tab query (as Teddy again suggested). The point being you want to see the roomID's as column headers not row headers. I just didn't want to take credit for Teddy's input.

    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •