How to Insert a record in Multiple Tables in Relationship. (Starter Leaver DB)

[mond007]

Hi
I have designed a Database table layout that is normalised. It's a very simple application designed to record Starters and leaver in a Company. There will be 70 or so Managers entering details of who has started/left in their departments (big Company).
A team of Admin staff will then be tasked to perform the rudimentary associated tasks like Update RADS DB, Order Laptops (IT), Update SAP etc.

The following shows the tables.




The Purple text indicate different Teams of people responsible for entering the relevant data.
All users all can create starter leaver departmental changes/records.

I think I will need the following MS Access Forms screens :

a) Form Screen 1 A screen to be able to set up base data i.e. Tbl_Starter_Leaver All Departments can enter data here.

b) Form Screen 2 A screen to enter data RADS Data so a Form for :
Tbl_Starter_Leaver with a subform for Tbl_Rads (Entered by the RADS Team)

c) Form Screen 3 A screen to enter data Boarding process data so a Form for :
Tbl_Starter_Leaver with a subform for Tbl_Boarding_Status (Entered by the Boarding Process Team Team)

d) Form Screen 4 A screen to enter data IT Status Data so a Form for :
Tbl_Starter_Leaver with a subform for Tbl_IT_Status (Entered by the IT Team)

e) Form Screen 4 A screen to enter IT Status Data so a Form for :
Tbl_Starter_Leaver with a subform for Tbl_Sap_Status (Entered by the SAP Team)



So once the base data has been entered by using Form Screen 1 by any user all the relevant teams in purple will go in and do there retrospective entries to get the Starter or Leaver life cycle to completion status - which is deemed complete once all the team inputs have been entered.

I would like some assistance how to construct the Form Screen 1 (The entering of the base data). Essentially, this is where I will have a Form with only one table in it.
For new data entered in this screen i.e. Tbl_Starter_Leaver (base data)

The secondary reason for normalising the database is to prevent locking. Basically, multiple teams of people could all be updating data and not lock each other out. I would obviously detach/split the database two front and back end as usual.

QUESTION : How do I automatically insert blank records in all the other tables ? i.e. Tbl_Rads, Tbl_Boarding_Status, Tbl_IT_Status & Tbl_SAP_Status when adding a record to the base table Tbl_Start_Leaver ?

Thanks in advance Kuldip.

[John_G]

First, a question about your table relationships. If, in the tables on the right (Tbl_Rads etc) Autonumber is an autonumber field within those tables, then your relationships are wrong. There is no guarantee whatever that the numbers in those tables will match the Autonumber field in the main table. Each of those tables must have a field (Numeric Long, but not autonumber) that refers to the number in the main table, i.e. it is a Foreign Key back to the main table.

The relationships between Tbl_Starter_Leaver and those other tables will use that FK.
(Note: Don't name fields "Autonumber"; it's very confusing and says nothing about what the field contains)

What is the relationship type between Tbl_Starter_Leaver and those other 4 tables? If it is one-to-many, then the diagram makes sense, but if it is one-to-one, then you don't need those other tables - why not just include their data as fields in the main table?

[mond007]

Ok,

The Autonumber is just my way of saying that the table has its inherent autonumber and is NOT part of the data. I was told in another thread that table invariably all tables should have autonunmber. This is why when a new record is added to the base data new records are also created in related tables i.e Tbl_Rads, Tbl_boarding_Status, Tbl_IT_Status and Tbl_SAP_Status (I presume with the same generated ID).

The answer to the main question about the what is the relationship is that it is a 1-to-1. In another thread
https://www.accessforums.net/showthread.php?t=59863&p=319880#post319880
I was told that normalising the data needs to be done.

>The secondary reason for normalising the database is to prevent locking. Basically, multiple teams of people could all be updating data and not lock each other out.

In fact, the main reason for splitting up into different tables (normalising) is to make this DB multi-user. Once the base details are created and a number of different people can simultaneously update different parts of the same record. When you have a small Admin team you would not want locking problems.
If I could have this as one table with all the details "flattened" then this would be an easy task.

Thanks in advance.

[John_G]

I was told in another thread that invariably all tables should have autonunmber.

There are those who have that opinion, but I strongly disagree with it. If your data has a numeric or short text field that uniquely identifies each record, that field is perfectly acceptable for use as a primary key, no autonumber field needed.

In your case, however, I don't see any data field that might be suitable, so you probably do need an autonumber field, especially since you are using it in relationships.

(I presume with the same generated ID).

No. You cannot make that assumption when it comes to using autonumbers. If those 4 related tables have an autonumber field, it cannot be used to relate those tables to the main table. You need an additional field in those tables (not autonumber) which contains the autonumber value from the main table.

There is nothing wrong with your idea of using separate tables for different parts of the data, to prevent locking.

[John_G]

If you use a main form - sub form setup, then link the main form and the sub-form using the Autonumber on the main form and the FK field (see previous post) on the sub-form, then you will see that the Autonumber from the main form is automatically copied to the FK on the sub-form.

The bare-bones sample database attached demonstrates this for you.

[mond007]

Thanks you for comments. I also share the same view about the autoumber.

So back to my original question :

When I have Form Screen 1 which is only the base core data - How do I take over the automatic insert of main record and then simultaneously insert records into the associated tables i.e. i.e Tbl_Rads, Tbl_boarding_Status, Tbl_IT_Status and Tbl_SAP_Status ?

So if I understand things correctly, I have to INSERT a record into Tbl_Start_Lever and then setup a key field in say Tbl_RADS table etc and set the number in that Key field to be the autonumber generated in the Tbl_Start_Lever table ?.

I have a look at some similar threads but its not so easy for me to visualise. I do know it will be with record sets.

I am not sure how or where to program up the code ?.

Thanks in advance.

[John_G]

How do I take over the automatic insert of main record and then simultaneously insert records into the associated tables i.e. i.e Tbl_Rads, Tbl_boarding_Status, Tbl_IT_Status and Tbl_SAP_Status ?

You don't need to. Assuming you are using a main form - sub form arrangement with the main form and sub form linked on the ID, then when you have a main table record on the screen, and move to the sub-form, Access will create the record in the sub-forms's record source table as soon as you type anything into it.

This concept works whether you have a one-to-many or one-to-one relationship.

All this without a single line of code.

[mond007]

Thank you.
I have redone the table layout and added what I think are the correct FK links as you have mentioned. See following image.


So if I understand this correct, would create :

a) Form Screen 1 - which only has one table that creates the base/core data Tbl_Start_Leaver record. (All Depts)

b) Form Screen 2 - Which is a 'Form - SubForm' Setup with Tbl_Starter_Leaver & Tbl_Rads where
Tbl_Starter_Leaver.autonumber links to Tbl_Rads.Start_Leaver_ID (Foreign Key). (Admin Teams)

c) Form Screen 3 - Which is a 'Form - SubForm' Setup with Tbl_Starter_Leaver & Tbl_Boarding_Status where
Tbl_Starter_Leaver.autonumber links to Tbl_Boarding_Status.Start_Leaver_ID (Foreign Key). (Admin Teams)

etc.

Again, if I understand this correctly, the accessing Form Screen 1 would create the base Starter_Leaver record and then accessing Form Screen 2 Form-Subform) would create the Tbl_Rads record when I insert any Tbl_Rads field data.

This is great, I think I understand now.

My only issue with this is that I have an overhaul Tbl_Starter_Leaver.Starter_Leaver_Status (calculated) and this is based upon all the green fields in the diagram i.e.


Tbl_Rads.Rads Updated date
Tbl_Boarding_Status.Boarding Process Date
Tbl_IT_Status.Laptop Request Completed
Tbl_IT_Status.Mobile Request Completed
Tbl_IT_Status.VOIP Request Completed
Tbl_SAP_Status.SAP Change Request completed


It is only when all there dates are present and valid that the overhaul status is deemed "Completed" ?

Question : If the records are not created at the time if creating the Tbl_Starter_Leaver record then I can not set calculated Start_Leaver_Status ?.
I guess I will have to engineer a different way of working out the Start_Leaver_Status !.

Thanks in advance