Multiple tables to be updated on adding a new record

[priyanka]

I have 3 tables in a hotel database:

Booking_Details
Booking_ID (PK)
Cust_ID (FK) with customer table
Room_ID (FK) to Room_Master containing Room_ID, Room_Category
No_Of_Rooms 
CheckInDate
CheckOutdate
...
RoomStatus
RoomNo (PK)
Room_ID
Avaliable [yes/no]

Booked_Rooms
Booking_ID 
Rooms_Booked

Room_Master
Room_ID (PK)
Room_Category
Room_Rate

I want to create a form for booking the rooms for a customer.
How I can create a form based on multiple tables, so that when record is inserted, it should update three tables Booking_Details, RoomStatus, Booked_Rooms.

Kindly suggest me a solution for this in MS Access 2010.

[JoeM]

Link your three tables together in a query, returning all the fields that you want.
Then use this query as the Control Source of your Form.
Then updating information on the Form should be reflected in the underlying Tables.

[priyanka]

I have 3 tables in a hotel database:

Booking_Details
Booking_ID (PK)
Cust_ID (FK) with customer table
Room_ID (FK) to Room_Master containing Room_ID, Room_Category
No_Of_Rooms 
CheckInDate
CheckOutdate
...
RoomStatus
RoomNo (PK)
Room_ID
Avaliable [yes/no]
Booked_Rooms
Booking_ID 
Rooms_Booked

Room_Master
Room_ID (PK)
Room_Category
Room_Rate

I want to create a form for booking the rooms for a customer.
How I can create a form based on multiple tables, so that when record is inserted, it should update three tables Booking_Details, RoomStatus, Booked_Rooms.

Kindly suggest me a solution for this in MS Access 2010.

Thanks for the reply.
When I created a Query and added in the Record Source (Data tab), nothing is displayed on the form.
I read on setting a query in Record source, it cannot edit data in the form.
What I had thought Booking form to be designed should have:

Cust_ID ICombo box) displaying Cust_ID from Customer_Master
First Name (text boxes)
Last Name (These will display details of the customer with selected Cust_ID.
Room Type (Combo Box) display category form Room_Master at back it will take Room_ID as value.
Number of Rooms Required (textbox)
CheckInDate:
CheckOutDate:
All Room Nos under Room Type should be displayed in Available Rooms (how I should display, no idea).
Also Available which is a Yes/No field.
Finally, data should be saved in the tables.

Is it possible with the structure given above.
If there is any link explaining this type of form design, kindly mention.

Thank you in advance.

[JoeM]

As long as you create an "editable" query, you should be able to use the Form for Data Entry.
By default, if you are using the "standard" Inner Joins in your query, the query will only return records where there are matching key fields records in each of the three tables. So if you do not have any records currently like that, you won't see any existing records in your query (and hence on your Form). But you should still be able to add new records using your Form (provided the query is editable).

If you have some one-to-many relationships, you may need to create Subforms instead, and link those to your Main Forms.

If you need more assistance doing this, it would be very helpful if we could see your query (paste your SQL code here), and some samples of the data in your tables.

[priyanka]

​Can u send me ur email id for sending table structure.

[JoeM]

Just post them to the site. You even have the option of uploading your file/database, though I won't be able to look at it until much later, as I cannot download files from my present location (though others may take a look at it in the meantime).

[priyanka]

Customer_Master
Cust_ID	FirstName	LastName
C001	John	Smith
C002	Jill	Paul
C003	Janny	Disco

Room_Master
Room_ID	Room_Category	Room_Rate
R001	Delux	2000
R002	Executive	3000

Room_Status
Room_No	Room_ID	Avaliable
101	R001	Yes
102	R002	Yes
103	R002	No
105	R001	Yes
107	R002	No
109	R001	No
200	R002	No

Booking_Details
Booking_Details	Cust_ID	Room_ID	No_Of_Rooms	Checkin_Date	Checkout_Date
B001	C001	R001	2	13/09/2012	20/09/2012
B002	C002	R002	1	10/09/2012	12/09/2012

Booked_Rooms
Booking_ID	Booked_Rooms
B001	101
B001	105
B002	102

Room_Master
Room_ID	Room_Category	Room_Rate
R001	Delux	2000
R002	Executive	3000

Room_Status
Room_No	Room_ID	Avaliable
101	R001	Yes

[JoeM]

Can you also post the SQL code of the query you wrote to join your tables together so we can analayze it?
Just switch your Query to SQL View and copy and paste that code here.

[priyanka]

Can you also post the SQL code of the query you wrote to join your tables together so we can analayze it?
Just switch your Query to SQL View and copy and paste that code here.

This is the query which I generated, but nothing comes on form after adding it in the record source.

SELECT Booking_Details.Booking_Details, Booking_Details.Cust_ID, Booking_Details.Room_ID, Booking_Details.No_Of_Rooms, Room_Status.Room_No, Room_Status.Avaliable, Booked_Rooms.Booked_Rooms
FROM Room_Status, Booking_Details INNER JOIN Booked_Rooms ON Booking_Details.Booking_Details = Booked_Rooms.Booking_ID;

[JoeM]

The key is this part here:

ON Booking_Details.Booking_Details = Booked_Rooms.Booking_ID

Looking at your example, it looks like you should have matches (B001 and B002).
If you look at your actual data, do you actually have matches between these two tables like this?
If so, it should definitely return records in your query. If not, it won't.

If it appears that you do have matches, but the query is returning nothing, it could be indicative that your values really don't match. Maybe there is an extra space or special character in one of them, so they don't match exactly. I would recommend using the LEN function on both the Booking_Details.Booking_Details and Booked_Rooms.Booking_ID fields to confirm that there aren't any extra characters or spaces causing issues.

For example, if your Booking_ID is "B001" and LEN([Booking_ID]) returns a value of 5, you know you have some extra characters in there.

[priyanka]

The key is this part here:

Looking at your example, it looks like you should have matches (B001 and B002).
If you look at your actual data, do you actually have matches between these two tables like this?
If so, it should definitely return records in your query. If not, it won't.

If it appears that you do have matches, but the query is returning nothing, it could be indicative that your values really don't match. Maybe there is an extra space or special character in one of them, so they don't match exactly. I would recommend using the LEN function on both the Booking_Details.Booking_Details and Booked_Rooms.Booking_ID fields to confirm that there aren't any extra characters or spaces causing issues.

For example, if your Booking_ID is "B001" and LEN([Booking_ID]) returns a value of 5, you know you have some extra characters in there.

Thanks for the reply.

Is there any other solution where I can drag fields from the required tables. As my first table first saves the customer data in the Customer_Master table.
Now, for this new Customer, when user clicks Booking, opens a new form which will display customer id from the last form in the first field.
Then, displays Room_Category from Room_Master in a combo box.
On selection, displays the Room_No for the corresponding Room_ID and Availability for selection.
Then, CheckIn date and check out dates...
When user clicks Add, the data from these fields in the Booking_details table and Room_Status table.

[JoeM]

Let's not put the horse the cart here.
Based on what I said in my last post, if you truly have matching records in those two tables, but that query is NOT returning any data, then you have a data issue. If you have a data issue, unless you correct it, it doesn't matter what you try, you are going to have problems. So let's focus on fixing that part first.

Can you please address/respond to the comments/concerns I made in the previous post?

[priyanka]

SELECT Customer_Master.Cust_ID, Room_Master.Room_Category, Room_Master.Room_Rate, Booking_Details.Checkin_Date, Booking_Details.Checkout_Date, Room_Status.Room_No, Room_Status.Avaliable
FROM ((Room_Master INNER JOIN (Customer_Master INNER JOIN Booking_Details ON Customer_Master.Cust_ID=Booking_Details.Cust_ID) ON Room_Master.Room_ID=Booking_Details.Room_ID) INNER JOIN Room_Status ON Room_Master.Room_ID=Room_Status.Room_ID) INNER JOIN Booked_Rooms ON Booking_Details.Booking_ID=Booked_Rooms.Booking_ID ;

This is the new query which I generated.
I understood why the form was empty, as I did not dragged the fields on the form.
Data from Booking_Details is displayed on the form when I am navigating.
I tried to add a button for new record, then it says cannot go to specific record.

[JoeM]

It looks like you are dealing with an "unupdateable" query, which is why you cannot add new records.
Take a look at this write-up here, which explains conditions that prevent a query from being updateable: http://office.microsoft.com/en-us/ac....aspx#BMcannot

[priyanka]

It looks like you are dealing with an "unupdateable" query, which is why you cannot add new records.
Take a look at this write-up here, which explains conditions that prevent a query from being updateable: http://office.microsoft.com/en-us/ac....aspx#BMcannot

I read this article and followed.
But with no results.
Such as small database, but not finding any appropriate solution to solve this.
Will macros be used to design such type of form.
If possible can u show me a form designed with a Query based on the tables provided.
As, I am not getting any site, which show how to design such forms.