how to find who has and who has not taken "food poision" training?

[Chocolate Cookie]

Hi

I have 2 tables (a) employee name / ID / work_center and (b) ee_ID /
course_taken / date

I link the 2 with ID and want to generate a query output that will show
training status of any given course such as "list everybody (once only) and
show if he has or has not taken the food poision training?

Employee_table

John 001 kitchen
Tony 002 kitchen
Dave 003 waiter
Mary 004 waiter
Boby 005 boss

course

001 food_poinsion 2004/01/19
001 Org_behavior 2004/03/27
002 Org_behavior 2004/03/27
003 food_poinsion 2004/01/19
004 Org_behavior 2003/09/30
005 Accounting 2004/04/16

desired report:

John food_poision
Tony
Dave food_poision
Mary
Boby

so I can schedule Tony Mary and Boby for training.

Please help to show the blanks.

I don't know anything about SQL or Visual Basic, I am sorry.

 

[Allen Browne]

To track the data you are talking about you need these tables at a minimum.

Employee table: one record for each person
EmployeeID AutoNumber primary key (PK)
Surname Text etc.

Unit table: one record for each kind of unit offered.
UnitID Text (3) PK.
UnitName Text Name of this unit

Course: one record for each time a course is run.
CourseID AutoNumber PK
UnitID Text (3) foreign key (FK) to Unit.UnitID
StartDate Date/Time date this course begins

CourseDone: one record for each person in each course.
CourseID Number FK to Course.CourseID
EmployeeID Number FK to Employee.EmployeeID


You can now create a query based on the Course and CourseDone tables.
Depress the Total icon on the toolbar (upper Sigma icon).
Group By the EmployeeID field, and the CourseID field.
Optionally, choose Max of the StartDate field.
Save the query with the name "qryCourseDone".

Now create another query into your Employee table and the Unit table. There
will be no line joining the 2 tables. This gives you every possible
combination of Emplyee and Unit. Save with the name "qryEmployeeUnit".

Now create another query that uses both queries as the input "tables". You
should see 2 lines joining the 2 "tables" in the upper pane of the query
design window, on the EmployeeID fields, and the UnitID fields. Double-click
these lines. Access opens a dialog with 3 options. Choose the one that says:
All records from qryEmployeeUnit, and any records from ...
This query will show every person and every unit, with the last date they
did the course, and the date will be blank if they never did the course.
Save this query.

Use the 3rd query as the RecordSource for your report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[John Spencer (MVP)]

Use the Exists clause.

SELECT E.*,
Exists(SELECT * FROM Course
WHERE Course.CourseTaken = "Food_Poinsion") As CourseTaken
FROM Employee_Table as E