Keeping Old Data

[Pat]

Hello everyone,

I am completely new to Access, but as a project for work I am attempting to
create a way to keep track of when we have to cancel our employees.

The information I need to include is:

Last Name
First Name
Skill Set
Shift (They work)
Date (That we cancel)
VLC (Voluntary low census - they choose to go home)
MLC (We make them go home)
SB (We send them home with the option of calling them back)

What is very important is that I need to somehow keep all the dates matched
with whether they were VLC, MLC, or SB that we canceled them. Also the form
created needs to autosort them according to date canceled so that the most
recent cancellation is found at the end of the list.

My question - is this possible in Access. From everything I have read so far
on it, it seems I can create a table with my data, create queries to pull up
the data I need (say a dayshift aide), and forms for staffers to enter the
date of cancellation and type - but I don't quite see how to keep the old
data listed along with the new.

Any suggestions - please remember I am very new trying to teach myself how
to use this program. Also I have Access 2003.

Thanks in advance

Pat

 

[Tom Wickerath]

Hi Pat,

Yes, Access can do all of what you are asking. You might want to start the
process by checking out Access MVP Crystal's set of tutorials, available here:

http://www.accessmvp.com/Strive4Peace/Index.htm

Do your employees have multiple skills and/or shifts available? If not, then
you can have this data in an employees table.

On the Date, this sounds like the date of some event that can repeat many
times (a workshift?). You will most likely want this data in a related child
table. This new table includes the same identifier field that you will need
to have in the Employees table. We call this field a primary key in the
one-side table; it is indexed uniquely and cannot be null. In the child
table, we call this a foreign key; it is allowed to repeat in this table.

On the Census data (VLC, MLC, SB), you will likely want a table to hold this
data as well. Something like this:

tblCensus
pkCensus (Set as primary key field)
CensusDesc (Text description)

You can use either an Autonumber for the pkCensus field or a text field
(your choice). If you use a text field, then you would enter the three unique
values VLC, MLC and SB into this field, along with the descriptive text in
the CensusDesc field.

A work events or work shifts (whichever is applicable) table would look
something like this (this is the child table I mentioned earlier)

tblWorkEvents
pkWorkEventID (Autonumber / Set as primary key)
EventDate (Date of applicable event)
fkEmployeeID (Employee foreign key)
fkCensusID (Census foreign key)

Note:
Date is considered a reserved word in Access. You should avoid using any
reserved words for things that you assign a name to in Access (fields,
tables, queries, forms, reports, macros, modules, along with controls in
forms and reports). Please visit this site briefly:

http://www.accessmvp.com/TWickerath/

Download a copy of my Access Links Word document. Explore the various links
on the first page for Special Characters, Reserved Words, and Database Design.

Finally, your employees table would look something like this, *assuming*
that an employee has only one skill and can work only one shift:

tblEmployees
pkEmployeeID (Employee primary key)
EmpFirstName
EmpLastName
EmpSkill
EmpShift

You might even want to break the Employee Skill data (EmpSkill) out to a
separate lookup table, especially if there are many different skill codes
and/or skill descriptions, or if an employee needs to be certified annually
in a particular skill.

Off hand, it appears as if the simplist design is going to involve three
tables, but it might require more, depending on the business rules in place.


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