complex querie - a follow up

[Guest]

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

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

 

[[MVP] S.Clark]

Without reading every word you've written, I'll give some of my standard
query answers...

1. Not all answers come from a single query. Maybe breaking it down to 2 or
3 queries will do it.

2. If that's no good, consider writing data to a table, then basing the
report on the table, instead of the query.

3. VBA solves everything. Create the SQL string in code, implementing the
dynamic criteria, and pass the criteria to the report.

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

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

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

 

[Chris2]

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

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.