A query based on information in previous queries

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: On-line Liz (anonymous_at_discussions.microsoft.com)
Date: 05/13/04 

Date: Thu, 13 May 2004 04:56:02 -0700

There’s a fair bit to read here but I’d really appreciate someone’s help.

I am building a database to keep check that employees are attending their essential annual training safety courses. I have the following tables:

Employee table: [IDNo], [ProfessionID], [Surname], [Forename], [Trust/University], [EmailAddress]

Profession codes: [ProfessionID], [Profession]

Courses usually taken: [IDNo], [CourseUTID]

Course details: [CourseID], [CourseName], [CourseDate]

Course names and group: [CourseName], [CourseGroup]

Course taken or booked: [IDNo], [CourseID], [CompletedCourse]

All the employees courses are listed/entered into the table ‘Course taken or booked’, whether they be essential, desirable or optional [CourseGroup]. The details of the courses + dates are in table ‘Course details’ to save typing the details in all the time for each employee.

I need to keep check that the employees have done their essential courses in the last year. Firstly, I needed to do a query to show me which essential courses the employees must do. (I used table ‘Courses usually taken’ as these list the essential courses only that they must take) – as follows:

Query 1: ‘essential courses usually taken’

SELECT [Employee table].Forename, [Employee table].Surname, [Courses usually taken].CourseUTID
FROM [Employee table] INNER JOIN ([course names and group] INNER JOIN [Courses usually taken] ON [course names and group].CourseName = [Courses usually taken].CourseUTID) ON [Employee table].IDNo = [Courses usually taken].IDNo
GROUP BY [Employee table].Forename, [Employee table].Surname, [Courses usually taken].CourseUTID
ORDER BY [Employee table].Surname;

Secondly I then needed to do a query to give me a list of the essential courses actually done in the last year ([CourseGroup] is criteria set to ‘essential’ and the [CourseDate] is also set) so that I can compare with Query 1, which essential courses each employee hasn’t done:

Query 2: ‘essential courses in last year’

SELECT [Employee table].Forename, [Employee table].Surname, [course details].CourseName, [course details].CourseDate, [course names and group].CourseGroup
FROM [Employee table] INNER JOIN ([course names and group] INNER JOIN ([course details] INNER JOIN [Course taken or booked] ON [course details].CourseID = [Course taken or booked].CourseID) ON [course names and group].CourseName = [course details].CourseName) ON [Employee table].IDNo = [Course taken or booked].IDNo
GROUP BY [Employee table].Forename, [Employee table].Surname, [course details].CourseName, [course details].CourseDate, [course names and group].CourseGroup, [Employee table].IDNo, [Course taken or booked].CourseID
HAVING ((([course details].CourseDate)>Date()-365 And ([course details].CourseDate) Not Between Date() And DateAdd("m",50,Date())) AND (([course names and group].CourseGroup)="Essential"));

To save manually comparing the 2 lists from the 2 queries above, I would really like a third query to automatically give me a list of the annual essential courses which the employees HAVE NOT yet taken. For example, query 1 would show that John Smith must take the courses, Fire, Manual Handling and Food Hygiene. Query 2 lists only Fire and the date he took it. I want the new query to give his name and list Manual Handling and Food Hygiene next to his name, as he has not taken them. So whatever courses are not listed next to someone’s name in query2 that should be (as listed in query 1), I want listed in the new query (3).

NB I have never really written any SQL as I’ve always dragged my fields from the tables in design view into the columns and got by like this. I would appreciate it so much if someone could provide me with some help/a step by step solution to give me my third query.

Many thanks,
Liz.

Relevant Pages

  • Re: Need query to find employees who have not had required trainin
    ... The first unmatched query works great. ... I think that I might not be building the ALL employees at once, ... You can build a query of all courses taken an employee, ...
    (microsoft.public.access.queries)
  • Re: Need query to find employees who have not had required trainin
    ... "Jeff Boyce" wrote: ... You can build a query of all courses taken an employee, ... Create an unmatched query... ... If you need to do this for ALL employees at once, first create a list of all ...
    (microsoft.public.access.queries)
  • Re: Need query to find employees who have not had required training
    ... You can build a query that lists all course that are mandatory, ... You can build a query of all courses taken an employee, ... If you need to do this for ALL employees at once, first create a list of all ...
    (microsoft.public.access.queries)
  • Re: SQL statement solution needed for query to save manually comparing 2 others.
    ... To ascertain what courses they must take run a simple query to obtain ... tblCourseReq.EmployeeID FROM tblEmployee INNER JOIN ... Again to ascertain what courses they have completed run a similar query (see ... > I am building a database to keep check that employees are attending their ...
    (microsoft.public.access.modulesdaovba)
  • complex select query
    ... I am creating a database that tracks when hospital employees take certain ... required education courses. ... I have a query currently that lists what ...
    (microsoft.public.access.queries)