Most effective joins

[trythis]

I have a database for staffing.

I have employee table with positions
department table
positions table
schedule table

I import the schedule from the time clock and I want to run reports from this.

I have
emplyeeid
positionid
departmentid
scheduleid
all primary keys in their table

My emplyee table also lists the position of each emplyee which matches with the positions table to get the descriptions of the positions.

I also have positions in the schedules table.

I am not sure how to map this.

should I map the positions table to the emplyees table then map the emplyee table to schedule

or should I map all tables to schedules.

tblschedules
schedulesid
empname
departmentid
positionid
starttime
endtime


tblDepartment
departmentid
depname
deptabbreviation

tblPosition
positionid
posname
posabbreviation

tblemplyee
emplyeeid
empname
positionid

I am making this to hard but cannot get it. Please help.
Tina

 

[David Eagar]

I think you have answered your own question:

I import the schedule from the time clock and I want to run reports from this.

If the primary purpose of the database is to report on the schedule, that should be your primary table. You can have many Departments, Employess etc but only 1 Schedule

 

[boblarson]

You would only need EmployeeID in the schedules table because the other ID's are already tied to the employee. (The department ID would also be stored in the employees table, unless you wanted to keep a history of their movement, in which case you would have a junction table which would store

tblEmployeesDepartments
EmployeeID
DepartmentID
EffectiveDate

 

[trythis]

I think you have answered your own question:

I import the schedule from the time clock and I want to run reports from this.

If the primary purpose of the database is to report on the schedule, that should be your primary table. You can have many Departments, Employess etc but only 1 Schedule

So my next question is how do I account for the fact that the position does not change. the department changes but everytime I see this employee I want to show what their position is.

so when I select that emplyee from a drop list I want a position field to populate with the posabbreviation. I cannot get this to work.

Help

 

[boblarson]

So my next question is how do I account for the fact that the position does not change. the department changes but everytime I see this employee I want to show what their position is.

so when I select that emplyee from a drop list I want a position field to populate with the posabbreviation. I cannot get this to work.

Help

Do you have something you can upload to show what you currently have? Basically, all you need to do is to include in the combo box rowsource the extra field from the positions table (linked to the employees table also in the rowsource query which is linked on positionID).

See if this sample helps. It shows you how you can get extra information and assign it to a text box for display purposes when you select a record from a combo box.

 

[trythis]

This is a nursing database so the position is RN or A so that will not change. (A is nurses aide and RN is registered nurse)

the Department will change if they are assigned to ER or OB or PT and they will change department sometime several times a day. for two hour shifts here and there.

Another thing that I just occured to me is that my emplyee table is redundant. So I should just delete it.

No I cannot delete it because I need that employee list so that someone can be added to the schedule that was not scheduled to work previously.

Sorry I just cannot wrap my brain around this.

Thanks,Tina