Automatically create the same information in 2 different tables

[CecileT]

New to Access and building my first database, I am hoping I can get some help.



I create 1 record for each hotel in Table #1.
In table #2, I am adding details for the same hotels that I would need to keep separated from info in table #1.

I created a relationship between the 2 tables with the Hotel name as the common key.

My question is, when creating a record for a hotel in table #1, can it automatically create a record for the same hotel in table #2.

Table 1 Hotel Details	Table 2 Hotel Input Info
Hotel ABC	Hotel ABC
Sales Manager Name	Contract Received
EMail	Comments

If this automation is not possible, this means that the record will need to be manually created in the 2 tables and will need to be worded exactly the same. There is a chance that:
-the user forgets to create the record for the hotel in table #2
and/or
- the name of the hotel does not identically match between the 2 tables

[ranman256]

run an append query to take the existing record and add to the target table.

[Gicu]

Cecile,

I suggest you do not use the hotel name as the joining field; the hotel can change its name, get purchased by another chain, etc. Add a new field to the first table called HotelID, make it an autonumber and the primary key of that table. Add a similar field in the second table but set the data type to number (long integer). That will be your foreign key and this pair will be used to create the join between the tables.
Using a main form bound to table 1 with a subform bound to table 2 with the master/child fields set both to HotelID will ensure that anytime you add any new info into the subform it will create the new record with the corresponding HotelID from the main form.

Cheers,
Vlad

[orange]

CecileT,
You have asked a specific question and have been given 2 specific responses.
I'd like to know more about your proposed database. What other things are involved?
And what process(es) act on these things?
Customer, Booking, Reservations, Invoices, Rooms...... It isn't often that 2 tables constitute a working database.

You may get some ideas of the scope, the things possibly involved and relationships between those things from these links.
http://www.databaseanswers.org/data_...tels/index.htm ----a data model
http://www.databaseanswers.org/data_...tels/facts.htm ----the facts initiating the model

Good luck with your project.

[CecileT]

Cecile,

I suggest you do not use the hotel name as the joining field; the hotel can change its name, get purchased by another chain, etc. Add a new field to the first table called HotelID, make it an autonumber and the primary key of that table. Add a similar field in the second table but set the data type to number (long integer). That will be your foreign key and this pair will be used to create the join between the tables.
Using a main form bound to table 1 with a subform bound to table 2 with the master/child fields set both to HotelID will ensure that anytime you add any new info into the subform it will create the new record with the corresponding HotelID from the main form.

Cheers,
Vlad

Thank you Vlad, creating the subform is one step closer to what I would like achieve. So now whenever I am entering a new record, it creates one in the second table, generating the same Hotel ID. What I would like to achieve at this point is not only to have the Hotel ID auto populated and matching, I would like that the Hotel Name also auto populate and match what I am entering for table 1. Does that make sense?

[CecileT]

CecileT,
You have asked a specific question and have been given 2 specific responses.
I'd like to know more about your proposed database. What other things are involved?
And what process(es) act on these things?
Customer, Booking, Reservations, Invoices, Rooms...... It isn't often that 2 tables constitute a working database.

You may get some ideas of the scope, the things possibly involved and relationships between those things from these links.
http://www.databaseanswers.org/data_...tels/index.htm ----a data model
http://www.databaseanswers.org/data_...tels/facts.htm ----the facts initiating the model

Good luck with your project.

Thank you orange.
My team is currently using a shared Excel table with 67 columns. Groups of columns are dedicated to specific departments: i.e. columns A to S need to be filled out by Contracting Department, columns T to AF need to be filled out by Data department.

The challenges with the shared Excel table right now are:

• It is difficult for users to identify the columns they need to fill out and are relevant to them
• Although Excel documents can be shared, we regularly have some issues with that feature
• The sorting of the document is long and cannot be done frequently

Additionally, a lot of information are spread across multiple documents, filters and formats which make cross referencing information difficult and overall does not allow smooth communication between departments.

I believe Access could help us tremendously but with no budget for training, I am ending up watching tutorials and figuring out the best options for the company with no support until now.
I hope this makes it a little clearer.

[Gicu]

Cecile,

You don't need the hotel name in the second table, that is the beauty of databases. You can bring it in from the first table by simply joining on the HotelID in a query . If you need to change the name you only do it in table1. For displaying purposes on a form you can use a combo box bound to the HotelID field and having the row source HotelID and HotelName from table1. If you set the column widths to 0";3" (so the HotelID is hidden) you will see the hotel name only (but it will store the corresponding ID). I know it might sound a bit complicated right now but if you read a bit on it you'll see how easy it truly is. From what you are describing Access is definitively the better tool for your application.

Cheers,
Vlad

[orange]

CecileT,

See the link to Database Planning and Design in my signature for some great training articles.
The tutorials from RogersAccessLibrary - mentioned in that link - are excellent for learning about database, tables and relationships. You will learn by working through 1 or 2 of the tutorials.
Good luck.