A query based on information in previous queries

[Guest]

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

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

 

[Jeff Boyce]

Liz

Consider letting Access do this for you...

If you click on the New Query selection in the New Object dropdown toolbar
button, you can select the "unmatched" query. The wizard will walk you
through setting up a query that looks for rows from one source (one of your
queries) that are not in another source (your other query).

 

[Guest]

Hi Jeff

Thanks very much for answering my query. I’m not having much luck though. I’ve tried a few times with the umatched query wizard as you suggested but it doesn’t bring up what I need. Do you think perhaps because I want to specifically bring up the names of individuals along with just the names of the essential courses that they haven’t done within the last year (specified on [date] field criteria, only in query 2) is why it won’t work. It seems as though I need to do more but I don’t know what! I’m thinking all of the time, but I haven’t come across solving this problem before

Can you help me again? I’d be most grateful

Kind regards
Liz
P.s what does MVP stand for? I’m quite interested

 

[Jeff Boyce]

Liz

You haven't described what your used of the unmatched query wizard DOES
produce.

I've found the following approach to work when what I'm trying to do gets

build the minimum query (fewest fields possible) that returns the IDs you need from your first table
build the minimum query that returns the IDs from the second table
run the "unmatched" wizard, using those first two queries
create a ?!fourth!? query, based on the output of the unmatched query,

and joined to the table(s) that contain "the rest of the information" you
need.

I'm sure there's a technical term, but I call this "chaining". I'm also
fairly sure that someone, somewhere out there can create a single SQL
statement that does all this ... but I tend to figure out a reasonably
effective solution and move on. If you'd like an "elegant" one-SQL
statement solution, you probably need to re-post, and ask for that
explicitly.

Microsoft has a program to acknowledge folks who provide support to users of
their products. This is the website for more information about the Most
Valuable Professional (MVP) award:


Regards

Jeff Boyce
<Access MVP>

 

[Guest]

Hi Jeff

Thanks again for your response. You are probably right in what you are saying but I'm probably going wrong somewhere. My unmatched query returned just one course for a group of employees instead of what I wanted. I think when things go wrong like this, it is probably because of the situation - trying to explain something over emails/postings when really, one needs to see the actual database and work together like that. I think I'm probably going wrong with the wizard somewhere. I think I'll take your advice + re-post the problem for an SQL statement

Kind regards and thanks again for your hel

Liz

----- Jeff Boyce wrote: ----

Li

You haven't described what your used of the unmatched query wizard DOE
produce

I've found the following approach to work when what I'm trying to do get

build the minimum query that returns the IDs from the second tabl
run the "unmatched" wizard, using those first two querie
create a ?!fourth!? query, based on the output of the unmatched query

and joined to the table(s) that contain "the rest of the information" yo
need

I'm sure there's a technical term, but I call this "chaining". I'm als
fairly sure that someone, somewhere out there can create a single SQ
statement that does all this ... but I tend to figure out a reasonabl
effective solution and move on. If you'd like an "elegant" one-SQ
statement solution, you probably need to re-post, and ask for tha
explicitly

Microsoft has a program to acknowledge folks who provide support to users o
their products. This is the website for more information about the Mos
Valuable Professional (MVP) award
http://mvp.support.microsoft.com

Regard

Jeff Boyc
<Access MVP>