How do I create a new table for each employee, or do I need to?

[Luna Saisho]

Hi! Like I often see in these, I'm somewhat new to Access and I'm tasked
with making a database that tracks employee performance in our warehouse.

So what I need to do is be able to have an employee record, then for every
day they worked, their manager will put information in, such as pallets
loaded and hours worked per shift.

I have some of it set up already, but all I understand to do at this point
is to have one day's worth of info in there, and I've been asked to create it
so they can get daily or monthly reports for each employee (and the entire
warehouse staff, if possible) to track performance.

Does anyone have any tips to point me in the right direction on how to store
the info? I should be able to make the reports ok.

Thank you so much!

 

[Jerry Whittle]

First off you do NOT make a seperate table for each employee. What you need
is at least two tables. One, call it tblEmp has all the information about the
employees like Employee number, first name, last name, supervisors, etc.
Something like the Employee number, call it EmpNo, can be the primary key.

Then you need a second table, call it tblPerformance, that also includes an
EmpNo field which will be the foreign key, date, pallets loaded, hours
worked, etc.

With these two tables joined you can add records for the employee in
tblPerformance. From it you can run reports based on the date field for
daily, weekly, monthly, ect., reports.

Sounds like you are new to the database game. We all were at one time.
I highly suggest getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.

 

[Luna Saisho]

Hi Jerry,

Thanks so much for your amazingly fast and kind reply!

Yes, I am new to creating databases, but my manager thinks I have the head
to do this kind of stuff. Considering I never created anything in Access
before, what I have already is kind of amazing! I will definately look
into that book, but do they have one for a Goddess in Training?

You said a few things that really clicked with me, and you provided a great
start for me here. I figured a table for each employee would be overkill,
but I didn't think about an ID for each employee. My question on this is
should I use the auto-numbered ID for the employee number, or should I have
the manager entering assign one themselves?

I have one more question (for now) if you don't mind.. When the manager
enters the daily info for an employee, what would be the best way for them to
select an employee?

Again, thank you!

 

[John Vinson]

On Thu, 18 May 2006 12:09:01 -0700, Luna Saisho <Luna

Hi! Like I often see in these, I'm somewhat new to Access and I'm tasked
with making a database that tracks employee performance in our warehouse.

So what I need to do is be able to have an employee record, then for every
day they worked, their manager will put information in, such as pallets
loaded and hours worked per shift.

I have some of it set up already, but all I understand to do at this point
is to have one day's worth of info in there, and I've been asked to create it
so they can get daily or monthly reports for each employee (and the entire
warehouse staff, if possible) to track performance.

Does anyone have any tips to point me in the right direction on how to store
the info? I should be able to make the reports ok.

You certainly should NOT create a new table for each employee. Storing
data in fieldnames is a very bad idea; storing it in tablenames is
even *worse*!

You need TWO tables: a table of Employees, with one record for each
person; this table would have a unique EmployeeID as its Primary Key;
and a table of WorkDone, related one-to-many to the table of
employees. This table would have its own primary key (perhaps an
Autonumber), and an EmployeeID field to link it to the Employees
table. It would have fields for the information you need to record
about work done. If you have various types of jobs that you want to
record (e.g. pallets loaded, widgets repaired, winks tiddled) then you
may need more tables. For time on the job I'd really strongly suggest
recording the start and end times, and using Queries to calculate the
time on the job (there's a handy DateDiff() function to do this).

In any case, the daily or monthly reports will NOT be part of your
tables! They will instead be calculated on the fly, using Queries.


John W. Vinson[MVP]

 

[Luna Saisho]

Thank you to both of you for your replies! So far, things are moving along
real well, but I am again about to show my newbie-ness to you with what's
very likely quite a basic question.

I've made two tables now, and have created a relationship between them.
Unfortunately, this relationship is rocky in one way...

I have one form that will display a screen much like the sheet the managers
get from each material handler. It has their name, and their shift that can
be 1-3 or A-D (8 hour shifts are 1-3, and 12 hour shifts are A-D, if you're
interested, but it's not important for this database). I'd like the shift to
be able to be changed in the main table, but any information entered below
(pallets shipped/received/put away, hours worked on each shift, etc) to be
stored in the second table.

I thought referencing the other table on the form (Like
"=[EFF-Performance]![Hours Shift 1]") would work, but it only shows "#Name?"
in that field on the form.

I'm familiar with doing reports, mostly, so this is my last and biggest
hurdle to jump, I believe.

BTW, Jerry, I do plan to pick up that book but I do need to continue work on
this to get my paycheck.

Thank you very much!!
Stephanie

 

[KARL DEWEY]

Join the two tables in a query. Left join Employee to Performance on the
EmpNo. In design view click on the EmpNo in the Employee table and drag it
to the EmpNo in the Performance table. Double click the line that connect
them and in the window that opens select the item that says you want all
records from the Employee table and only those that match from the
Performance table.

Use a form that has the query as record sorce for data entry. You might
consider a main form and a subform - Employee - Preformance.

 

[John Vinson]

I have one form that will display a screen much like the sheet the managers
get from each material handler. It has their name, and their shift that can
be 1-3 or A-D (8 hour shifts are 1-3, and 12 hour shifts are A-D, if you're
interested, but it's not important for this database). I'd like the shift to
be able to be changed in the main table, but any information entered below
(pallets shipped/received/put away, hours worked on each shift, etc) to be
stored in the second table.

I thought referencing the other table on the form (Like
"=[EFF-Performance]![Hours Shift 1]") would work, but it only shows "#Name?"
in that field on the form.

Use a Form based on the employee table, and a Subform based on the
[ERF-Performance] table; use the Employee ID as the master/child link
field.

Note that punctuation marks and blanks can cause problems down the
line if you use them in table or fieldnames; and that if you have
fields named [Hours Shift 1], [Hours Shift 2] etc. then your database
*is not properly normalized!!!* You should instead have a field named
Shift (filled from a combo box, say, to allow only values 1, 2, 3, A,
B, C, D) and a separate field named Hours, containing the hours worked
on that shift. If an employee works two shifts - you enter two
records.

John W. Vinson[MVP]

 

[Luna Saisho]

Ah, so much wonderful help here! Between this area, and MS' own assistance
area, things are coming along well, thank you everyone! ^_^

By strange coincidence, and this is the truth, one of the very few fields I
had spaces in was exactly [Hours Shift 1] ... to 3. Considering I found out
about the A-D being needed part way through making this, I was going to try a
different way to store this info, and your suggestion hits the spot almot
exactly. Considering many of the employees work overtime, I will use four
fields. Two to hold the shift, and two that match holding the hours worked
for each shift. I may have to ask if it's possible for them to work within 3
shifts, but I believe that's rare if at all.

A subform... Something again I didn't think about.

Believe it or not, this is mostly all new to me, but everyone's suggestios
have really helped a lot. I'm sure I'll be back! Any other help is welcome!


Steph

I have one form that will display a screen much like the sheet the managers
get from each material handler. It has their name, and their shift that can
be 1-3 or A-D (8 hour shifts are 1-3, and 12 hour shifts are A-D, if you're
interested, but it's not important for this database). I'd like the shift to
be able to be changed in the main table, but any information entered below
(pallets shipped/received/put away, hours worked on each shift, etc) to be
stored in the second table.

I thought referencing the other table on the form (Like
"=[EFF-Performance]![Hours Shift 1]") would work, but it only shows "#Name?"
in that field on the form.

Use a Form based on the employee table, and a Subform based on the
[ERF-Performance] table; use the Employee ID as the master/child link
field.

Note that punctuation marks and blanks can cause problems down the
line if you use them in table or fieldnames; and that if you have
fields named [Hours Shift 1], [Hours Shift 2] etc. then your database
*is not properly normalized!!!* You should instead have a field named
Shift (filled from a combo box, say, to allow only values 1, 2, 3, A,
B, C, D) and a separate field named Hours, containing the hours worked
on that shift. If an employee works two shifts - you enter two
records.

John W. Vinson[MVP]

 

[John Vinson]

Ah, so much wonderful help here! Between this area, and MS' own assistance
area, things are coming along well, thank you everyone! ^_^

By strange coincidence, and this is the truth, one of the very few fields I
had spaces in was exactly [Hours Shift 1] ... to 3. Considering I found out
about the A-D being needed part way through making this, I was going to try a
different way to store this info, and your suggestion hits the spot almot
exactly. Considering many of the employees work overtime, I will use four
fields. Two to hold the shift, and two that match holding the hours worked
for each shift. I may have to ask if it's possible for them to work within 3
shifts, but I believe that's rare if at all.

"Fields are expensive. Records are cheap".

Four fields is twice too many. I'd really, really, really urge that
you use TWO fields. If an employee works two shifts on one day (two
twelve-hour shifts!!?) then *ENTER TWO RECORDS*. You can sum up the
time grouping by date later on.

A subform... Something again I didn't think about.

You'll use them a LOT - one of the really powerful tools in Access.

Believe it or not, this is mostly all new to me, but everyone's suggestios
have really helped a lot. I'm sure I'll be back! Any other help is welcome!

John W. Vinson[MVP]

 

[Luna Saisho]

Hi John,

Ok, I can see your point here... Basically, keep it as simple as possible so
there are fewer things that could go wrong? It makes sense.

I'm so sorry to keep bothering you and the others with what's probably
terribly basic questions, but I need just enough to get this somewhat simple
database working. I don't believe I'll have problems with reports or queries
to present the data, but I'm stuck in one more little spot.

I have the EFF-Data and EFF-Efficiencies tables joined. I can enter one
record (Their daily stats) for each employee I enter into the database, and
if I try to add another record, I get "The changes you requested to the table
were not successful because they would create duplicate values in the index,
primary key, or relationship." (Copy/Paste from the Help, not the dialogue
box)

At this point, I know I'm nearly drowning... I keep making advances (largly
in part to your help) and then find another area to get stuck in. I really
appreciate all the help you and everyone's given me, and I hope I don't
bother you too much more.

Steph

Ah, so much wonderful help here! Between this area, and MS' own assistance
area, things are coming along well, thank you everyone! ^_^

By strange coincidence, and this is the truth, one of the very few fields I
had spaces in was exactly [Hours Shift 1] ... to 3. Considering I found out
about the A-D being needed part way through making this, I was going to try a
different way to store this info, and your suggestion hits the spot almot
exactly. Considering many of the employees work overtime, I will use four
fields. Two to hold the shift, and two that match holding the hours worked
for each shift. I may have to ask if it's possible for them to work within 3
shifts, but I believe that's rare if at all.

"Fields are expensive. Records are cheap".

Four fields is twice too many. I'd really, really, really urge that
you use TWO fields. If an employee works two shifts on one day (two
twelve-hour shifts!!?) then *ENTER TWO RECORDS*. You can sum up the
time grouping by date later on.

A subform... Something again I didn't think about.

You'll use them a LOT - one of the really powerful tools in Access.

Believe it or not, this is mostly all new to me, but everyone's suggestios
have really helped a lot. I'm sure I'll be back! Any other help is welcome!

John W. Vinson[MVP]

 

[Luna Saisho]

Please ignore my last question on duplicates, I'm sorry. I figured out my
issue (the autonumber was still on in the Performance table) and things are
working great.

If I need more help, it'll be on reports/queries and I'll post something
new, but I do want to thank you and everyone else who replied! You made this
possible, and my bosses are going to be very happy!

Ah, so much wonderful help here! Between this area, and MS' own assistance
area, things are coming along well, thank you everyone! ^_^

By strange coincidence, and this is the truth, one of the very few fields I
had spaces in was exactly [Hours Shift 1] ... to 3. Considering I found out
about the A-D being needed part way through making this, I was going to try a
different way to store this info, and your suggestion hits the spot almot
exactly. Considering many of the employees work overtime, I will use four
fields. Two to hold the shift, and two that match holding the hours worked
for each shift. I may have to ask if it's possible for them to work within 3
shifts, but I believe that's rare if at all.

"Fields are expensive. Records are cheap".

Four fields is twice too many. I'd really, really, really urge that
you use TWO fields. If an employee works two shifts on one day (two
twelve-hour shifts!!?) then *ENTER TWO RECORDS*. You can sum up the
time grouping by date later on.

A subform... Something again I didn't think about.

You'll use them a LOT - one of the really powerful tools in Access.

Believe it or not, this is mostly all new to me, but everyone's suggestios
have really helped a lot. I'm sure I'll be back! Any other help is welcome!

John W. Vinson[MVP]

 

[John Vinson]

If I need more help, it'll be on reports/queries and I'll post something
new, but I do want to thank you and everyone else who replied! You made this
possible, and my bosses are going to be very happy!

Always good to keep the bosses happy, and I'm glad I was able to help
do so!

Now I've got to go put some Friskies Whitefish catfood in the bowl or
*my* boss (the one who curls up on my lap as I type) will be unhappy!
<g>

John W. Vinson[MVP]