Update a pk on left which is linked to other right tables

[BittenApple]

Hello all,
I have a table that contains two fields a primary key Hos_ID and hospital_name.

First approach:
I have to add more hospitals to my table. I thought, if have disable referential integrity temporarily between this table and every other tables that are related to this table on the right, and then delete all the data and keep the structure and then I can insert the new data, and then If I bring back the relation between tables while referential integrity is forced, this might take care of my case. Does this method updates all the tables on the right if I disable all the links? Cascade on delete and Update... are also check.

Second approach:

-The second question is: if I add the new hospital names to hospital table to the end of the records, how can I keep the left table to be alphabetically sorted while keep the relation with other right tables considering that I can disable link between tables temporarily. Can other tables on the right be updated? Can I sort the hospital names alphabetically while keep auto number increasing and then link all the tables back together? For example, a table start with B, I want to insert it to the hosID 3, which it used to map to a different hospital. I need to do this to keep the sort on hosID and hospital names.


Please advise me.

Regards,
BitBit

[HansV]

1) There is no need to disable referential integrity or to delete data if you want to add hospitals to the hospital table.
Adding a new hospital will not automatically create records in related tables. Users can add them as needed, or you can use append queries or VBA code to do that.

2) There is no need to keep the hospitals in alphabetic order. In a relational database, records are stored in an arbitrary order in a table. By default, the records are displayed in the order of the primary key, but you can display them in any order as long as you can specify the sort order in a logical way. For example, you can create a query based on the hospital table that sorts the records by hospital_name. You can use this query as the record source for a form or report, and as the row source of a combo box or list box. You should never change the value of the primary key in order to insert a record!

[BittenApple]

Hans,
Thanks for the response,
1-Will VBA code be complicated?
2-Per your instruction, I will add a hospitable which starts with letter B to the end of records. Initially, the records are displayed in the order of the primary key, adding the hospital name starting with letter B to the end of records, then hospital names are not sorted any longer. So I sort the table by hospital names, then hos ID will look like:
1
2
3
4
99
5
6
The last added hospital sits to the beginning of the table.
I wonder if I change the sort field and put it on hos_Name, that would create a problem in child tables and database itself as general.

-you can create a query based on the hospital table that sorts the records by hospital_name:
That is very thoughtful, I have already created my database, and it will be hard to go back and change everything, tables can be sorted, right? The fact table is not populated with data by end users yet.

I hope this all makes sense.

Regards,
BitBit

[HansV]

1. The code would not be very complicated.

2. It doesn't matter if the new hospital whose name begins with a B has a higher Hos_ID than a hospital whose name begins (for example) with H or S. An AutoNumber primary key is just a unique identifier that never (!) changes, even when the name of the hospital changes. An AutoNumber doesn't have an intrinsic meaning; in fact, most database designers don't show AutoNumber fields to the end user; they do include them in the record source of forms and reports, but don't have a visible text box bound to the AutoNumber primary key.

[BittenApple]

Hello Hans,
Can I have VBA code, please?

After I have visualized for so many times what happens if I enter a new hospital name to the end of my records, I decided to create a mock database.
I added a new hospital name to the end of table; I sorted the hospital table by hospital name, the combo box on the form doesn't follow the same sort; unless, I had created my combo box row source based off a query. Because on the query, we can always support the sort, that is my thought.

If I ruin my database which I have built for my project and change the row source for the combo box to a query; I am afraid, I might ruin everything. What else can be done?

Regards,
BitBit

Thanks for the rest of the explanation.

[HansV]

You'll have to tell me exactly what the code should do, in detail.

And yes, the Row Source of the combo box should be a query based on the hospital table; the query should be sorted on the hospital name. The combo box will then also be sorted on the hospital name.

[BittenApple]

Hans,
You said:
1) There is no need to disable referential integrity or to delete data if you want to add hospitals to the hospital table.
Adding a new hospital will not automatically create records in related tables. Users can add them as needed, or you can use append queries or VBA code to do that.

I wonder what the code could be

Regards,
BitBit

[HansV]

You'll have to specify what exactly you want the code to do, in detail.

[BittenApple]

Hello Hans,
I just read your answer again. Sorry for inconvenience.
I will let you know.
Regards,
BitBit