SELECT P.status, P.[candidate ID], P.[start date], P.award, ED.surname,
ED.forename,
ED.title, PD.[post/role], PC.Project, PC.locality, Co.[old contract],
ED.[employee ID new]
FROM (([post details] PD
LEFT JOIN [Project Codes] PC ON PD.[project code] = PC.Code)
INNER JOIN ((candidate Ca
INNER JOIN employeedetails ED ON Ca.[employee ID] = ED.[employee ID
new])
INNER JOIN contractual Co ON ED.[employee ID new] = Co.[employee ID])
ON PD.[post ID] = Co.[post ID])
INNER JOIN progress P ON Ca.[candidate number] = P.[candidate ID]
WHERE ((P.status = "completed" Or P.status = "working towards") AND
P.award = "care 2"
AND PD.[post/role] = "support assistant" AND
(Co.[old contract] = False OR ((P.status = "completed" Or P.status =
"working towards")
AND P.award = "care 3" AND PD.[post/role] = "support worker" AND
Co.[old contract] = False)
OR ((P.status ="completed" Or P.status ="working towards")
AND (P.award ="care 3" And P.award = "management 3")
AND PD.[post/role] = "assistant project manager" AND Co.[old contract] =
False);
WHERE
P.status IN ("completed", "working towards")
-- your logic seems to require status of completed or working towards in all
cases, so get this out of the way once
-- an IN clause is a bit shorter and easier to read than the OR you used
AND
-- Job title = support assistant needs care2 AND starndard registration; or
Advanced registration only
((PD.[post/role] = "support assistant"
AND ((P.award = "care 2" AND <registration> = "standard") OR
<registration> = "advanced"))
-- I couldn't tell from your post where <registration> is found. You need
to correct this reference.
-- I wrote this logic where advanced registration doesn't require award
care2 but standard does.
-- job title= support worker needs care3 AND standard registration; OR
advanced registration only
OR (PD.[post/role] = "support worker"
AND ((P.award = "care 3" AND <registration> = "standard") OR
<registration> = "advanced"))
-- Job title=assistant project manager needs care3 AND standard registration
AND management3
OR (PD.[post/role] = "project manager"
AND P.award = "care 3" AND <registration> = "standard" AND P.award =
"management3")
I have placed comments within the above. You would have to remove those
before trying to run the query.
I used aliases for all the tables to make it easier to read. You should
probably get accustomed to using these.
Perhaps you have attempted to write this using the GUI query designer. With
logic as complex as you have, I think this is a bad policy. It gets awfully
messy that way. However, the SQL view will mangle your query text very
badly. I recomment you use Notepad and keep a separate text version of the
query. Go back to this when you need to edit the query, and past the query
text into the SQL view. This is quite effective in overcoming the problem
with scramboling up the query text.
Tom Ellison
lynn atkinson said:
Thanks for your reply
The following is the SQL of a query which 'nearly' works. It captures the
support workers, and suppost assistants oK, but misses the assistant project
managers as they need 2 awards care3 AND management3 which is where it comes
unstuck.
LECT progress.status, progress.[candidate ID], progress.[start date],
progress.award, employeedetails.surname, employeedetails.forename,
employeedetails.title, [post details].[post/role], [Project Codes].Project,
[Project Codes].locality, contractual.[old contract],
employeedetails.[employee ID new]
FROM (([post details] LEFT JOIN [Project Codes] ON [post details].[project
code] = [Project Codes].Code) INNER JOIN ((candidate INNER JOIN
employeedetails ON candidate.[employee ID] = employeedetails.[employee ID
new]) INNER JOIN contractual ON employeedetails.[employee ID new] =
contractual.[employee ID]) ON [post details].[post ID] = contractual.[post
ID]) INNER JOIN progress ON candidate.[candidate number] =
progress.[candidate ID]
WHERE (((progress.status)="completed" Or (progress.status)="working
towards") AND ((progress.award)="care 2") AND (([post
details].[post/role])="support assistant") AND ((contractual.[old
contract])=False)) OR (((progress.status)="completed" Or
(progress.status)="working towards") AND ((progress.award)="care 3") AND
(([post details].[post/role])="support worker") AND ((contractual.[old
contract])=False)) OR (((progress.status)="completed" Or
(progress.status)="working towards") AND ((progress.award)="care 3" And
(progress.award)="management 3") AND (([post details].[post/role])="assistant
project manager") AND ((contractual.[old contract])=False));
This is not the query I spoke about in my first post, but assistant project
manager problem is similar to what I am needing to solve in many queries.
Thanks in advance
Tom Ellison said:
Dear Lynn:
If you are writing the query for a Jet database, it will be a bit complex,
using nested IF statements. Other than this complexity, the filtering would
be performed using EXISTS with correlated subqueries. If you are well
practiced in using these, it ought not to be too difficult.
Assuming for the moment that this still seems a large challenge, I suggest
you write a query and post it here in which you try to come close to this
goal. I may be able to work from this to build what you need.
For a MSDE database, a CASE statement would be easier to write, and to read,
but the logic would be the same.
--
Tom Ellison
This is a follow up post which I need clarifying.
I am looking at a training database and I need to filter a query to find
qualified people. Whether a person is qualified or not depends on their
job
title.
Job title = support assistant needs care2 AND starndard
registration;
and
to
