HELP!

[smudgedwhiteink]

Hello,

I am quite new to access and presently creating an employee database. I
have got the basis of that I wanted to acheive down; however, I have hit a
rock. I need to be able to track absences (AWOL, SICK, LWOP, BEREAVEMENT,
WCB) for each employee.

I have it set up quite simple, just in a form, where I can enter all
employee info, and have the employee number as the primary key... for future
use (when I actually figure this program out)

Any suggestions on how I might do this???

Thanks for any help that is offered,
Accessivly confused.

 

[pietlinden]

Hello,

I am quite new to access and presently creating an employee database.  I
have got the basis of that I wanted to acheive down; however, I have hit a
rock.  I need to be able to track absences (AWOL, SICK, LWOP, BEREAVEMENT,
WCB) for each employee.  

I have it set up quite simple, just in a form, where I can enter all
employee info, and have the employee number as the primary key... for future
use (when I actually figure this program out)

Any suggestions on how I might do this???

Thanks for any help that is offered,
Accessivly confused.

I would create a single table for the absences.

(EmployeeID, DateStart, DateEnd, AbsenceType)

Either that or
(EmployeeID (PK1), DateAbsent (PK2), AbsenceType)

then counting/grouping is simple. What I do when I start to design
things like this is ask "how am I going to search/summarize this
information? If my design doesn't lend itself readily to it, I know
I've done something wrong.

 

[smudgedwhiteink]

I would create a single table for the absences.

(EmployeeID, DateStart, DateEnd, AbsenceType)

Either that or
(EmployeeID (PK1), DateAbsent (PK2), AbsenceType)

then counting/grouping is simple. What I do when I start to design
things like this is ask "how am I going to search/summarize this
information? If my design doesn't lend itself readily to it, I know
I've done something wrong.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - -
Okay, that seems reasonable. I just curious, (I know I am being stubborn
here) but, is there any way to add the information right into the form that I
have created for all the other information for each employee. SO - If i
search for the form for say employee GAUV - then, I get all absences, AND all
employee info?

 

[Brian Smith]

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
- - - - - - - - - - - - - - -
Okay, that seems reasonable. I just curious, (I know I am being stubborn
here) but, is there any way to add the information right into the form
that I
have created for all the other information for each employee. SO - If i
search for the form for say employee GAUV - then, I get all absences, AND
all
employee info?

Create a subform for your absence info and put that form on to your main
employee form. The two forms will probably be linked by EmployeeID if that
is the field name you are using to keep track of employees. I'd also create
the subform as a continuous form so you can track as many absences for each
employee as needed.

Brian

 

[Tom Wickerath]

You can display this information in a form that is shown as a subform on your
main form. What you definately do not want to do is try to add all the
employee attributes (name, address, city, state, zip, hiredate, job code,
etc.) and their leave information into one table. That would be a
denormalized "Access spreadsheet", which will be very difficult to work with
in the future. Since you indicated that you are new to Access, I'm wondering
if you have spent any time learning about database normalization. In this
case, you would have a one-to-many (1:M) relationship, ie. "An employee can
have many leaves of absence". In fact, you have a many-to-many (N:M)
relationship between employees and types of leave:

1:M An employee can take (zero to) many types of leave
Example: Sick leave, Vacation leave, Bereavement leave, AWOL and WCB
(whatever that is)

and

1:M A type of leave can apply to many employees.

The N:M relationship being made by joining two 1:M relationships with a
third "join" table.

If this discussion of relationships and join tables is foreign to you, then
you need to spend some time learning about database design. Here is a link to
get you started:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

If nothing else, make sure to read the first two papers by Michael
Hernandez, who is the author of the book "Database Design for Mere Mortals".
Here is a DB design for tracking employee leave information, that you might
find helpful:

http://www.databaseanswers.org/data_models/employees_leave_record_keeping/index.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[B Wilson]

You can display this information in a form that is shown as a subform on your
main form. What you definately do not want to do is try to add all the
employee attributes (name, address, city, state, zip, hiredate, job code,
etc.) and their leave information into one table. That would be a
denormalized "Access spreadsheet", which will be very difficult to work with
in the future. Since you indicated that you are new to Access, I'm wondering
if you have spent any time learning about database normalization. In this
case, you would have a one-to-many (1:M) relationship, ie. "An employee can
have many leaves of absence". In fact, you have a many-to-many (N:M)
relationship between employees and types of leave:

1:M An employee can take (zero to) many types of leave
Example: Sick leave, Vacation leave, Bereavement leave, AWOL and WCB
(whatever that is)

and

1:M A type of leave can apply to many employees.

The N:M relationship being made by joining two 1:M relationships with a
third "join" table.

If this discussion of relationships and join tables is foreign to you, then
you need to spend some time learning about database design. Here is a link to
get you started:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

If nothing else, make sure to read the first two papers by Michael
Hernandez, who is the author of the book "Database Design for Mere Mortals".
Here is a DB design for tracking employee leave information, that you might
find helpful:

http://www.databaseanswers.org/data_models/employees_leave_record_keeping/index.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________