Staff Movement Database

[hgg21]

Hi,

I have this plan to start a staff movement database. However I need some tips on how to get it going.

There are 3 shifts per day. 2 persons per shift. I have 3 tables. 1 table is for employees profile (tblprofile). Where EmployeeID is the primary key. 2nd table is activity table (tblactivity) which records the date and shift1 2 or 3 or rest days of the employee. They are linked by employeeID. 3rd table is a training table which records the training which employees took. Also linked to the 1st by employee. Both r/s are 1 is to many.


However, I m not sure is this the optimal or is there a better way to create a r/s so that I can find out who is what shift on a particular date. Must be able to show the 2 employees particulars in a form. I m not so sure about query and filter functions also. Pls help. Thanks!!!

Regards
Jerry

 

[devwilcat]

Hello,

Your layout looks good to me. For displaying the relevant scheduling data, I would use an INNER JOIN statement. Something to the effect of:

SELECT tblProfile.EmployeeID, tblProfile.EmpName, tblActivity.Date, tblActivity.Shift, tblTraining.Training
FROM tblProfile INNER JOIN tblActivity ON
tblProfile.EmployeeID = tblActivity.EmployeeID
INNER JOIN tblTraining ON
tblProfile.EmployeeID = tblTraining.EmployeeID

I believe this query would get you each employees shifts and training. You can then throw in a where condition for the tblActivity.Date if you just wanted to see the data for a particular day.

I hope this helps.

Regards,

Devon