count unique - still confused

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lynn atkinson - 07 Dec 2005 11:16 GMT

I have posted a couple of posts previously,but am still not getting the
result I need.

I want to produce a report of staff qualifications and to count how many
staff are qualified. However some staff have more than one qualification, but
I want to count them just once (I need to produce % of staff qualified)
It was suggested to use the unique values property in the underlying query,
but as the staff can be qualified in more than one subject, their records are
not unique. (I need to list the subject in this query as the subject is also
a criteria)

For example one member of staff's 2 records listed in the query would be
status=complete, award = management 4, employeeID=10 AND
status=complete, award = care 4, employeeID=10

I want to count employeeID=10 only once
What am I missing? How do I do this?

Part of the original post gave an example as follows.

> > For example
> > employeeid 01 is qualified in care 1
> > employeeid 01 is qualified in management1
> > employeeid 02 is qualified in care 1
> > employee id 03 is qualified in care 1
> > employeeid 03 is qualified in management1
> >
> > I want the calculation to return the number 3 - ie 3 employees qualified, no
> > matter how many qualifications they have.
> >
> > How do I do this
> >
> > a previous suggestion was to use the uniquevalues in the query, but the
> > count is done in a report and the records would not be unique.
> >
> > Any other suggestions gratefully received.

What am I missing????

Reply to this Message

JAA149 - 07 Dec 2005 12:10 GMT

Dear Lynn,

Try DISTINCT or more properly DISTINCTROW. Please refer to MS Access help
for DISTINCT/DISTINCTROW. Go to MS Access Help & type "ALL, DISTINCT,
DISTINCTROW, TOP Predicates"

I think you problem will be solved by using DiSTINCTROW after the SELECT
statament. (Note the difference between DISTINCT & DISTINCTROW).

You can use DISTINCT while only selecting the EmployeeId filed as it works
with only 1 field OR you can use the DISTINCTROW for more than 1 fileds

Regards

================

> I have posted a couple of posts previously,but am still not getting the
> result I need.
[quoted text clipped - 34 lines]
>
> What am I missing????

Reply to this Message

lynn atkinson - 07 Dec 2005 16:58 GMT

OK, I have worked out what distinct and distinctrow is doing. BUT it doesnt
solve my problem. the records i need to display are employee1 status=complete
and award may be award1 or award 2

So if I use the select distinct as a sub query (or as the main query?) , how
do I do that? Dont know how to work subqueries, despite reading a lot about
them this afternoon! Can I limit the employee ids in the main query to only
those specified by the select distinct query? I I am getting very weary of
this one and am on the verge of giving up completely. Can anyone help
further????

> Dear Lynn,
>
[quoted text clipped - 50 lines]
> >
> > What am I missing????

Reply to this Message

John Vinson - 08 Dec 2005 00:10 GMT

>OK, I have worked out what distinct and distinctrow is doing. BUT it doesnt
>solve my problem. the records i need to display are employee1 status=complete
[quoted text clipped - 6 lines]
>this one and am on the verge of giving up completely. Can anyone help
>further????

You haven't posted your table structure or the SQL of your attempts so
this can't be very precise, but I can give you a couple of
suggestions.

Perhaps simplest to understand is a method using two Queries. Create a
Query which selects the employee ID (and NOTHING ELSE) of the
employees that you want to count; uncheck the "show" checkbox in the
query grid for the award and status fields, since you're only using
them as criteria.

View the query's Properties and select "Unique Values".

The SQL will be something like

SELECT DISTINCT EmployeeID
FROM Employees
WHERE <various criteria>

Save this query (which will look very uninteresting, just a list of
ID's) as qryDistinctEmployees.

Now base a second query ON THIS QUERY; make it a totals query and
Count employeeID.

The Subquery method needs to use the SQL from the stored first query.
It's probably easiest to build that query first, open it in SQL view,
and copy and paste the SQL to a query (also in the SQL window) like

SELECT Count(*) FROM (<paste the query here>);

John W. Vinson[MVP]

Reply to this Message

lynn atkinson - 08 Dec 2005 11:03 GMT

OK thanks for that. Getting closer!
Here is the sql of the original query

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

> >OK, I have worked out what distinct and distinctrow is doing. BUT it doesnt
> >solve my problem. the records i need to display are employee1 status=complete
[quoted text clipped - 38 lines]
>
> John W. Vinson[MVP]

Reply to this Message