Many users, same info, different tables or same...

[naiveprogrammer]

I have a DB that will keep track of employee info per their supervisor. I
will create a FE for each user but I'm not sure how I should create the
table. Should I create a table for EACH user even though each user will
enter the same exact info. I would think this would be easier for queries &
reports but I'm not 100% sure.

Any ideas would be very helpful!

 

[mscertified]

What data are you keeping in this table and how will it be used, 'employee
info per their supervisor' is rather vague?
I normally use local tables where I don't want one user's data to conflict
with another user's data.
Local tables are also faster to process and eliminate the record locking
conflicts that can occur with a shared table.

Dorian

 

[naiveprogrammer]

The table keeps up with employee's # of hours worked for that week. Two
questions are asked- 1. Did Employee work entire week? OR 2. Did Employee NOT
work entire week. If #2 is true then the supervisor chooses from several
different options as to why their employee didn't work an entire week. (i.e.
Vacation, Sick, FMLA, & etc... ) All of which hours for type of "Leave" is
recorded as well. (i.e. Vacation = 4hrs for 11/28/05)

By local tables you mean create a table for EACH supervisor? How would you
suggest I put ALL Supervisors Data into ONE report?

 

[John Vinson]

I have a DB that will keep track of employee info per their supervisor. I
will create a FE for each user but I'm not sure how I should create the
table. Should I create a table for EACH user even though each user will
enter the same exact info. I would think this would be easier for queries &
reports but I'm not 100% sure.

Any ideas would be very helpful!

One table per person? ABSOLUTELY NOT. Doing so will not make things
easier, but rather make them much more difficult!

Data should not be stored in tablenames. It should be stored *in
tables*.

You should have one Employees table for all the data, with a field
identifying who is their supervisor. A Query can easily limit the
display and editing to that particular supervisor's reports.


John W. Vinson[MVP]

 

[naiveprogrammer]

Thanks John! That's what I was thinking but I wasn't 100% sure. I just want
the best design possible.

 

[John Vinson]

The table keeps up with employee's # of hours worked for that week. Two
questions are asked- 1. Did Employee work entire week? OR 2. Did Employee NOT
work entire week. If #2 is true then the supervisor chooses from several
different options as to why their employee didn't work an entire week. (i.e.
Vacation, Sick, FMLA, & etc... ) All of which hours for type of "Leave" is
recorded as well. (i.e. Vacation = 4hrs for 11/28/05)

By local tables you mean create a table for EACH supervisor? How would you
suggest I put ALL Supervisors Data into ONE report?

Don't confuse data PRESENTATION - a form or report - with data
STORAGE. They are different tasks with different requirements!

You will need at least two tables:

Employees
EmployeeID
LastName
FirstName
<other bio details>
SupervisorID

COnsidering that a supervisor would herself be an employee, you can
use a "Self Join" relationship where the SupervisorID field in one
record would be the EmployeeID of that person's supervisor. Only the
CEO would have a NULL SupervisorID.

Hours
EmployeeID < link to Employees
WorkDate
StartTime
EndTime
TimeType << e.g. Working, Leave, Sick, Vacation, ...

You'ld record each episode of work or absence in a new record in the
Hours table.

For a Report for a given supervisor's employees, you'ld create a
Totals Query joining Employees to Hours, summing the hours, grouping
by TimeType, and using a criterion to select the supervisor.

There are many other possibilities but this should give you a good
start.


John W. Vinson[MVP]