I need some help

[Edward]

Hi everybody,
I’m new in Access 2007 so sorry if Im asking a very basic question. I want
to create a database that will keep track of some training sessions and
people who have attended the class. I have created a table which keeps the
info for people, a second table which holds the info for each training class
( date, type, instructor,…). The idea is to create people record and then
schedule them in a particular training class, Should I create a third table
which combines the info in people table and session’s table? Each class might
have several attendees. I would appreciate if someone gives me some hint to
start with and any sources that I can use.

 

[Gina Whipp]

You will need a table which stores the PeopleID (not the whole record) and
the TrainingClassID. However, you might want to add additional tables,
unless you are sure the Instructor is never going to change or the class is
offered more then once during the day.

I am also noting that you re putting a date in the TrainingClass table.
Well, does that mean Access 101 offered today gets a date of 12.29.2008 and
then you enter Access 101 that gets offered tomorrow is entered again with a
date of 12.30.2008? You probably don't want to store date in the table with
the Class. For ideas on table set-up (the MOST IMPORTANT part of any good
database) take a look at the free databases from Microsoft:

http://office.microsoft.com/en-us/templates/CT101428241033.aspx

There are one or two there that might help you on your way...

 

[Armen Stein]

Hi everybody,
I’m new in Access 2007 so sorry if Im asking a very basic question. I want
to create a database that will keep track of some training sessions and
people who have attended the class. I have created a table which keeps the
info for people, a second table which holds the info for each training class
( date, type, instructor,…). The idea is to create people record and then
schedule them in a particular training class, Should I create a third table
which combines the info in people table and session’s table? Each class might
have several attendees. I would appreciate if someone gives me some hint to
start with and any sources that I can use.

Hi Edward,

Yes, the third table keeps track of the relationship between a session
and a person. This type of table is often referred to as a
"many-to-many", "cross-reference" or "joining" table. You might call
it Enrollment, or SessionPerson.

In that table, you'll need the primary keys from the session and
person tables, plus any fields about that enrollment itself, like
enrollment date, etc. You should not store any other duplicate data
from the person or session tables - just the primary keys.

You may also want to look at Access 207 templates - I believe there is
at least one available that covers students and classes that you may
be able to adapt.

If you would like to learn more about database design, a good book to
get started with is Database Design for Mere Mortals by Hernandez.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Edward]

Gina,
I got the idea of having a third table which has a combined primary key (
classID+PeopleID) but i didnt get the part which you mentioned I need to make
sure instructore doesn't change or we will have more than one class per day?
in fact we have two instructors who can be assigned to any class and we might
have more than one class per day so for additional reports in a monthly bases
I need to separate those classes by date and even time , also there some
variations in the material so I need a field in class table which keeps that
info as well.
--
Best regards,
Edward

You will need a table which stores the PeopleID (not the whole record) and
the TrainingClassID. However, you might want to add additional tables,
unless you are sure the Instructor is never going to change or the class is
offered more then once during the day.

I am also noting that you re putting a date in the TrainingClass table.
Well, does that mean Access 101 offered today gets a date of 12.29.2008 and
then you enter Access 101 that gets offered tomorrow is entered again with a
date of 12.30.2008? You probably don't want to store date in the table with
the Class. For ideas on table set-up (the MOST IMPORTANT part of any good
database) take a look at the free databases from Microsoft:

http://office.microsoft.com/en-us/templates/CT101428241033.aspx

There are one or two there that might help you on your way...