Using one field in mutiple tables

[Nishant_R]

Hi I need urgent HELP.. I am working on project where i am making a database of all employees in a firm. Now i have made several tables for his leave details,family details,medical records,etc.Now in eac of the table the primary key is there employee number. I want that i should fill the employee number in only one table and it should get filled in other tables automatically.

 

[meboz]

All the tables should exists in a one-to-one relationship.

I take it what your after is that when you insert a new employee, you would like to create new records, with that employee ID number, in all the related tables.

Ive have had this problem before I a decided to solve it via VBA and execute an INSERT statement on each of the related tables, with the employee ID number i was entering, to create the records

The question I have to those in the know is:

Is this the recommended way of dealing with a situation like this?

AsI see it, its a pretty common scenario

 

[Mile-O]

You need one-to-many relationships here as one employe can have many medical details, family members, leave details.

The employee ID should only be the primary key in the employees table.

ie.

tblEmployees
EmpoyeeID
Forename
Surname

tblMedicalHistory
HistoryID
DiagnosisID
EmployeeID
DateOfIllness

tblLeaveHistory
LeaveID
AbsenceTypeID
EmployeeUD

and whatever else - a table for leave types, a table for diagnoses, etc.

 

[The_Doc_Man]

When you have a situation like this, with a single person's ID linking back to multiple records in a parent-child relationship, a common way to handle it is with parent/child FORMS to enter the details.

The parent form hold's the person's data. Then you build a sub-form for each separate table and embed the sub-form on the parent form. For each sub-form, if you let the sub-form wizard help you, you can define the (primary key) person's ID field appearing the parent form as the linking field to the (foreign key) person's ID field appearing in the sub-forms. If the information is suitable, you can do many-to-one mapping.

Doing it this way, when you add a new record for the sub-form, it is automatically entered because of the linkage. If you find that you are running out of space for the sub-forms (and it is highly likely that you will), look at tab controls, one tab per sub-form. Also note that if any table needs other data from the parent BESIDES the person's ID, the way to do that is to make the field names the same in the two tables. (However, in most cases that I can imagine, this would denormalize the application tables and is therefore not recommended.)

In the design, you have to remember that when we speak of a "many-to-one" linkage, "many" CAN and probably WILL include cases "none" and "one" for some folks. Like, no dependents, no children, or no wife, or stuff like that. So when you have a many-to-one design, be sure to make your application test for and correctly handle the "none" cases.

It is probably NOT a good idea to create blank records in the child tables the moment you add the parent table record because the whole point of a relational database is to help you minimize space. Adding the blank to other tables is probably not too good a choice. Also, probably misleading.

Don't forget that when building reports, you have ways to handle oddball cases by using INNER and OUTER JOIN clauses constructively. An inner join will give you all parent records with at least one child. An outer join will give you all parent records and will just put a NULL in the child fields for any of the "none" cases.

 

[Nishant_R]

Little confused

Hi to all Thanks for response to my query. But the question which i think still remains unanswered is that, when i make a form for user to add an employee, and i enter his ID in one table now i don't want to enter that ID in every subtable like his health records, etc so how should i go about it?? I want once i add ID in one table it should automatically get entered in other table like health records,family records,etc.

 

[Mile-O]

I want once i add ID in one table it should automatically get entered in other table like health records,family records,etc.

Again, define the correct relationships and then use subforms, remembering to Link master/Child fields between the subform and the parent.