How do I set up access training database, queries, etc?

[Guest]

I need an access table from which I can query a specific course name and
other relevant training course information and/or determine which employees
have requested a particular course or be able to query an employee and see
all the courses that an employee has requested.

I have built an Employee Table and a Course Table. I couldn’t figure out a
way to put in an employee who was taking more than one course. So I thought I
might be able to build a Training Request Table with three columns - a
primary key, Training Request ID (auto numbered), Employee ID with a lookup,
and Course ID.

I think this is probably simple if you know what you are doing. But I don’t.
I have spent weeks trying to figure this out and nobody I know knows anything
about access.

 

[John Vinson]

On Mon, 28 Feb 2005 18:03:04 -0800, "NOT ACCESSABLE" <NOT

I need an access table from which I can query a specific course name and
other relevant training course information and/or determine which employees
have requested a particular course or be able to query an employee and see
all the courses that an employee has requested.

I have built an Employee Table and a Course Table. I couldn’t figure out a
way to put in an employee who was taking more than one course. So I thought I
might be able to build a Training Request Table with three columns - a
primary key, Training Request ID (auto numbered), Employee ID with a lookup,
and Course ID.

I think this is probably simple if you know what you are doing. But I don’t.
I have spent weeks trying to figure this out and nobody I know knows anything
about access.

You're on the right track nonetheless! Yes, you do need the third
table.

The trick is to use a Form - not a table datasheet - to do the data
entry. Table datasheets are of VERY limited functionality; they're
designed to efficiently store data, *not* to display or enter it.

You should have three tables:

Employees
EmployeeID Primary Key, autonumber or assigned employee number
LastName
FirstName
<other bio data>

Courses
CourseID Primary Key, autonumber or assigned unique class #
CourseTitle
<other info about the course, e.g. course dates, etc.>

Enrollment
CourseID Long Integer
EmployeeID Long Integer
<any needed info about THIS employee in THIS class>

CourseID and EmployeeID would be a joint two-field Primary Key (select
the two fields in table design view and click the Key icon). You won't
need an extra key.

You can then create a Form based on either Employees (if you want to
select an employee and assign them to several courses) or on Courses
(if you want to look at a course and pick a number of employees to
enroll into it). On the Form you would put a Subform; if the form is
based on Courses, you'ld set the Master and Child Link Field
properties of the subform to CourseID. On the subform you'ld put a
Combo Box bound to EmployeeID (but displaying the employee's name).

Reverse the procedure on the subform if it's on the Employees form.

John W. Vinson[MVP]