Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Unanswered: Creating a booking form?

    I'm designing a simple B&B booking form for my as level ict coursework. It has to include room and breakfast bookings

    At the moment the tables I have are as follows:

    tblCustomer: Customer ID, Title, Surname, Forename, D.O.B., Tel. No., Add. 1, Add. 2, Add. 3, Postcode

    tblRooms: Room ID, Price, Occupancy

    tblBreakfast: Breakfast ID, Breakfast, Price

    tblBookings: Booking ID, Customer ID, Room ID, Breakfast ID, Date of Arrival, Date of Departure

    Major problem = I need to have queries that allow me to search for dates in-between the arrival and departure dates, at the moment I cant do this!

    Any ideas?
    Last edited by stardog32; 01-27-05 at 18:15.

  2. #2
    Join Date
    Nov 2003
    Posts
    167
    Stardog - if you're wanting to build a list of dates between arrival & departure:

    1/4/2004
    1/5/2004
    etc etc


    You need to build a pivot table which will allow you to loop through a counter. To build a pivot table, build a table with a single integer column and enter 10 rows with values 0 through 9. Assuming the name of the table is [pivot], run the following query:

    start here ----------------------------------
    SELECT a.i & b.i & c.i & d.i AS x
    FROM [pivot] AS a, [pivot] AS b, [pivot] AS c, [pivot] AS d
    order by a.i & b.i & c.i & d.i;

    This establishes your counter. Now if you add dates and select only those between arrival and departure (from table [dates]) you end up with something like this:

    SELECT a.i & b.i & c.i & d.i AS x, format(DateAdd("d",a.i & b.i & c.i & d.i,(SELECT First(dates.arrivaldate) FROM dates)),"mm/dd/yyyy") as [GapDate]

    FROM [pivot] AS a, [pivot] AS b, [pivot] AS c, [pivot] AS d, dates

    WHERE format(DateAdd("d",a.i & b.i & c.i & d.i,(SELECT First(dates.arrivaldate) FROM dates)),"mm/dd/yyyy") Between [dates].[arrivaldate] and [dates].[departuredate]

    order by a.i & b.i & c.i & d.i;

    end here -----------------------------------------------

    Of course, this returns the dates between ALL of your arrivals and departures, which I'm not sure is very useful.

    If you'd like some hints on pivot tables (which are excruciatingly useful), the best book I've seen is The Transact-SQL Cookbook. I forget which publishing house.

    ps - I chose 5 digits for the counter so your db would be good for 27 years.

  3. #3
    Join Date
    Jan 2005
    Posts
    2

    Thumbs up

    ThankU!!!!

  4. #4
    Join Date
    Nov 2003
    Posts
    167
    You're very welcome.

    By the way - I really do recommend the Transact SQL cookbook (Gennick/Spetic - Oreilly Press). I'm more of a PL/SQL person, but the strategies it goes through are good for all SQL flavors, including JET. The first chapters on pivot tables (probably the most indispensable and overlooked tool in a data analyst's arsenal) alone is worth the purchase price.

    There's not a day goes by that I don't use some strategy I learned in that book.

Posting Permissions

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