Access Table Help

[mndpy]

I am working on a database that displays employee information. I have tables
that include:
Employees
Absence / Tardy
Checklists
Special Events

For each of these tables the primary key is their ID# and each table has all
the ID#'s in.

I have two other tables that are:
Inservices 1
Inservices 2

They also include the primary key ID#
These two tables only include specific people assigned to certain
departments.

I have the relationships a 1-1 for all the tables back to the Employees.

My problem is that when I combine data when making a query from one of the
all tables and one of the some table my data does not show up.

So I have to have something wrong. I can't see to figure it out. So any help
that anyone can give me would be appreciated.

Thank!

 

[John W. Vinson]

I am working on a database that displays employee information. I have tables
that include:
Employees
Absence / Tardy
Checklists
Special Events

For each of these tables the primary key is their ID# and each table has all
the ID#'s in.

What's the datatype of ID#?

I have two other tables that are:
Inservices 1
Inservices 2

What is in these tables? It's almost surely an error to have two (apparently)
identical tables.

They also include the primary key ID#
These two tables only include specific people assigned to certain
departments.

I have the relationships a 1-1 for all the tables back to the Employees.

BEEEEP!!! That's your problem.

That would mean the each employee can have one, and only one, Absence/Tardy,
ever. Surely you don't fire an employee for being a minute late!

One to one relationships are VERY RARE. If you're not "Subclassing" or using
"Table driven field level security" or a couple of other arcane techniques,
you almost surely want one to many relationships, not one to one.

My problem is that when I combine data when making a query from one of the
all tables and one of the some table my data does not show up.

Not sure what you mean by "the all tables" or "the some tables", but your one
to one relationship is surely the cause of the problem.

So I have to have something wrong. I can't see to figure it out. So any help
that anyone can give me would be appreciated.

I think you need to rethink how your relationships work! If each Employee can
have zero, one, or many Absences, then you need an Absences_Tardy (don't use /
in fieldnames) table with its own primary key (perhaps an autonumber), and a
*foreign key* field, an employee ID linked one-to-many to the Employees table.
You would use a Form based on Employees with a Subform based on Absences_Tardy
to enter data.

If each employee may be involved in zero, one, or many Special Events, and
each Special Event may be attended by zero, one, or many Employees, then you
have a Many to Many relationship (not a one to one relationship). You will
need a new table. A structure like this may work:

Employees
EmployeeID (again, don't use # in fieldnames, it's a date delimiter)
LastName
FirstName
<other biographical data>

SpecialEvents (it's best not to use blanks either... sorry... <g>)
EventID <Primary Key>
EventName
EventDate
<other info about the event>

EventAttendance
EmployeeID <link to Employees, who attended>
EventID <link to SpecialEvents, what did they attend>
<any info about THIS employee's attendance at THIS event>

 

[mndpy]

My tables consist of the following information:

Employees Table - 34 Different items related to their personal information
and hire information

Absence / Tardy - includes 15 absence reports and 15 tardy reports that can
be filled out for each employee

Special Events - Includes 24 different yes/no categories to know of the
employee participated in it.

The Checklist Table has 75 items yes/no on if someone is trained in that
particular area.

The inservices 1 and 2 are table that only include certain people from the
employees table. They do not include everyone and do not include the same
people.

So any of help on how to relate them would be great. Thanks!

 

[John W. Vinson]

My tables consist of the following information:

Employees Table - 34 Different items related to their personal information
and hire information

Absence / Tardy - includes 15 absence reports and 15 tardy reports that can
be filled out for each employee

Special Events - Includes 24 different yes/no categories to know of the
employee participated in it.

The Checklist Table has 75 items yes/no on if someone is trained in that
particular area.

The inservices 1 and 2 are table that only include certain people from the
employees table. They do not include everyone and do not include the same
people.

So any of help on how to relate them would be great. Thanks!

You've got a start, but you're not quite there! I'm not quite clear what's in
some of these tables. What are the actual fieldnames, datatypes and contents
of the fields in Absence/Tardy (and please DO change the name, the slash
*will* cause problems).

Your Special Events table is a decent spreadsheet design but it's not good for
a relational table. "Fields are expensive, records are cheap" - if you have
one field per event, then whenever you have a 25th event, you will need to
change the structure of your table, of your form, of all your queries, of all
your reports... OUCH! The Checklist table is even worse (though the same
problem).

If you have a many to many relationship, such as each Employee being trained
in many Areas, and each Area having many Employees trained, you need three
tables:

Employees
EmployeeID
LastName
FirstName
<etc., biographical info you already have>

TrainingAreas
AreaID <Primary Key, autonumber probably>
Area <Text description of the training area>
<other info about this training area, e.g. prerequisites>

EmployeeTraining
EmployeeID <link to Employees, who was trained>
AreaID <for which area were they trained>
<perhaps trainingdate, satisfactory/unsatisfactory completion, comments>

You'ld use the same technique with Special Events as I posted previously.

If you're copying the data in Employees into the Inservice tables... DON'T.
Data should be stored once, and once only; storing it redundantly in a second
table is asking for a heap of trouble! Perhaps you could explain a bit more
what an "inservice" means.

If you wish, take a look at some of the resources below. Crystal's tutorial is
a good place to start:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials