Auto input data in a field in a form using relationships

[18khattaba]

I have two tables and one is for customers and the other is for prices of hotels. For every location, there is one hotel. In the customers form, I wold like it to auto select the hotel when the user selects a location. I would like to do this using relationships.

 

[bob fitz]

Cross posted: https://www.accessforums.net/showthread.php?t=87361

Please read: https://excelguru.ca/a-message-to-forum-cross-posters/

 

[arnelgp]

create a "junction" table between locations and hotels.
if there is 1-1 relation (one hotel for each location) then you only
need 1 table.

 

[jdraw]

18khattaba
Are you trying to support some sort of business with this proposed setup?
What are the "rules of that business"?
What Locations are involved?
Who are the current/proposed Customers?
What is your activity with respect to those Customers?
One or more Hotel(s) per Location?
Prices by Room by Occupancy (Number persons * Days) or what?

You need to know the business rules in order to establish and confirm your relationships.

 

[Gasman]

Answered on the other site.

 

[18khattaba]

I have created 3 tables linked in a relationship, then I created a query to put all that data in a single table. The tables are called BookingTBL, Customers and PriceOfHotels. I have used the query to make a form the price of hotels table should not be updated but only viewed to retrieve information from it. How can I get the form to add information based on the product and what the user inputs.

So if the user selects they want to go to Belfast it should automatically select the hotel "The Belfast". Also, how can I do this in a way where the user can see what hotel and the price of it in a field that is locked.

I can't do a combo box as the table looks like this and so it will show like 4 of each location which doesn't look nice also, the user has to input which location and the type of hotel which could cause a problem when selecting.

This is the price of hotels table:

 

[18khattaba]

I have posted another thread that explains it better.

 

[theDBguy]

I have posted another thread that explains it better.

Hi. Welcome to AWF!

No need to do that. To avoid any confusion, I have merged your two threads on the same topic.

 

[jdraw]

Is this a school assignment? If not, could you describe the requirement in business terms?
Showing us a table and some values without a business context isn't helpful to get a focused response.
You may want to review some database concepts : Normalization, Relationships, Business Rules

Please see this Hotel Bookings Reservation page (data model and rules) by Gina Whipp

 

[Gasman]

I have created 3 tables linked in a relationship, then I created a query to put all that data in a single table. The tables are called BookingTBL, Customers and PriceOfHotels. I have used the query to make a form the price of hotels table should not be updated but only viewed to retrieve information from it. How can I get the form to add information based on the product and what the user inputs.

So if the user selects they want to go to Belfast it should automatically select the hotel "The Belfast". Also, how can I do this in a way where the user can see what hotel and the price of it in a field that is locked.

I can't do a combo box as the table looks like this and so it will show like 4 of each location which doesn't look nice also, the user has to input which location and the type of hotel which could cause a problem when selecting.

This is the price of hotels table:
View attachment 105649

Never heard of Distinct or Group By?
Your db does not appear to be normalized, hence all your problems.

 

[arnelgp]

see this demo if it will be of help to you.

 

[18khattaba]

see this demo if it will be of help to you.

Thank You. This is very helpful and I now understand on how to do it but I would like to know what these tables are, where did they come from and what do they do to the actual database.

 

[Pat Hartman]

They are Access system tables that YOU do not update directly. It is a bug with Access that they show in the relationships window at all. You can get rid of them by deleting all three tables from the window. This does not delete the tables from the database. It simply removes them from the relationships window. If you press the show all relationships button, they will pop back on again.

 

[18khattaba]

They are Access system tables that YOU do not update directly. It is a bug with Access that they show in the relationships window at all. You can get rid of them by deleting all three tables from the window. This does not delete the tables from the database. It simply removes them from the relationships window. If you press the show all relationships button, they will pop back on again.

Thank you I now have a better understanding of it