Hi Since discovering Access abou

[AccessAddict]

Hi

Since discovering Access about 6 months ago I'm addicted, and with the help of you guys have learnt so much about databases in general it's my most time consuming hobby (I know, I'm sad but there ya go lol).
I've successflly designed a few db's, but all have been flatfiles, which I know is not the way to go, so for my latest (and most ambitious!!) project, I want to start out correctly by establishing the relationships at table level and keep the db normalised, which is a whole new ball game for me!!
It's basically going to be a hotel booking system with tblCustomer related to tblRoomName (one to many) which in turn is related to tblRoomNumber (one to many ie room names Economy, Standard, Deluxe on the one side and room numbers E1, E2, E3, S1, S2, S3 etc on the many side). What I'm struggling to work out is how each room type/number would relate to a table containing days of the year so that the system could book a room and thereby flag that room type/number as unavailable and not be able to double book it. Presumably I would have one table containg all dates from 1/1 thru 31/12 and somehow relate it to the room number and however the room is to flagged as available or let?
If anyone has any thoughts they'd be very much appreciated before I lock myself away from the world for another 6 months or so!!

Thanks for your time

 

[fneily]

Wow, a reservation system. I know people who have played with Access for three years who can't do that. First, you definitely better learn how to normalize. You'll notice that most of the problems encountered by Access users are because they don't know how (or want) to normalize. You have only three tables so no big problem.

Since you're keeping it simple, how about a calendar where you click on a date and a list of available rooms pops up? Then you could type in the room number and customer name for that date(s) and it would be tracked? This is called an "Event Calendar" if you want to search for them on the net. If you would like, I could email you one I have, you can then see how it works and look at the coding to revise for your needs. It wouldn't take to many changes to make it work for you.
Send me your email if you want a copy. It was written in Access 2000.
fneily@hotmail.com

Neil

 

[danvlas]

"one table containg all dates from 1/1 thru 31/12 "

Not quite. Because February has 28 or 29 days, you will miss 29 Feb on leap years. Or, if you include it, you'll get an invalid date for 'normal' years. I struggled with this for some time and in the end came to a table of dates:

A table with all dates (year included) would be fine. 10 years = max 3653 records (piece of cake).

You will need a many-to-many relationship:
One room can be booked in many days over time
One day includes many room bookings.

So...create a bridge table between tblAllDates and tblRooms.
Create a unique index on RoomNo+DateBooked
Create the one to many relationships between tblAllDates and 'bridge'. Do the same for tblRooms and 'bridge'

Now, one particular room can be booked only once for a particular date.

This is just an idea...you have to think about the overlapping booking periods and many other things specific to booking systems.

Good luck





Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[AccessAddict]

Hi

Many thanks for taking the time to reply and for your suggestions. I had the vaguest notion that a many to many relationship was the way to go but on the advice of everything I've read so far I've been trying to avoid it like the plague LOL. Oh well, out with the Access Bible again, and mucho stocking up of strong coffee and sleepless nights trying to get to grips with a bridge table and associated VBA. Watch this space for some very stupid questions in the VERY near future <G>

Again, thanks for your help

Cheers

Alan

 

[JeremyNYC]

Alan,

Check out the Fundamentals of Relational Database Design article on my website. It's a terrific piece, written by one of the Gurus of Access development, Paul Litwin. It will help you get an understanding of how and why to normalize your data.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[AccessAddict]

Hi

Thanks for the pointer to Paul Litwins article - it's excellent. I think I'm finally getting to grips with normalising data now, unlike many-to-many relationships!!! So, stupid question no. 1:

I've tried to implement Daniels suggestions as per his above post as follows

tblRoomType with one field:
RoomName (Text) (PK)

tblRoomNo with two fields:
RoomNmbr (Text) (PK) Contains ACTUAL room numbers
RoomName (Text) (FK to tblRoomType)

tblAllDates with two fields:
DateID (Autonumber) (PK)
Date (Date/Time) Contains dates 1/1/03 thru 31/1/03 at the moment for testing purposes

tblBridge with two fields:
RoomNo (Text) (PK)
Date (Number) (PK)

tblRoomNo is related to tblBridge (1-M) using RoomNmbr field to RoomNo fields, and tblAllDates is related to tblBridge (1-M) using DateID and Date fields, thereby giving me a M-M relationship between tblRoomNo and tblAllDates ? If this is correct (which I guess it isn't) I can still add a room on the same date twice, so I know I've cocked something up somewhere along the way.

Can you see what I'm doing wrong?

Again, many thanks for your time and suggestions

Alan

 

[JeremyNYC]

Alan,

If you have both fields in tblBridge set as the PK you won't be able to add duplicate values. You probably missed on that.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[AccessAddict]

Hi Jeremy

Thanks for the reply. Both fields in tblBridge are set to:

PK
Indexed Yes(Duplicates OK)

as per Daniels suggestion in his above post. That's where I think I'm going wrong in that I'm misunderstanding what he's telling me (I know, I'm just too thick!!!)

If I set them to Yes(No duplicates) that obviously stops me from adding for example more that one reference to 1st January 2003, whereas I will need several references to each calender date as there are many different rooms that can be let per date, but I don't want to be able to add more than one instance of a particular room on a specific date. Hope that makes sense?

Cheers

Alan

 

[danvlas]

No...
You have set up two different indexes, which restrict data on each field separately.

What you need is to restrict the combination of two fields, therefore you have to create a 'concatenated' PK-ie a primary key on two fields.

Set both fields as not indexed.
Then go to View-Indexes
Index name: PrimaryKey (or whatever name you want).
Field Name: RoomNo
DateFld (list both fields, one below the other).

Click on the index name.
In the index properties, set Primary to Yes.
Save the table.
Now you will have the key on both fields.

Good luck





Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html