Access07: Hire Co : Create Human Resource tables

From virtualMV2015wiki
Jump to: navigation, search
 Home  MS Access <

Entity Relationship diagrams

In the design phase the staff/timesheet entities and relationships were described

Acc07 Hire ERD HR0.png

After discussions with the client, the fully attributed ERD following was created

Acc07 Hire AERD HR0.png

Note that Primary keys are underlined and foreign keys have a * placed next to them (but only in the diagram - do not put a * in the field name).

Create Staff and Timesheet tables

In the Heights database a single table was created. For the Hire database, 2 tables need to be created: tblStaff and tblTsheet

Create the following 2 tables (Field sizes are shown) The ID fields are the Primary keys

Acc07 Hire HR tblStaff.png Acc07 Hire HR tblTSheet.png

Nte that the order of attributes (fields) is primary key, Foreign key, table attributes (refer to the Timesheet table). This is done so that if you have a lot of fields it is easier to link the fields when creating relationships.

Create relationships

Database Tools > [Show/Hide] Relationships

Acc07 Relationships.png

In the Show Table dialog box either

  • double click the two tables (tblStaff and tblTSheet), or
  • use the [Control] key to select both table and click [Add]

(Now close the Show Table popup if it is still open)

Resize the fully attributed Entity so that all attributes (fields) show.

Establish the relationship between tblStaff.StfID and tblTSheet.StfID by clicking on tblStaff.StfID and dragging over to tblTSheet.StfID(You should always start from the 1 and drag to the many relationship).

Acc07 Relationships(1).png

An "Edit Relationships" window opens - select "Enforce Referential integrity"

Select [Join type]

Acc07 RelationshipsJoins.png

In the "Join Properties" window select the appropriate join type. Here we are selecting type (2) since we want all staff to be included whether or not there is a timesheet. (Normally (1) is selected).

Accept the options to close the pop-ups and you should wind up with the Fully Adttributed ERD as shown following:

Acc07 Hire HR Relationships1.png

Create extended relationships

This is only for advanced users. Suggest you close the database down. Make a copy (using Windows Explorer called dbHireExt.accdb ), then use this to add the extra tables and their relationships.

Acc07 Hire HR RelationshipsExt.png

Notes

  • To get the 1-1 relationship (tblStaff to tblCarPark) you need to create the StfID field in tblCarPark with the Indexed property set to "Yes (No Duplicates)" this means that a staff member can only ever be entered once into the car park table (that is a staff member can only have one car park)
  • To get the Staff - Manager relationship we add tblStaff twice into the Relationship diagram

Tips

  • When creating attributes use the following order: Primary key(s), Foreign key(s), table attributes
  • Always drag from the 1 to the many in a relationship.
  • Try to line up dependant fields.
  • Always expand tables to show all fields (sometimes this is not possible, but if you have large tables all the dependencies should show dependant fields)
  • Rearrange the tables to avoid crossing lines if you need to create many dependencies with many tables.
  • Circular relationships may indicate a problem!

Icon References.png References


Access07: Hire Co : Create Human Resource tables. (2017). In virtualMV's ( Michael Verhaart ) wiki. Retrieved December 16, 2017, from http://www.virtualmv.com/wiki/index.php?title=Access07%3A_Hire_Co_%3A_Create_Human_Resource_tables    (zotero)