count unique - yet more info

[Guest]

Not sure if posts buried far down in threads started yesterday are picked up
as new, so I am going to start another one - Tell me if this is wrong
procedure.

Anyway further to my post yesterday with the count unique subject, I am
close to getting what I want, but still not there.
A brief resume of my question
I need to count how many people are qualified. However some may have more
than one award, but I just want count them once regardless of how many awards
they have.

Here is the sql of the original query which I have based a report on,
however, I cannot get it to count the employee ID only once. This is where
the select distinct should come in, but where

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],
Count(employeeinfo.[employee ID]) AS [CountOfemployee ID]
FROM employeeinfo INNER JOIN progress ON employeeinfo.[employee ID] =
progress.[employee id]
GROUP BY 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]
HAVING (((progress.status)="completed") AND ((progress.award)="care 4") AND
((employeeinfo.[post/role])="locality manager" Or
(employeeinfo.[post/role])="project manager") AND ((employeeinfo.[old
contract])=False)) OR (((progress.status)="completed") AND
((progress.award)="management 4") AND ((employeeinfo.[post/role])="locality
manager" Or (employeeinfo.[post/role])="project manager") AND
((employeeinfo.[old contract])=False));

iI need to put in the select distinct query - after the count bit? Do I
replace the FROM statement as it stands?

the select distinct query is

SELECT DISTINCT progress.[employee id]
FROM progress
WHERE (((progress.status)="completed") AND ((progress.award)="care 4")) OR
(((progress.status)="completed") AND ((progress.award)="management 4"));

I have obviously put it in the wrong place because I am now getting a
message about the LEVEL reserved word etc.....

Can I have 2 FROM statements or do I replace the FROM in the existing query?

Cheers

 

[Vincent Johns]

Not sure if posts buried far down in threads started yesterday are picked up
as new, so I am going to start another one - Tell me if this is wrong
procedure.

I don't like cluttering newsgroups with redundant posts, but sometimes
you need to do that if you don't get an answer. And I would certainly
have ignored your question here had you not re-posted it, on the basis
that someone else was probably taking care of it.

Anyway further to my post yesterday with the count unique subject, I am
close to getting what I want, but still not there.
A brief resume of my question
I need to count how many people are qualified. However some may have more
than one award, but I just want count them once regardless of how many awards
they have.

Since your description here doesn't match your SQL very well, I made
some guesses about what you wanted. (See below.)

Here is the sql of the original query which I have based a report on,
however, I cannot get it to count the employee ID only once. This is where
the select distinct should come in, but where

I don't see any need for DISTINCT or DISTINCTROW, since your [employee
id] field is (I assume) unique already. (BTW, I renamed it to
[employeeinfo id] to more closely match the name of its Table.)

I also omitted a bunch of fields which seem to be irrelevant to your
Query, but I assume you can easily re-insert them where you need to do so.

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],
Count(employeeinfo.[employee ID]) AS [CountOfemployee ID]
FROM employeeinfo INNER JOIN progress ON employeeinfo.[employee ID] =
progress.[employee id]
GROUP BY 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]
HAVING (((progress.status)="completed") AND ((progress.award)="care 4") AND
((employeeinfo.[post/role])="locality manager" Or
(employeeinfo.[post/role])="project manager") AND ((employeeinfo.[old
contract])=False)) OR (((progress.status)="completed") AND
((progress.award)="management 4") AND ((employeeinfo.[post/role])="locality
manager" Or (employeeinfo.[post/role])="project manager") AND
((employeeinfo.[old contract])=False));

iI need to put in the select distinct query - after the count bit? Do I
replace the FROM statement as it stands?

the select distinct query is

SELECT DISTINCT progress.[employee id]
FROM progress
WHERE (((progress.status)="completed") AND ((progress.award)="care 4")) OR
(((progress.status)="completed") AND ((progress.award)="management 4"));

I have obviously put it in the wrong place because I am now getting a
message about the LEVEL reserved word etc.....

Can I have 2 FROM statements or do I replace the FROM in the existing query?

Cheers

My example Tables (omitting the apparently useless [candidate] Table
that appeared in your previous examples) look like this:

[employeeinfo] Table Datasheet View:

employeeinfo ID surname post/role old contract
--------------- ------------ ---------------- ------------
1 McGillicuddy locality manager No
2 Ho project manager Yes
3 Singh locality manager No
10 Smith project manager No
180 Jones locality manager No

[progress] Table Datasheet View:

status award employeeinfo ID
--------- ------------ ---------------
completed care 4 1
completed management 4 1
completed care 4 2
completed care 1 3
completed management 4 3
completed management 4 10
completed care 4 10
completed care 4 180
working towards management 4 180

My approximation to your Query lists /how many/ qualifications each
employee has, even though you suggested that you don't really care how
many as long as it's a positive number.

[Q_Qualifications] SQL:

SELECT progress.[employeeinfo ID], employeeinfo.surname,
Count(employeeinfo.[employeeinfo ID])
AS [CountOfemployee ID]
FROM employeeinfo INNER JOIN progress
ON employeeinfo.[employeeinfo ID]
= progress.[employeeinfo ID]
WHERE (((progress.status)="completed")
AND ((employeeinfo.[old contract])=False)
AND ((progress.award)="care 4")
AND ((employeeinfo.[post/role])="locality manager"
Or (employeeinfo.[post/role])="project manager"))
OR (((progress.status)="completed")
AND ((employeeinfo.[old contract])=False)
AND ((progress.award)="management 4")
AND ((employeeinfo.[post/role])="locality manager"
Or (employeeinfo.[post/role])="project manager"))
GROUP BY progress.[employeeinfo ID],
employeeinfo.surname
ORDER BY employeeinfo.surname;

[Q_Qualifications] Query Datasheet View:

employeeinfo ID surname CountOfemployee ID
--------------- ------------ ------------------
180 Jones 1
1 McGillicuddy 2
3 Singh 1
10 Smith 2

So, we now know who has at least one qualification. Now, to produce a
list of these people, we can use another Query that checks the number of
qualifications.

[Q_Qualified_Persons]

SELECT Q_Qualifications.surname,
Q_Qualifications.[employeeinfo ID]
FROM Q_Qualifications
WHERE (((Q_Qualifications.[CountOfemployee ID])>0))
ORDER BY Q_Qualifications.surname;

(The "WHERE" clause probably isn't necessary, as only the matching
records will be reported.)

[Q_Qualified_Persons] Query Datasheet View:

surname employeeinfo ID
------------ ---------------
Jones 180
McGillicuddy 1
Singh 3
Smith 10

Does this do what you wanted?

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Thanks for your reply. It is getting closer... But my end result is to report
how many people are qualified, so in your example the result would be 4. I
dont need to know individual names but I do need different reports for
different levels therefore I need the 'award' information somewhere in the
original query.
This is where my original query failed as it was counting individual
employeeid's more than once if they had more than one award.

Not sure if posts buried far down in threads started yesterday are picked up
as new, so I am going to start another one - Tell me if this is wrong
procedure.

I don't like cluttering newsgroups with redundant posts, but sometimes
you need to do that if you don't get an answer. And I would certainly
have ignored your question here had you not re-posted it, on the basis
that someone else was probably taking care of it.

Anyway further to my post yesterday with the count unique subject, I am
close to getting what I want, but still not there.
A brief resume of my question
I need to count how many people are qualified. However some may have more
than one award, but I just want count them once regardless of how many awards
they have.

Since your description here doesn't match your SQL very well, I made
some guesses about what you wanted. (See below.)

Here is the sql of the original query which I have based a report on,
however, I cannot get it to count the employee ID only once. This is where
the select distinct should come in, but where

I don't see any need for DISTINCT or DISTINCTROW, since your [employee
id] field is (I assume) unique already. (BTW, I renamed it to
[employeeinfo id] to more closely match the name of its Table.)

I also omitted a bunch of fields which seem to be irrelevant to your
Query, but I assume you can easily re-insert them where you need to do so.

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],
Count(employeeinfo.[employee ID]) AS [CountOfemployee ID]
FROM employeeinfo INNER JOIN progress ON employeeinfo.[employee ID] =
progress.[employee id]
GROUP BY 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]
HAVING (((progress.status)="completed") AND ((progress.award)="care 4") AND
((employeeinfo.[post/role])="locality manager" Or
(employeeinfo.[post/role])="project manager") AND ((employeeinfo.[old
contract])=False)) OR (((progress.status)="completed") AND
((progress.award)="management 4") AND ((employeeinfo.[post/role])="locality
manager" Or (employeeinfo.[post/role])="project manager") AND
((employeeinfo.[old contract])=False));

iI need to put in the select distinct query - after the count bit? Do I
replace the FROM statement as it stands?

the select distinct query is

SELECT DISTINCT progress.[employee id]
FROM progress
WHERE (((progress.status)="completed") AND ((progress.award)="care 4")) OR
(((progress.status)="completed") AND ((progress.award)="management 4"));

I have obviously put it in the wrong place because I am now getting a
message about the LEVEL reserved word etc.....

Can I have 2 FROM statements or do I replace the FROM in the existing query?

Cheers

My example Tables (omitting the apparently useless [candidate] Table
that appeared in your previous examples) look like this:

[employeeinfo] Table Datasheet View:

employeeinfo ID surname post/role old contract
--------------- ------------ ---------------- ------------
1 McGillicuddy locality manager No
2 Ho project manager Yes
3 Singh locality manager No
10 Smith project manager No
180 Jones locality manager No

[progress] Table Datasheet View:

status award employeeinfo ID
--------- ------------ ---------------
completed care 4 1
completed management 4 1
completed care 4 2
completed care 1 3
completed management 4 3
completed management 4 10
completed care 4 10
completed care 4 180
working towards management 4 180

My approximation to your Query lists /how many/ qualifications each
employee has, even though you suggested that you don't really care how
many as long as it's a positive number.

[Q_Qualifications] SQL:

SELECT progress.[employeeinfo ID], employeeinfo.surname,
Count(employeeinfo.[employeeinfo ID])
AS [CountOfemployee ID]
FROM employeeinfo INNER JOIN progress
ON employeeinfo.[employeeinfo ID]
= progress.[employeeinfo ID]
WHERE (((progress.status)="completed")
AND ((employeeinfo.[old contract])=False)
AND ((progress.award)="care 4")
AND ((employeeinfo.[post/role])="locality manager"
Or (employeeinfo.[post/role])="project manager"))
OR (((progress.status)="completed")
AND ((employeeinfo.[old contract])=False)
AND ((progress.award)="management 4")
AND ((employeeinfo.[post/role])="locality manager"
Or (employeeinfo.[post/role])="project manager"))
GROUP BY progress.[employeeinfo ID],
employeeinfo.surname
ORDER BY employeeinfo.surname;

[Q_Qualifications] Query Datasheet View:

employeeinfo ID surname CountOfemployee ID
--------------- ------------ ------------------
180 Jones 1
1 McGillicuddy 2
3 Singh 1
10 Smith 2

So, we now know who has at least one qualification. Now, to produce a
list of these people, we can use another Query that checks the number of
qualifications.

[Q_Qualified_Persons]

SELECT Q_Qualifications.surname,
Q_Qualifications.[employeeinfo ID]
FROM Q_Qualifications
WHERE (((Q_Qualifications.[CountOfemployee ID])>0))
ORDER BY Q_Qualifications.surname;

(The "WHERE" clause probably isn't necessary, as only the matching
records will be reported.)

[Q_Qualified_Persons] Query Datasheet View:

surname employeeinfo ID
------------ ---------------
Jones 180
McGillicuddy 1
Singh 3
Smith 10

Does this do what you wanted?

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

OK; assuming that your results from what I showed you are ...

[Q_Qualified_Persons] Query Datasheet View:

surname employeeinfo ID
------------ ---------------
Jones 180
McGillicuddy 1
Singh 3
Smith 10

then you can define a Query that will count these.

[Q_NumQualified] SQL:

SELECT Count(Q_Qualified_Persons.[employeeinfo ID]) AS NumberQualified
FROM Q_Qualified_Persons;

and you'll get the desired value.

[Q_NumQualified] Query Datasheet View:

NumberQualified
---------------
4

Thanks for your reply. It is getting closer... But my end result is to report
how many people are qualified, so in your example the result would be 4. I
dont need to know individual names but I do need different reports for
different levels therefore I need the 'award' information somewhere in the
original query.

It's kind of buried in there; check the contents of [Q_Qualifications] SQL:

... AND ((progress.award)="care 4") ...

But, if you need to do this more than once, you should probably set up
some other Table that lists all the types of awards that interest you
and also has a yes/no type [ReportThisAwardNow?] field that specifies
that you want to include a given award in your results. Change
[Q_Qualifications] so that it links to this Table (where the
[ReportThisAwardNow?] field has a "yes" value), and that ought to give
you the list of names that you want.

Of course, if you wished, you could even go beyond this to define a
Query that would list all the possible qualifications and, for each one,
tell you how many persons possessed that qualification.

This is where my original query failed as it was counting individual
employeeid's more than once if they had more than one award.

:

lynn atkinson wrote:

Not sure if posts buried far down in threads started yesterday are picked up
as new, so I am going to start another one - Tell me if this is wrong
procedure.

I don't like cluttering newsgroups with redundant posts, but sometimes
you need to do that if you don't get an answer. And I would certainly
have ignored your question here had you not re-posted it, on the basis
that someone else was probably taking care of it.

Anyway further to my post yesterday with the count unique subject, I am
close to getting what I want, but still not there.
A brief resume of my question
I need to count how many people are qualified. However some may have more
than one award, but I just want count them once regardless of how many awards
they have.

Since your description here doesn't match your SQL very well, I made
some guesses about what you wanted. (See below.)

Here is the sql of the original query which I have based a report on,
however, I cannot get it to count the employee ID only once. This is where
the select distinct should come in, but where

I don't see any need for DISTINCT or DISTINCTROW, since your [employee
id] field is (I assume) unique already. (BTW, I renamed it to
[employeeinfo id] to more closely match the name of its Table.)

I also omitted a bunch of fields which seem to be irrelevant to your
Query, but I assume you can easily re-insert them where you need to do so.

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],
Count(employeeinfo.[employee ID]) AS [CountOfemployee ID]
FROM employeeinfo INNER JOIN progress ON employeeinfo.[employee ID] =
progress.[employee id]
GROUP BY 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]
HAVING (((progress.status)="completed") AND ((progress.award)="care 4") AND
((employeeinfo.[post/role])="locality manager" Or
(employeeinfo.[post/role])="project manager") AND ((employeeinfo.[old
contract])=False)) OR (((progress.status)="completed") AND
((progress.award)="management 4") AND ((employeeinfo.[post/role])="locality
manager" Or (employeeinfo.[post/role])="project manager") AND
((employeeinfo.[old contract])=False));

iI need to put in the select distinct query - after the count bit? Do I
replace the FROM statement as it stands?

the select distinct query is

SELECT DISTINCT progress.[employee id]
FROM progress
WHERE (((progress.status)="completed") AND ((progress.award)="care 4")) OR
(((progress.status)="completed") AND ((progress.award)="management 4"));

I have obviously put it in the wrong place because I am now getting a
message about the LEVEL reserved word etc.....

Can I have 2 FROM statements or do I replace the FROM in the existing query?

Cheers

My example Tables (omitting the apparently useless [candidate] Table
that appeared in your previous examples) look like this:

[employeeinfo] Table Datasheet View:

employeeinfo ID surname post/role old contract
--------------- ------------ ---------------- ------------
1 McGillicuddy locality manager No
2 Ho project manager Yes
3 Singh locality manager No
10 Smith project manager No
180 Jones locality manager No

[progress] Table Datasheet View:

status award employeeinfo ID
--------- ------------ ---------------
completed care 4 1
completed management 4 1
completed care 4 2
completed care 1 3
completed management 4 3
completed management 4 10
completed care 4 10
completed care 4 180
working towards management 4 180

My approximation to your Query lists /how many/ qualifications each
employee has, even though you suggested that you don't really care how
many as long as it's a positive number.

[Q_Qualifications] SQL:

SELECT progress.[employeeinfo ID], employeeinfo.surname,
Count(employeeinfo.[employeeinfo ID])
AS [CountOfemployee ID]
FROM employeeinfo INNER JOIN progress
ON employeeinfo.[employeeinfo ID]
= progress.[employeeinfo ID]
WHERE (((progress.status)="completed")
AND ((employeeinfo.[old contract])=False)
AND ((progress.award)="care 4")
AND ((employeeinfo.[post/role])="locality manager"
Or (employeeinfo.[post/role])="project manager"))
OR (((progress.status)="completed")
AND ((employeeinfo.[old contract])=False)
AND ((progress.award)="management 4")
AND ((employeeinfo.[post/role])="locality manager"
Or (employeeinfo.[post/role])="project manager"))
GROUP BY progress.[employeeinfo ID],
employeeinfo.surname
ORDER BY employeeinfo.surname;

[Q_Qualifications] Query Datasheet View:

employeeinfo ID surname CountOfemployee ID
--------------- ------------ ------------------
180 Jones 1
1 McGillicuddy 2
3 Singh 1
10 Smith 2

So, we now know who has at least one qualification. Now, to produce a
list of these people, we can use another Query that checks the number of
qualifications.

[Q_Qualified_Persons]

SELECT Q_Qualifications.surname,
Q_Qualifications.[employeeinfo ID]
FROM Q_Qualifications
WHERE (((Q_Qualifications.[CountOfemployee ID])>0))
ORDER BY Q_Qualifications.surname;

(The "WHERE" clause probably isn't necessary, as only the matching
records will be reported.)

[Q_Qualified_Persons] Query Datasheet View:

surname employeeinfo ID
------------ ---------------
Jones 180
McGillicuddy 1
Singh 3
Smith 10

Does this do what you wanted?

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Got It! At last - thanks very much for your help - and the help of others
before you.

OK; assuming that your results from what I showed you are ...

[Q_Qualified_Persons] Query Datasheet View:

surname employeeinfo ID
------------ ---------------
Jones 180
McGillicuddy 1
Singh 3
Smith 10

then you can define a Query that will count these.

[Q_NumQualified] SQL:

SELECT Count(Q_Qualified_Persons.[employeeinfo ID]) AS NumberQualified
FROM Q_Qualified_Persons;

and you'll get the desired value.

[Q_NumQualified] Query Datasheet View:

NumberQualified
---------------
4

Thanks for your reply. It is getting closer... But my end result is to report
how many people are qualified, so in your example the result would be 4. I
dont need to know individual names but I do need different reports for
different levels therefore I need the 'award' information somewhere in the
original query.

It's kind of buried in there; check the contents of [Q_Qualifications] SQL:

... AND ((progress.award)="care 4") ...

But, if you need to do this more than once, you should probably set up
some other Table that lists all the types of awards that interest you
and also has a yes/no type [ReportThisAwardNow?] field that specifies
that you want to include a given award in your results. Change
[Q_Qualifications] so that it links to this Table (where the
[ReportThisAwardNow?] field has a "yes" value), and that ought to give
you the list of names that you want.

Of course, if you wished, you could even go beyond this to define a
Query that would list all the possible qualifications and, for each one,
tell you how many persons possessed that qualification.

This is where my original query failed as it was counting individual
employeeid's more than once if they had more than one award.

:

lynn atkinson wrote:


Not sure if posts buried far down in threads started yesterday are picked up
as new, so I am going to start another one - Tell me if this is wrong
procedure.

I don't like cluttering newsgroups with redundant posts, but sometimes
you need to do that if you don't get an answer. And I would certainly
have ignored your question here had you not re-posted it, on the basis
that someone else was probably taking care of it.


Anyway further to my post yesterday with the count unique subject, I am
close to getting what I want, but still not there.
A brief resume of my question
I need to count how many people are qualified. However some may have more
than one award, but I just want count them once regardless of how many awards
they have.

Since your description here doesn't match your SQL very well, I made
some guesses about what you wanted. (See below.)


Here is the sql of the original query which I have based a report on,
however, I cannot get it to count the employee ID only once. This is where
the select distinct should come in, but where

I don't see any need for DISTINCT or DISTINCTROW, since your [employee
id] field is (I assume) unique already. (BTW, I renamed it to
[employeeinfo id] to more closely match the name of its Table.)

I also omitted a bunch of fields which seem to be irrelevant to your
Query, but I assume you can easily re-insert them where you need to do so.


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],
Count(employeeinfo.[employee ID]) AS [CountOfemployee ID]
FROM employeeinfo INNER JOIN progress ON employeeinfo.[employee ID] =
progress.[employee id]
GROUP BY 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]
HAVING (((progress.status)="completed") AND ((progress.award)="care 4") AND
((employeeinfo.[post/role])="locality manager" Or
(employeeinfo.[post/role])="project manager") AND ((employeeinfo.[old
contract])=False)) OR (((progress.status)="completed") AND
((progress.award)="management 4") AND ((employeeinfo.[post/role])="locality
manager" Or (employeeinfo.[post/role])="project manager") AND
((employeeinfo.[old contract])=False));

iI need to put in the select distinct query - after the count bit? Do I
replace the FROM statement as it stands?

the select distinct query is

SELECT DISTINCT progress.[employee id]
FROM progress
WHERE (((progress.status)="completed") AND ((progress.award)="care 4")) OR
(((progress.status)="completed") AND ((progress.award)="management 4"));

I have obviously put it in the wrong place because I am now getting a
message about the LEVEL reserved word etc.....

Can I have 2 FROM statements or do I replace the FROM in the existing query?

Cheers


My example Tables (omitting the apparently useless [candidate] Table
that appeared in your previous examples) look like this:

[employeeinfo] Table Datasheet View:

employeeinfo ID surname post/role old contract
--------------- ------------ ---------------- ------------
1 McGillicuddy locality manager No
2 Ho project manager Yes
3 Singh locality manager No
10 Smith project manager No
180 Jones locality manager No

[progress] Table Datasheet View:

status award employeeinfo ID
--------- ------------ ---------------
completed care 4 1
completed management 4 1
completed care 4 2
completed care 1 3
completed management 4 3
completed management 4 10
completed care 4 10
completed care 4 180
working towards management 4 180

My approximation to your Query lists /how many/ qualifications each
employee has, even though you suggested that you don't really care how
many as long as it's a positive number.

[Q_Qualifications] SQL:

SELECT progress.[employeeinfo ID], employeeinfo.surname,
Count(employeeinfo.[employeeinfo ID])
AS [CountOfemployee ID]
FROM employeeinfo INNER JOIN progress
ON employeeinfo.[employeeinfo ID]
= progress.[employeeinfo ID]
WHERE (((progress.status)="completed")
AND ((employeeinfo.[old contract])=False)
AND ((progress.award)="care 4")
AND ((employeeinfo.[post/role])="locality manager"
Or (employeeinfo.[post/role])="project manager"))
OR (((progress.status)="completed")
AND ((employeeinfo.[old contract])=False)
AND ((progress.award)="management 4")
AND ((employeeinfo.[post/role])="locality manager"
Or (employeeinfo.[post/role])="project manager"))
GROUP BY progress.[employeeinfo ID],
employeeinfo.surname
ORDER BY employeeinfo.surname;

[Q_Qualifications] Query Datasheet View:

employeeinfo ID surname CountOfemployee ID
--------------- ------------ ------------------
180 Jones 1
1 McGillicuddy 2
3 Singh 1
10 Smith 2

So, we now know who has at least one qualification. Now, to produce a
list of these people, we can use another Query that checks the number of
qualifications.

[Q_Qualified_Persons]

SELECT Q_Qualifications.surname,
Q_Qualifications.[employeeinfo ID]
FROM Q_Qualifications
WHERE (((Q_Qualifications.[CountOfemployee ID])>0))
ORDER BY Q_Qualifications.surname;

(The "WHERE" clause probably isn't necessary, as only the matching
records will be reported.)

[Q_Qualified_Persons] Query Datasheet View:

surname employeeinfo ID
------------ ---------------
Jones 180
McGillicuddy 1
Singh 3
Smith 10

Does this do what you wanted?

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.