Multiple tables to be updated on adding a new record

[JoeM]

The problem is not with your Form, it is with your Query - you are creating a query that is not updatebale. If try adding a record in the query, you will have the same issue.
I will try to recreate your scenario on my side later today (may not be until tonight, this are pretty crazy at work today). However, look for any many-to-many relationships in your query. If you have any of those, that definitely will cause problems.

Also, can you tell me the primary keys in each of your tables (every table should have one), and if the fields you are joining your tables on are indexed, and whether or not they are unique?

[priyanka]

This is the database structure of my database.....
Customer
Cust_ID (PK), first_name, last_name,..

Room_Master
Room_ID (PK), Room_Category, Room_Rate

Room_Status
Room_No (PK), Room_ID (FK), Available (yes/no data type)

Booking_Details
Booking_ID (PK), Cust_ID (FK), Room_ID (FK), No_of_Rooms_Required, Rate, payment

Booked_Rooms
Booking_ID (FK), Room_No

No tables are not indexed and there is no unique constraint specified in any table. Simply the tables are joined based on their primary keys.

[JoeM]

I have been messing around with this, and I fear you might be trying to do too much in a single query/form that involves too many other object. One of the problems may be a potential "many-to one-to many" relationship when you link Booking_Details to Room_Master and then to Room_Status (that situation was described in that link I referenced a few posts back: http://office.microsoft.com/en-us/ac....aspx#BMcannot).

You may also have some design issues also. I am not sure what the Room_Status table is supposed to be showing, and how it relates to the other tables.
How are all your different IDs being generated (Room ID, Customer ID, Booking_ID)?

[priyanka]

I have been messing around with this, and I fear you might be trying to do too much in a single query/form that involves too many other object. One of the problems may be a potential "many-to one-to many" relationship when you link Booking_Details to Room_Master and then to Room_Status (that situation was described in that link I referenced a few posts back: http://office.microsoft.com/en-us/ac....aspx#BMcannot).

You may also have some design issues also. I am not sure what the Room_Status table is supposed to be showing, and how it relates to the other tables.
How are all your different IDs being generated (Room ID, Customer ID, Booking_ID)?

All the PK columns are provided by the user.
For example, Customer Form which adds customer details to Customer is given by the user on the form. This form works as it is based on single table.
For Room_ID, currently no form is designed, they are updated directly in the table,

Table

Room_ID	Room_Category	Room_Rate
R001	Delux	3000
R002	Maharaja	8000
R003	Executive	9000

Room_No	Room_ID	Available
101	R001	No
102	R001	No
103	R001	No
106	R002	Yes
107	R002	Yes
109	R002	Yes
110	R003	Yes
111	R003	Yes
112	R003	Yes

Booking_Details form should update Booking_Details, Room_Status, and Booked_Rooms table.

[JoeM]

Is this for a single-time booking, or a database to be used for multiple booking dates?
Because if it is to be re-used, I see a problem with your Room_Status table, as it has no Booking_ID or date associated with each record.

[priyanka]

Is this for a single-time booking, or a database to be used for multiple booking dates?
Because if it is to be re-used, I see a problem with your Room_Status table, as it has no Booking_ID or date associated with each record.

I did not understood the highlighted line.
The Booked_Rooms table contains the Room_No and Booking_ID.
So, this will help me to find rooms booked on the Booking_ID.

[JoeM]

The Room_Status table seems to show Room availability, but as of when (what date)?
Let's say that you have a Room_No 101. It is booked on 14/09/2012, but is available on 15/9/2012.
How would that be represented in the Room_Status table, if there is no date or Booking_ID in that table?

[priyanka]

The Room_Status table seems to show Room availability, but as of when (what date)?
Let's say that you have a Room_No 101. It is booked on 14/09/2012, but is available on 15/9/2012. - not told.
How would that be represented in the Room_Status table, if there is no date or Booking_ID in that table?

Correct, but as per my table design
when a user is booking a room, if occupied, then should not allow to book.
only for available room, a customer can book.
Example, 101 and 102 in R001 are availability is No, means not available.


Rest, if information is required for which Booking_ID, these rooms are available, then Booked_Rooms table will provided information.

[priyanka]

Correct, but as per my table design
when a user is booking a room, if occupied, then should not allow to book.
only for available room, a customer can book.
Example, 101 and 102 in R001 are availability is No, means not available.


Rest, if information is required for which Booking_ID, these rooms are available, then Booked_Rooms table will provided information.

Actually, this is part of semester project, so many functionalists are not given.
only customer form and a booking detail form need to be designed
for a hotel database.

[JoeM]

I am not understanding your use of the "Room_No" and "Room_ID" fields. Can you explain the difference?
For a specific room at the location, are these values static, or do they change on different dates?

[JoeM]

Continuing with my previous point, maybe an example would help clarify things.
Many hotels have a single "Honeymoon" suite, a specific room in the hotel with a hot tub, etc.
Let's say that the Honeymoon Suite is booked for 14/9/2012, but not for 15/9/2012.
How would that be shown in your database (what would those records look like in each of your tables)?

[priyanka]

I am not understanding your use of the "Room_No" and "Room_ID" fields. Can you explain the difference?
For a specific room at the location, are these values static, or do they change on different dates?

Compare this with categories aND PRODUCTS
Room_ID identifies a category means Room_Category
under that category room_nos are given
Room_nos are static, only there availability will change.
example, if 101 is occupied and on table level if i remove check mark means available
then this room_no should be given for booking

[priyanka]

Compare this with categories aND PRODUCTS
Room_ID identifies a category means Room_Category
under that category room_nos are given
Room_nos are static, only there availability will change.
example, if 101 is occupied and on table level if i remove check mark means available
then this room_no should be given for booking

Continued..
Date is not takeen into consideration.
plz do not confuse with dates
on a current booking date, if room_no 101 is marked as occupied, means not available.

[JoeM]

I am afraid I am not understanding your model.
Based on what you are saying, and the lay out of your Room_Status table, it seems to me that each room can only be booked once, ever (i.e. once a roomed, it can never be booked again).

The only thing I can think of that would even remotely make sense to me is if your Room_Status table just means current status, i.e. you are toggling the availability on and off. If that is the case, then I think that you have a database design issue, as that violates the rules of database normalization. Whether a room is currently available or not can be derived from your other tables. Any field that is a calculation or can be derived from other table fields should never be stored at the table level (unless you are creating some sort of history table to see what the value was at a given point in time). Those values (room availability) should be able to be derived in a query using the other tables.

For more on database design and normalization, here is a good article: http://www.deeptraining.com/litwin/d...aseDesign.aspx

[priyanka]

Within a Room_Category, say 'Deluxe', in that three rooms 101, 102, and 103.
Don't we require a separate table to store room_nos. Available column in the table gives the current status of the room.
As, it is a Yes/No column, so checked means selected.
Ya it shows current status of room nos.
but, through form, I need to book for rooms that are available (means not selected in the table). I used sub forms for that, i update some room_nos but it shows all the details for specific Room_ID (which is a combo box).
I can even deselect the already booked rooms.