Database Confusion

[Sydious]

I am fairly new to Access and am working on a project for work. I know how to
get around in access but when I try to build the database I get all confused.
I have about 80 - 100 employees I would like to track labor hours for.
Currently I have a excel Spred sheet that gets filled out every day and
saved. Of course now I have tons of these files saved. I want to make an
Access database for tracking and saving every day the labor hours used by
each individual employee. It will be used by the Group Leaders on 3 shifts
who will enter the hours each day. Of course I have lots of plans for it but
I can't even get that part of it going.

Can anyone explain or lead me to some info somewhere how I would set this up?
I think I am getting confused with relationships between tables. Not sure if
thats whats wrong.

I have 2 Tables.
1 Employees
Feilds:
Employee ID
Name
Shift

2 Labor Hours
Feilds:
EmployeeID
LaborDate
LaborHourType1
LaborHourType2

I put a One to Many Relationship from EmployeeID (Table1) to Employee ID
(Table 2)

Above info is just info.
I need help in the concept of putting together this type of databases.
Not sure if anyone will understand this but I seem to be running in circles
on my own.
Help!????

 

[Sydious]

To make it more clear as to what I am tring to do:

I want to make a Form that the group leaders can fill out each day with ALL
the employee's for their shift on it and fields to enter hours into the
diffrent labor types.

All I can end up with is a Form where you have to cycle through each
individule employee and a sub for with related Labor Hours Data.

Kind of like I have now with excel sheets.
1 sheet with all the names and hours for the shift for one day.

Maybe this makes it more understandable.

Any suggestions?

 

[Wayne-I-M]

Hi

To start with I would suggest getting basic format of the tables sorted.

You could 1 table for employees
Employee ID (Primary - autonumber)
1stName (text)
2nsName (text)
Some address fields
Ect.
This table would hold "unique" information regarding the employee and
"nothing" else.

Next you could have another table that holds other (shift) information as
shift details may change ie. the hourly rate of pay may alter and so you need
to be able to store this without altering past records.

ShiftID (Primary - autonumber)
EmployeeID (number - Linking field to tblEmployees)
LaborDate (date)
ShiftLeader (text)
LaborHourType1Rate (currency)
LaborHourType2Rate (currency)
Deductions
Additions




Create a query to combine the 2 tables then run your forms, reports, etc
filter from this query. As an example you could have a dropdown that the
shift leader could simply select their name from and this would filter the
form to their own shift. Or the dropdown could filter a subform with the
employees of their shift shown. etc. etc

The main point is that really should ensure that each table ONLY holds
"unique" information. - Note your post showed that shift was included in
your employees table and this is not unique as there are (I assume) more than
1 employee on each shift.

Once you have this up and running you can always come back to the forum with
specific questions on how to undertake specific actions or functions.

As an example the query can be used to work out the amount paid out for
employees,
Hope this point you someway towards what you are aiming for.


--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

 

[Sydious]

Hmmm...
OK now I am more confused.
Why wouldn't a field in the employee table be for what shift the employee
belongs too?

I am not trying to track wages. I am trying to trck how many hours each
employee worked on each task that day.
So I am trying to make a form that lists all 25-30 employees (visable all at
once) with about 10 fields of diffrents "tasks" they do. (order picking,
truck loading,trailer unloading, cleaning, .ect.
Each employee that was present that day would end up with a total of 8 hours
of time in 1 or more task.
This would get entered each day for all employees per shift.
1st I am having trouble displaying ALL the employees for the shift on 1 form
with fields for each employee for the tasks.
Maybe I am not thinking of this correctly.

 

[Wayne-I-M]

Hi again

Hmmm...OK now I am more confused.

Why wouldn't a field in the employee table be for what shift the employee
belongs too?

I may have misunderstood –
I assumed that “shift†referred to a given period between 2 time – ie. From
[StartShift] to [EndShift]. Sorry
However if there are more than one person on the “shift†then you really
should create a separate table for this.
As an example (from your post). If John and Sally and Mike are all on the
same “shift†then you will record the shift details in each of their records.
There is no need for this. Also it may be that even though the 3 of them
are on the same shift – they “may†have different work patterns, hourly
rates, or John may not turn up for work one day, etc, etc, etc. As you can
see you need one table for the employees and another one for the “shiftâ€
details.

You will almost certainly be better putting in a linking table (as there are
many employees and many shifts) this is what is known as a many to many
relationship.
Using this method you could record which shifts were worked by which
employees.
Name the table tblLink and have a minimum of 3 fields
LinkID
EmployeeID
ShiftID
TaskID (see last point for this)

Use the relationship page to create the relationships for you.

I am trying to track how many hours each employee worked on each task that day.

For this you will need at start time and end time. In your query (and so
also on your form) you “could†insert the hours work by each member of the
shift (don’t forget that with a relational database you can allocate
different “hours worked†to each employee – of course you “could set as a
default that everyone worked the same BUT data base have a habit of growing
it is better to build-in options that you “may†need in future from the
outset if you can.

So I am trying to make a form that lists all 25-30 employees (visible all at
once) with about 10 fields of different "tasks" they do. (order picking,
truck loading, trailer unloading, cleaning, .ect.

You could use (with your new relational data format) a main form with the
shift details and then a sub-form detailing how worked on that particular
shift. This way if someone fails to turn up for their shift you can easily
note this against their name.
There are many many way of denoting with task an employee has done during
the shift – a couple of common ones would be a dropdown list that you can
select from, or (not as good for many items as it would make the form look
slightly bloated) you could use check boxes.
I would suggest that you could look at a toggle group (if each employee only
performs one task during each shift – as you can see I don’t understand what
you’re trying to do. I spend a great deal of time with clients just chatting
about the methods prior to even starting a new d Base).

Each employee that was present that day would end up with a total of 8 hours of time in 1 or more task.
This would get entered each day for all employees per shift.

Again you could use a StartTime / EndTime for each task (don’t forget – that
at the moment this is all you want but as it’s very simple to do you may as
well build in this functionality at the start).

1st I am having trouble displaying ALL the employees for the shift on 1 form. with fields for each employee for the tasks.

You could use a subform for this with the format set to Datasheet this way
you can display from 1 employee per shift up to …. Well let just say many
thousands.

Maybe I am not thinking of this correctly.

Yes you are. You are looking at a problem and trying to work out the best
way to solve it using the available resources (in this case an access data
base.

Just one point
Assuming that (now or in the future) you may alter the “tasks†that
employees perform during the “shifts†– I would have a tblTask with a link to
the shift table to enable you to allocate the tasks on to the shifts

Have a look at Allan Brown’s excellent tips on all of these subjects
http://allenbrowne.com/tips.html

He also has a school type project here
http://allenbrowne.com/casu-06.html
Which I think will be a great help to you



--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

 

[Sydious]

Hi. Thanks for the help your giving me.
Head is still swimming.
Ok I think I am getting closer. I have a DataSheet style form that lists all
the employees, with a sub form that you can open for each and enter Task
type, Start Time, End time.
How do I go about making it for 1 day at a time?
Some of the people who will be using this are slow learners as far as
computers go. So I want to make this as easy as posible for them.
1 form for them to fill out for each day. All employees listed and Task
type, and times. An employee might do more then 1 task each day. Maybe up to
5 or more tasks.
Think the data sheet view with the subform +'s might be to nuch. Would like
a cleaner style of entry. Again only displaying the 1 day's data at a time.

Hi again

Hmmm...OK now I am more confused.

Why wouldn't a field in the employee table be for what shift the employee
belongs too?

I may have misunderstood –
I assumed that “shift†referred to a given period between 2 time – ie. From
[StartShift] to [EndShift]. Sorry
However if there are more than one person on the “shift†then you really
should create a separate table for this.
As an example (from your post). If John and Sally and Mike are all on the
same “shift†then you will record the shift details in each of their records.
There is no need for this. Also it may be that even though the 3 of them
are on the same shift – they “may†have different work patterns, hourly
rates, or John may not turn up for work one day, etc, etc, etc. As you can
see you need one table for the employees and another one for the “shiftâ€
details.

You will almost certainly be better putting in a linking table (as there are
many employees and many shifts) this is what is known as a many to many
relationship.
Using this method you could record which shifts were worked by which
employees.
Name the table tblLink and have a minimum of 3 fields
LinkID
EmployeeID
ShiftID
TaskID (see last point for this)

Use the relationship page to create the relationships for you.

I am trying to track how many hours each employee worked on each task that day.

For this you will need at start time and end time. In your query (and so
also on your form) you “could†insert the hours work by each member of the
shift (don’t forget that with a relational database you can allocate
different “hours worked†to each employee – of course you “could set as a
default that everyone worked the same BUT data base have a habit of growing
it is better to build-in options that you “may†need in future from the
outset if you can.

So I am trying to make a form that lists all 25-30 employees (visible all at
once) with about 10 fields of different "tasks" they do. (order picking,
truck loading, trailer unloading, cleaning, .ect.

You could use (with your new relational data format) a main form with the
shift details and then a sub-form detailing how worked on that particular
shift. This way if someone fails to turn up for their shift you can easily
note this against their name.
There are many many way of denoting with task an employee has done during
the shift – a couple of common ones would be a dropdown list that you can
select from, or (not as good for many items as it would make the form look
slightly bloated) you could use check boxes.
I would suggest that you could look at a toggle group (if each employee only
performs one task during each shift – as you can see I don’t understand what
you’re trying to do. I spend a great deal of time with clients just chatting
about the methods prior to even starting a new d Base).

Each employee that was present that day would end up with a total of 8 hours of time in 1 or more task.
This would get entered each day for all employees per shift.

Again you could use a StartTime / EndTime for each task (don’t forget – that
at the moment this is all you want but as it’s very simple to do you may as
well build in this functionality at the start).

1st I am having trouble displaying ALL the employees for the shift on 1 form. with fields for each employee for the tasks.

You could use a subform for this with the format set to Datasheet this way
you can display from 1 employee per shift up to …. Well let just say many
thousands.

Maybe I am not thinking of this correctly.

Yes you are. You are looking at a problem and trying to work out the best
way to solve it using the available resources (in this case an access data
base.

Just one point
Assuming that (now or in the future) you may alter the “tasks†that
employees perform during the “shifts†– I would have a tblTask with a link to
the shift table to enable you to allocate the tasks on to the shifts

Have a look at Allan Brown’s excellent tips on all of these subjects
http://allenbrowne.com/tips.html

He also has a school type project here
http://allenbrowne.com/casu-06.html
Which I think will be a great help to you



--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

Hmmm...
OK now I am more confused.
Why wouldn't a field in the employee table be for what shift the employee
belongs too?

I am not trying to track wages. I am trying to trck how many hours each
employee worked on each task that day.
So I am trying to make a form that lists all 25-30 employees (visable all at
once) with about 10 fields of diffrents "tasks" they do. (order picking,
truck loading,trailer unloading, cleaning, .ect.
Each employee that was present that day would end up with a total of 8 hours
of time in 1 or more task.
This would get entered each day for all employees per shift.
1st I am having trouble displaying ALL the employees for the shift on 1 form
with fields for each employee for the tasks.
Maybe I am not thinking of this correctly.

 

[Wayne-I-M]

Datasheets are a little bit weird sometimes to work with if you’re not to
them so (if it was me this is what I’d do).

Create a new query.
Right click the design grid (they blank bit at the top) and select view Show
Table.
Double click the Employees table and the shifts tables.
Close the properties box.
If here is a relationship shown between the tables (a line between the two)
– right click and delete it.
On the Employee Table click and drag the EmployeeID and drag it across to
the EmployeeID that is in the Shift table (if it isn’t then go back to the
table design and put it in)
Bring the fields that you want in your form (when you make it) like Name,
shift, task, etc.
Save the form and close it.

Create a new form. Call it frmShifts. Save.
Click View (at the top of the screen) and then select FieldList
Very Important bit - - - Only bring in the fields that are applicable to the
“shift†not the employees. Add these to the form Header - leave the details
section blank for the moment.
Shift Name.
Shift Date.
Shift Location
Ect.
BUT most importantly you MUST include ShiftID.
Save the form and close it.

Create a new form. Call it frmEmployees
Do the same as you did for the frmShift but this time bring in only those
fields that are applicable to the employees.
All these fields will normally go into the Detail section. Put them in 1
line next to each other. Don't have too much space above or below the fields
in the detail section.

In the “Header Section†you MUST put the ShiftID and the ShiftDate. (you
can add other stuff if you want but the ShiftID is very important). A nice
Lable is a good idea but that one of the things to do after you get the basic
to work.

Again if you don’t have these go back to the table design and put them in.
Right the area outside the design (normally grey) and open the properties
box.
Select Format
Select = Default Format = Continuous Forms
Save the form and close it.

Open the frmShifts in design view
Click view then Toolbox so you can see the toolbox.
Select Subform (click it – it will change color)
Single left click the Details section of your form.
And a box will open – select use and existing form
Select frmEmployees from the list (if there is one).
Click next and select Define My Own (form parent/child)
In both the panes select ShiftID
ClickNext and save (as frmSubEmployees)
Save the form.

When you look at the form now (in form view) the frmShifts will have a
subform showing the employees on that shift.

Next the date.
If it was me designing for inexperienced users I may be temped to use a
calendar control (search this forum for tips on this).
But for now.
Open you frmShifts in design view.
Right the date box and open the properties box.
Go to the data column and put this
Control Source = Your Date Field
Input Mask = 00/00/0000;0;_
Default Value = =Date()

Note that
00/00/0000;0;_ will be used for dates like this 25/12/2006 (this Christmas
day). If you want you can alter this to whatever you find easier.
=Date() will mean that the current date will be shown on new records.

To get the date that want simply open the form in Form View and type in the
date that you want and the employees on shift that day will be shown in the
sub form.

Obviously you need to gig about with the subform size and add fields as you
see fit.

But that is Form and subform making 101.

Enjoy

 

[Sydious]

This may be a dumb question.....
Why does the Shifts Table get and EmployeeID?
Not sure I understand. The shifts table holds no employee info.

 

[Sydious]

Also...am I supposed to have a Table for EACH shift? Or a table for all 3
shifts.

 

[Wayne-I-M]

This may be a dumb question.....

Why does the Shifts Table get and EmployeeID?
Not sure I understand. The shifts table holds no employee info.

The shift table contains all the information about "shifts" and the
employeeID. You use this "linking field" so that you can allocate employees
on to various shifts.

In all relational tables there has to be a "key" that will link the tables
together. Normally this is the primary field (but it doesn't have to be -
it's just less likely to mess up if it is) which is a unique identifier - as
there may be more than 1 John Smith (normally an autonumber field so you can
ensure there are no duplications).

 

[Wayne-I-M]

No you have a table ( tblShifts) and each shift gets it's own record (times,
tasks, etc) in this table with a unique (autonumber) primary key so you can
call up the information regarding that particular shift.

This also allows you (with your employee linking field) to allocate
employees on to a specific shift.


--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

 

[Wayne-I-M]

Of course it is possible for me (and many other people) to fully design your
database via e mail and other remote medium BUT – only you “fully†understand
what is needed – so I think that if you were to try and understand the basics
of database design , it would (in a very short time) prove useful to you and
will enable you to produce the results you want.

You don’t really need to pay for an access database if you are willing to
invest the time (and a bit of effort) to enable you understand
“how-it-all-worksâ€. Basically computer programmes, like everything else, do
take a while to master, but, once you have gained the level of skill required
to enable you to undertake the tasks needed/wanted (by yourself, your boss,
your club, etc) you will be able to “add†functionality to improve the
process – ie. at the moment you only want a table for employees and another
for shifts but in the future you may wish to add another for rates of pay for
different task within the shift ??. I understand that “at the moment†you
don’t need this BUT - - - in the future… you never know.

Ain’t that what life is all about. Also you will be able to workout “for
yourself†how to do things with you database. If you have problems there is
always this forum for tips.

I think that it would be beneficial for you (before you start your database)
to check out these 2 pages on the main MS site.

http://support.microsoft.com/kb/283698/
Which gives an insight into the basics of relationships in a database?

And for a broader view this page would be very useful.
http://support.microsoft.com/kb/289533/en-us

Good Luck

 

[Sydious]

Wayne,
I want to thank you for ALL the help you have given me. I have learned alot
already from you. One of the IT guys at my work gave me the Access 2003 Bible
and I have been nose deep in it for about 2 weeks now.

Your point is exactly what I am trying to do. I understand the "editor" part
of access fairly good so far. I struggle with the Relationships part right
now. I understand the basics kind of but when I goto do it, I fall short.
Of course the idea I have a grand. I know I need to take baby steps with
this project. I have a lot of Info I want to store and call upon in my job. I
have been put in charge of collecting Metrics and other info so a Database
make sense. I have a bunch of spred sheets I use now and I keepmakeing more
and more. Saveing all these spred sheets is becoming quite a large amount of
files.

I will be looking at the links you provided and maybe something more will
click. The more I try things out from example the more I learn.

Again THANKS for the help you have provided me thus far!