Hours People worked in an Area

[LMB]

Hi Guys,

I am making a query so I can make a subform from it. I want to start with my table design first so I know that is correct. Purpose of the table is to keep track of what areas employees work and then I will total the hours worked in a query. Finally I need to have a report that is like a spreadsheet with a vertical list of employees then the column headers will be the work areas. It will look like this. I hope it shows up right.

cvicu msicu
Linda 24 24
Mary 12 48


By looking at this report, I will be able to decide between these employees who's turn it is to work in a particular area. We want everyone to have equal time in all areas.

Table Design I think will work.

tblEmployee
EmployeeID
EmployeeLastName
EmployeeFirstName

tblWorkAreas
WorkAreasID
WorkArea (field names are cvicu, msicu, etc...)

tblAreaWorked
AreaWorkedID
EmployeeID
WorkAreaID
WorkAreaDate
WorkAreaHours

Let me know if my tables are ok.

Thanks,
Linda

 

[Julie]

Hi Linda,

Your tables look fine! Your TblAreaWorked is designed such as each day an
employee works in a certain area, you record the tottotal hours the employee
worked in that area. Is that what you want? Another way of doing it would be
to record the starttime and endtime for each day and let Access calculate
the hours. For thos you would use the DateDiff function.

For your spreadsheet-like report, create a totals query and then create a
crosstab query based on the totals query for the report. Look at crosstab
queries in the Help file and in the books you bought.

Steve


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Hi Guys,

I am making a query so I can make a subform from it. I want to start with
my table design first so I know that is correct. Purpose of the table is to
keep track of what areas employees work and then I will total the hours
worked in a query. Finally I need to have a report that is like a
spreadsheet with a vertical list of employees then the column headers will
be the work areas. It will look like this. I hope it shows up right.

cvicu msicu
Linda 24 24
Mary 12 48


By looking at this report, I will be able to decide between these employees
who's turn it is to work in a particular area. We want everyone to have
equal time in all areas.

Table Design I think will work.

tblEmployee
EmployeeID
EmployeeLastName
EmployeeFirstName

tblWorkAreas
WorkAreasID
WorkArea (field names are cvicu, msicu, etc...)

tblAreaWorked
AreaWorkedID
EmployeeID
WorkAreaID
WorkAreaDate
WorkAreaHours

Let me know if my tables are ok.

Thanks,
Linda

 

[LMB]

Thanks.

It is easier for the supervisor to put in one number instead of start and finish time. People generally work 4, 6 or 12 hour increments but 4.5 works too. We don't need to be that precise, this database has nothing to do with payroll, it's just for trying to keep everyone fairly even.

Linda


Hi Linda,

Your tables look fine! Your TblAreaWorked is designed such as each day an
employee works in a certain area, you record the tottotal hours the employee
worked in that area. Is that what you want? Another way of doing it would be
to record the starttime and endtime for each day and let Access calculate
the hours. For thos you would use the DateDiff function.

For your spreadsheet-like report, create a totals query and then create a
crosstab query based on the totals query for the report. Look at crosstab
queries in the Help file and in the books you bought.

Steve


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Hi Guys,

I am making a query so I can make a subform from it. I want to start with
my table design first so I know that is correct. Purpose of the table is to
keep track of what areas employees work and then I will total the hours
worked in a query. Finally I need to have a report that is like a
spreadsheet with a vertical list of employees then the column headers will
be the work areas. It will look like this. I hope it shows up right.

cvicu msicu
Linda 24 24
Mary 12 48


By looking at this report, I will be able to decide between these employees
who's turn it is to work in a particular area. We want everyone to have
equal time in all areas.

Table Design I think will work.

tblEmployee
EmployeeID
EmployeeLastName
EmployeeFirstName

tblWorkAreas
WorkAreasID
WorkArea (field names are cvicu, msicu, etc...)

tblAreaWorked
AreaWorkedID
EmployeeID
WorkAreaID
WorkAreaDate
WorkAreaHours

Let me know if my tables are ok.

Thanks,
Linda