Work Areas in a database

[LMB]

I am working on a new database to keep track of our department employees demographics as well as other things. Right now we keep track of their work areas in an excel spreadsheet. I want to know if it would be a good idea to try to incorporate the work areas in my database since it keeps track of seniority, name changes, etc better than excel. We keep track of the number of hours each employee works in each area. There are 8 work areas. I have a separate sheet for each area and the supervisor types in the number of hours each employee worked in a particular area on a particular date.

On my final report page in excel it lists the day shift employees from the most senior to the least senior in column A. Column B are the totals for a work area, Column C are the totals for the second work area and so on to show all 8 work areas and total number of hours worked in each area for each employee all on one sheet. If I put these work areas in access, would I create a separate table for each work area and link by EmployeeID and then join all the tables in a query to make the report or would I make one table called work areas and have a WorkAreaID, EmployeeID, WorkArea, WorkAreaDate, WorkAreaHours?

Thanks,
Linda

 

[Douglas J. Steele]

It is seldom a good idea to create multiple tables to represent the same
entity. You invariably end up naming the tables in such a way that the table
name actually contains data in it (WorkArea1, WorkArea2 and so on), making
it very difficult to do effective searches.

Your second approach would be much better.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


I am working on a new database to keep track of our department employees
demographics as well as other things. Right now we keep track of their work
areas in an excel spreadsheet. I want to know if it would be a good idea to
try to incorporate the work areas in my database since it keeps track of
seniority, name changes, etc better than excel. We keep track of the number
of hours each employee works in each area. There are 8 work areas. I have
a separate sheet for each area and the supervisor types in the number of
hours each employee worked in a particular area on a particular date.

On my final report page in excel it lists the day shift employees from the
most senior to the least senior in column A. Column B are the totals for a
work area, Column C are the totals for the second work area and so on to
show all 8 work areas and total number of hours worked in each area for each
employee all on one sheet. If I put these work areas in access, would I
create a separate table for each work area and link by EmployeeID and then
join all the tables in a query to make the report or would I make one table
called work areas and have a WorkAreaID, EmployeeID, WorkArea, WorkAreaDate,
WorkAreaHours?

Thanks,
Linda

 

[LMB]

Thanks,

I wasn't sure if I would need 3 tables to do this. Employees, Junction, Work Areas. Would it be a many to many relationship? Many employees work many areas or is the way I have it going to work? I keep trying to get this on paper so I can see how the data willl look but I probably need to do something else for a little while.

Thanks,
Linda
It is seldom a good idea to create multiple tables to represent the same
entity. You invariably end up naming the tables in such a way that the table
name actually contains data in it (WorkArea1, WorkArea2 and so on), making
it very difficult to do effective searches.

Your second approach would be much better.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


I am working on a new database to keep track of our department employees
demographics as well as other things. Right now we keep track of their work
areas in an excel spreadsheet. I want to know if it would be a good idea to
try to incorporate the work areas in my database since it keeps track of
seniority, name changes, etc better than excel. We keep track of the number
of hours each employee works in each area. There are 8 work areas. I have
a separate sheet for each area and the supervisor types in the number of
hours each employee worked in a particular area on a particular date.

On my final report page in excel it lists the day shift employees from the
most senior to the least senior in column A. Column B are the totals for a
work area, Column C are the totals for the second work area and so on to
show all 8 work areas and total number of hours worked in each area for each
employee all on one sheet. If I put these work areas in access, would I
create a separate table for each work area and link by EmployeeID and then
join all the tables in a query to make the report or would I make one table
called work areas and have a WorkAreaID, EmployeeID, WorkArea, WorkAreaDate,
WorkAreaHours?

Thanks,
Linda

 

[Douglas J. Steele]

Yeah, you're probably best off assuming it's a many-to-many relationship,
with a Junction table to resolve the many-to-many.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Thanks,

I wasn't sure if I would need 3 tables to do this. Employees, Junction,
Work Areas. Would it be a many to many relationship? Many employees work
many areas or is the way I have it going to work? I keep trying to get this
on paper so I can see how the data willl look but I probably need to do
something else for a little while.

Thanks,
Linda
It is seldom a good idea to create multiple tables to represent the same
entity. You invariably end up naming the tables in such a way that the table
name actually contains data in it (WorkArea1, WorkArea2 and so on), making
it very difficult to do effective searches.

Your second approach would be much better.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


I am working on a new database to keep track of our department employees
demographics as well as other things. Right now we keep track of their work
areas in an excel spreadsheet. I want to know if it would be a good idea to
try to incorporate the work areas in my database since it keeps track of
seniority, name changes, etc better than excel. We keep track of the number
of hours each employee works in each area. There are 8 work areas. I have
a separate sheet for each area and the supervisor types in the number of
hours each employee worked in a particular area on a particular date.

On my final report page in excel it lists the day shift employees from the
most senior to the least senior in column A. Column B are the totals for a
work area, Column C are the totals for the second work area and so on to
show all 8 work areas and total number of hours worked in each area for each
employee all on one sheet. If I put these work areas in access, would I
create a separate table for each work area and link by EmployeeID and then
join all the tables in a query to make the report or would I make one table
called work areas and have a WorkAreaID, EmployeeID, WorkArea, WorkAreaDate,
WorkAreaHours?

Thanks,
Linda