Re: complex querie - a follow up

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


"lynn atkinson" <lynnatkinson@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:B601AF3E-7BD4-4F41-8F29-E6C37DBED3C4@xxxxxxxxxxxxxxxx
> A couple of months ago I posted a question on how to filter a
query with many
> criteria. I did get a helpful answer, but I have got myself in
another pickle
> and cannot get the suggested code to work. In addition, I have
simplified the
> database by having fewer linked tables etc.
>
> The scenario is this
> I have 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; OR
> Advanced registration only
> job title= support worker needs care3 AND standard registration;
OR advanced
> registration only
> Job title=assistant project manager needs care3 AND standard
registration
> AND management3 OR Advanced registration AND management 3
>
> I want to list all candidates who are, for example,
> support workers who are qualified; all support assistants who are
qualified
> etc etc.
>
> I have a table (progresstbl) which lists the the award (eg care 2,
care3,
> standard registration, advanced registration etc) and the
> progress (eg completed, working towards etc). An employeeinfo
table holds
> information on the candidate like the name
>
> I would like to produce 2 reports - one listing all the people
completed
> their qualifying awards and one listing all the peole compled AND
those
> working towards the qualifying awards.
>
> If it helps, the following is the sql of something that nearly
works but
> doesnt have the combination of awards ie care 2 and standard
registration
>

<code snip>

>

lynn atkinson,

I realigned query for readability (table aliases would be great,
too; but I'll skip adding those in). Also, I apologize for any
line-wrapping that may occur. I spaced everything out for
visibility purposes, but when sending to a newsgroup, that isn't
always retained. If any line-wrapping occurs, some judicious
deleting of line-breaks should restore the correct appearance.


SELECT progress.status
,progress.[candidate ID]
,progress.[start date]
,progress.award
,employeeinfo.surname
,employeeinfo.forename
,employeeinfo.title
,employeeinfo.[post/role]
,employeeinfo.Project
,employeeinfo.locality
,employeeinfo.[old contract]
,employeeinfo.[employee ID]
FROM employeeinfo
INNER JOIN
progress
ON employeeinfo.[employee ID] = progress.[employee id]
WHERE
(
((progress.status) = "completed"
OR (progress.status) = "working towards")
AND ((progress.award) = "care 2")
AND ((employeeinfo.[post/role]) = "support assistant")
AND ((employeeinfo.[old contract]) = False)
)

OR

(
((progress.status) = "completed"
OR (progress.status) = "working towards")
AND ((progress.award) = "care 3")
AND ((employeeinfo.[post/role]) = "support worker")
AND ((employeeinfo.[old contract]) = False)
)

OR

(
((progress.status) = "completed"
OR (progress.status) = "working towards")
AND ((progress.award) = "care 3"
AND (progress.award) = "management 3")
AND ((employeeinfo.[post/role]) = "assistant project
manager")
AND ((employeeinfo.[old contract]) = False)
);

------------------------------
Job title = support assistant needs care2 AND starndard
registration; OR
Advanced registration only
job title= support worker needs care3 AND standard registration; OR
advanced
registration only
Job title=assistant project manager needs care3 AND standard
registration
AND management3 OR Advanced registration AND management 3
------------------------------


Completely Untested Suggestions:

Note: In the third group, you have specified that progress.award be
both equal to "care 3" and "management 3". This cannot happen.

Try:

(
((progress.status) = "completed"
OR (progress.status) = "working towards")
AND ((progress.award) IN ("care 3", "management
3"))
AND ((employeeinfo.[post/role]) = "assistant project
manager")
AND ((employeeinfo.[old contract]) = False)
);

This can further be reduced to:

(
((progress.status) IN ("completed", "working
towards"))
AND ((progress.award) IN ("care 3", "management
3"))
AND ((employeeinfo.[post/role]) = "assistant project
manager")
AND ((employeeinfo.[old contract]) = False)
);


Remember, that's just a guess on my part at this point.


Sincerely,

Chris O.


.

Relevant Pages

  • Re: Lennon was bad at making choices in life
    ... >> 1)Allen Klein for manager ... > contract with EMI and get them better royalty rates for the Beatles than the ... > they negotiated the new deal with EMI. ...
    (rec.music.beatles)
  • Re: Alonsos Future
    ... discussion was in his capacity as manager not as father) said. ... Suppose Lewis's contract is pretty poorly paid. ... which case their money would be safe) or that the money would be worth ... reopen negotiations. ...
    (rec.autos.sport.f1)
  • Re: Time Limit for Wages Claim Against Ex-Employer
    ... outstanding dispute regarding holiday pay deducted from a pay slip ... My original contract was a 6 month temporary contract at the end of ... All holiday was agreed verbally with my manager and there was ... Both my line managers were home-workers as was the area manager. ...
    (uk.legal)
  • Bolton Stuff
    ... so we're manager of Bolton and now and we've qualified for Europe. ... and utter cunts into lovely young men, so I thought it best we make an early ... and showed me his new contract of which he's quite touchingly proud. ... "I am the fat puddin', but a single puddingness" - Vicky Conlan ...
    (uk.sport.football)