employee list vs training courses completed - is their a query that will do this?

[Mariad]

Hi

I'm very new to access 2003 and learning about it slowly.

I've got a few tables set up one with an entire list of staff, and two other tables for two training courses that we run listing names of delegates and dates attended. I want access to produce an entire staff list and filter in training each member of staff has completed even if they haven''t completed any.

Am I asking too much?

 

[Steve R.]

It can be done, but you also need to demonstrate that you have made an effort at searching out a solution. You will need to research how to formulate an SQL statement. Look-up "sql statement" in Access (2007) help. Below is a simple example from Access help. The code below would have to be tweaked to match what you are trying to accomplish.

SELECT Last_Name
FROM Contacts
WHERE First_Name = 'Mary';

 

[MSAccessRookie]

Hi

I'm very new to access 2003 and learning about it slowly.

I've got a few tables set up one with an entire list of staff, and two other tables for two training courses that we run listing names of delegates and dates attended. I want access to produce an entire staff list and filter in training each member of staff has completed even if they haven''t completed any.

Am I asking too much?

When I read the statement marked in RED above, I could not help but think that your design might not be as normalized as it could be.

• Is there a need for each training course to have its own table?
• What would happen if next month (or next year) management requested to add two more training courses. Would they each get their own table? If so, this is not normalized, and not the best approach.

A better approach might be to have the following three tables (and any others you find that you require as well)

1. A Table of People and information about them (Primary Key - PersonID)
2. A Table of Courses and the information describing them (Primary Key - CourseID)
3. A Junction Table For Courses and People that reflects whether or not a person took a course. (Use the Primary Keys from the other Tables as Foreign Keys)

As suggested by Steve R., you will need to do some research. In addition to what he suggested, consider looking into the following as well:

• Primary Keys
• Foreign Keys
• Junction Tables
• Normalization