SQL statement solution needed for query to save manually comparing 2 others.

[On-line Liz]

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].CourseUTI
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].IDN
GROUP BY [Employee table].Forename, [Employee table].Surname, [Courses usually taken].CourseUTI
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].CourseGrou
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].IDN
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].CourseI
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"))

My problem!! 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 in the last year. For example, query 1 would show that John Smith must take the essential courses, Fire, Manual Handling and Food Hygiene. Query 2 lists only Fire and the date he took it, as that's all he's taken in his essential courses for the last year. 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 essential courses are not listed next to someone’s name in query2 (but listed in query 1), I want listed in the new query (3). I don't want listed the essential courses employees have already taken in the last year but HAVEN'T TAKEN within the last year. NB Each employees' essential courses are different to each other - depends on their profession

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. I would really like a one- SQL statement solution if possible

Many thanks,
Liz.

 

[Justin]

I went through your message pretty quick, but I think I know what you're looking for. Actually, its realy simple. You don't have to bother with writing SQL, unless you are looking for flexibility in your reporting. In either case, use the Query Wizard and select Find Unmatched Query. Then, follow the instructions....If you need to use SQL for a .Execute command or something, then copy and paste the SQL created by the query wizard into VB and edit it to get what you're looking for. But, then again, it sounds like you don't need flexibility in this reporting. So, you can just stick with the query wizard and you'll be fine.

Hope this helps.

 

[Rob]

Hi Liz,

I am building a database along the same lines (training management) and have
come across and solved a similar problem (after help from the queries
group). I do not profess to being a wiz at Access but will assist where I
can.

To help I will provide a quick overview of how I put the basics of mine
together (I am happy for any comment here

tblEmployee - EmployeeID, Surname, FirstName
tblCourse - CourseID, Description
tblCourseReq - EmployeeID, CourseID : Defines the courses that an employee
must take
tblCourseComp - AttemptID, EmployeeID, CourseID, Date, Result : Need an
attempt ID to allow the person to take the course more than once

To ascertain what courses they must take run a simple query to obtain
CourseDescription and Surname by EmployeeID (see below)

SELECT tblCourse.CourseDescription, tblEmployee.Surname,
tblCourseReq.CourseID, tblCourseReq.EmployeeID FROM tblEmployee INNER JOIN
(tblCourse INNER JOIN tblCourseReq ON tblCourse.CourseID =
tblCourseReq.CourseID) ON tblEmployee.EmployeeID = tblCourseReq.EmployeeID
WHERE (((tblCourseReq.EmployeeID)=[Enter the employee number]));

Again to ascertain what courses they have completed run a similar query (see
below)

SELECT tblEmployee.Surname, tblCourse.CourseDescription, tblCourseComp.Date,
tblCourseComp.CourseID
FROM tblEmployee INNER JOIN (tblCourse INNER JOIN tblCourseComp ON
tblCourse.CourseID = tblCourseComp.CourseID) ON tblEmployee.EmployeeID =
tblCourseComp.EmployeeID
WHERE (((tblCourseComp.Date) Between [First] And [Second]) AND
((tblCourseComp.Result)=Yes));

Now to gather the information that you require you need to do a LEFT OUTER
JOIN. The easiest way to do this in Access is to use the Unmatched Query
wizard as suggested by Justin. The query you want (based on the table
description above is) :

SELECT DISTINCTROW tblReqCourse.CourseDescription, tblReqCourse.Surname
FROM tblReqCourse LEFT JOIN tblCompCourse ON tblReqCourse.CourseID =
tblCompCourse.CourseID
WHERE (((tblCompCourse.CourseID) Is Null));

I have put together a quick representation of the above information and it
works (Drop me an email if u want a copy). Whilst being bare it seems to
cover all your requirements,

I hope that this helps,

Cheers
Rob

robannie SHIFT+2 webone fullstop com circle au

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"));

My problem!! 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 in the
last year. For example, query 1 would show that John Smith must take the
essential courses, Fire, Manual Handling and Food Hygiene. Query 2 lists
only Fire and the date he took it, as that's all he's taken in his essential
courses for the last year. 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 essential courses are not listed next to someone's name in
query2 (but listed in query 1), I want listed in the new query (3). I don't
want listed the essential courses employees have already taken in the last
year but HAVEN'T TAKEN within the last year. NB Each employees' essential
courses are different to each other - depends on their profession.

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. I would really like a one-
SQL statement solution if possible?

 

[on-line liz]

Hi Rob

Thanks for your response. I emailed you recently but not sure if you got it or not as I don't know if I typed your address correctly. Could you email me with a copy of your database as offered. That'd be great

My email

Easmith2@(take this out with the brackets)aol.co

Many thanks

Liz.