Multiple tables to be updated on adding a new record

[JoeM]

Yes, having a table for the Room Numbers is necessary, but I repeat, the current availability of a room should not be stored at the table level. It should be able to be determined off of the Booking_Details tables and related tables (I am curious as to how you update this anyhow, i.e. how you change it from not available to available as days go by).

[priyanka]

Hi, this is my new database design. If have made modifications in the structure.

Customer_Master
Cust_ID (PK), FirstName, LastNAme...

Room_Master
Room_No (PK), Room_Category (FK), status

Room_Category
Room_Category (PK), Rate

Booking_Master
Booking_ID (PK), TotalAmt

Booking_Transaction
Booking_ID (FK), Cust_ID (FK), Room_No (FK), CheckinDate, CheckOutDate.

Just check if design is corrrect.
Also, How i will design a form which will allow me to book more than one rooms under single Booking_ID

[JoeM]

It looks like you still have Status under "Room_Master". And what is the TotalAmt under the Booking_Master table? It looks like it may be another calculated field, which probably shouldn't be stored at the table level.

[priyanka]

It looks like you still have Status under "Room_Master". And what is the TotalAmt under the Booking_Master table? It looks like it may be another calculated field, which probably shouldn't be stored at the table level.

Ok, But status has to be maintained somewhere, as per my requirement only those rooms can be booked whose status is "Not Occupied/Not Booked".
As my design is not based on dates, so status is the only way to check the room status.

Allow me to present the requirement:
It is a simple database design, which is prepared for demonstration to the students.
So, design need to be simple to understand and How a form can be used to do booking for rooms, with multiple underlying tables.
That's why design shows only customers and booking.
Idea is, the manager should be able to book more than one room under single booking id.
If I use form and subform concept, only two tables were getting updated. Third table on toss.
Or this type of requirement can be done using customized form with VBA code.

Thus, still I am not getting whether design is wrong or requirement to update three tables in Access is wrong.

Kindly suggest me as per my requirement, as with your replies I am not getting any constructive result.

[JoeM]

Ok, But status has to be maintained somewhere, as per my requirement only those rooms can be booked whose status is "Not Occupied/Not Booked".

As I have mentioned more than once, you track it in a query, you do not store it anywhere in a table. Your database has design issues, and unless those are addressed you are going to encounter these type of problems.

I think the root of the problem is not having a complete understanding on how relational databases should be designed, particularly as it pertains to the rules of Normalization. So you are not understanding what I am telling you, or why. These concepts are essential to know, to understand, and to implement. It is very dangerous to dive right into Access and try to develop an involved database if you do not have a complete understanding of these concepts.

I experienced this myself on the first Access database project I ever attempted. I spent 6 months spinning my wheels and hitting dead-end after dead-end. Then I bought some books on Relational Databases and Normalization and after working through them I saw that I needed to re-design my whole database from scratch. It was lot of work to do that, but I did and it works great now (they are still using it 10 years later!).

Unfortunately, there is really no way for me to teach you all of that in the context of a single thread (and that is not really what these forums are designed for). Unless Excel, Access has a large learning curve and is not always that intuitive. I would advise you to either try to educate yourself on these important concepts (through books, classes, articles, and tutorials) so you have a better understanding on how your database should be constructed, or maybe looking for a Consultant to design the database for you.

I am afraid that is all the advise I can really give you at this point.