whats wrong with this query?

L

lynn atkinson

SELECT Count(employeeinfo.[employee id]) AS Expr1, employeeinfo.[employee
ID], progress.[candidate ID], progress.status, progress.[start date],
progress.award, employeeinfo.surname, employeeinfo.forename,
employeeinfo.title, employeeinfo.[post/role], employeeinfo.Project,
employeeinfo.locality, employeeinfo.[old contract]
FROM employeeinfo INNER JOIN (candidate INNER JOIN progress ON
candidate.[candidate number] = progress.[candidate ID]) ON
employeeinfo.[employee ID] = candidate.[employee ID]
WHERE (((progress.status)="working towards") AND ((progress.award)="care 4")
AND ((employeeinfo.[old contract])=False)) OR (((progress.status)="working
towards") AND ((progress.award)="management 4") AND ((employeeinfo.[old
contract])=False));

I have the query above and it is telling me that employee id is not part of
an aggregate function. As far as I can see it is - a count function. What am
I not understanding here?

Also, whilst I am here I want to count only unique employee ids ie if an
employee has qualifications care3 and care4. I only want to count that person
(employee id) once. How do I do this?
 
J

John Spencer

When I reformat your query, I see employeeinfo.[employee ID] as the second
item in the query. This is separate from the Count() as Expr1. In a
"Totals" (aggregate) query, you have to GROUP By every column that doesn't
involve one of the aggregate functions.

SELECT Count(employeeinfo.[employee id]) AS Expr1
, employeeinfo.[employee ID]
, progress.[candidate ID]
, progress.status
, progress.[start date]
, progress.award
, employeeinfo.surname
, employeeinfo.forename
, employeeinfo.title
, employeeinfo.[post/role]
, employeeinfo.Project
, employeeinfo.locality
, employeeinfo.[old contract]
FROM employeeinfo INNER JOIN (candidate INNER JOIN progress ON
candidate.[candidate number] = progress.[candidate ID]) ON
employeeinfo.[employee ID] = candidate.[employee ID]
WHERE (((progress.status)="working towards") AND ((progress.award)="care 4")
AND ((employeeinfo.[old contract])=False)) OR (((progress.status)="working
towards") AND ((progress.award)="management 4") AND ((employeeinfo.[old
contract])=False))
GROUP BY employeeinfo.[employee ID]
, progress.[candidate ID]
, progress.status
, progress.[start date]
, progress.award
, employeeinfo.surname
, employeeinfo.forename
, employeeinfo.title
, employeeinfo.[post/role]
, employeeinfo.Project
, employeeinfo.locality
, employeeinfo.[old contract]
 
L

lynn atkinson

Ok I have got the first bit to work, but I am still getting people listed
twice ie id 180 doing management 4 and id 180 doing care 4. How do I count
this person once only regardless of how many awards they are doing?

John Spencer said:
When I reformat your query, I see employeeinfo.[employee ID] as the second
item in the query. This is separate from the Count() as Expr1. In a
"Totals" (aggregate) query, you have to GROUP By every column that doesn't
involve one of the aggregate functions.

SELECT Count(employeeinfo.[employee id]) AS Expr1
, employeeinfo.[employee ID]
, progress.[candidate ID]
, progress.status
, progress.[start date]
, progress.award
, employeeinfo.surname
, employeeinfo.forename
, employeeinfo.title
, employeeinfo.[post/role]
, employeeinfo.Project
, employeeinfo.locality
, employeeinfo.[old contract]
FROM employeeinfo INNER JOIN (candidate INNER JOIN progress ON
candidate.[candidate number] = progress.[candidate ID]) ON
employeeinfo.[employee ID] = candidate.[employee ID]
WHERE (((progress.status)="working towards") AND ((progress.award)="care 4")
AND ((employeeinfo.[old contract])=False)) OR (((progress.status)="working
towards") AND ((progress.award)="management 4") AND ((employeeinfo.[old
contract])=False))
GROUP BY employeeinfo.[employee ID]
, progress.[candidate ID]
, progress.status
, progress.[start date]
, progress.award
, employeeinfo.surname
, employeeinfo.forename
, employeeinfo.title
, employeeinfo.[post/role]
, employeeinfo.Project
, employeeinfo.locality
, employeeinfo.[old contract]



lynn atkinson said:
SELECT Count(employeeinfo.[employee id]) AS Expr1, employeeinfo.[employee
ID], progress.[candidate ID], progress.status, progress.[start date],
progress.award, employeeinfo.surname, employeeinfo.forename,
employeeinfo.title, employeeinfo.[post/role], employeeinfo.Project,
employeeinfo.locality, employeeinfo.[old contract]
FROM employeeinfo INNER JOIN (candidate INNER JOIN progress ON
candidate.[candidate number] = progress.[candidate ID]) ON
employeeinfo.[employee ID] = candidate.[employee ID]
WHERE (((progress.status)="working towards") AND ((progress.award)="care
4")
AND ((employeeinfo.[old contract])=False)) OR (((progress.status)="working
towards") AND ((progress.award)="management 4") AND ((employeeinfo.[old
contract])=False));

I have the query above and it is telling me that employee id is not part
of
an aggregate function. As far as I can see it is - a count function. What
am
I not understanding here?

Also, whilst I am here I want to count only unique employee ids ie if an
employee has qualifications care3 and care4. I only want to count that
person
(employee id) once. How do I do this?
Click to expand...
Click to expand...
 
J

John Spencer

You have to remove the progress.award from the list of fields to be
displayed. You get unique rows based on the contents of every field that is
displayed/grouped by. You can keep the fields in the WHERE clause.

As a guess, you are going to need to remove
Progress.Award and Progress.[Start Date] to get unique rows. If you want
you could use Min to get the earliest Start Date

SELECT Count(employeeinfo.[employee id]) AS Expr1
, employeeinfo.[employee ID]
, progress.[candidate ID]
, progress.status
, Min(progress.[start date]) as StartDate
, employeeinfo.surname
, employeeinfo.forename
, employeeinfo.title
, employeeinfo.[post/role]
, employeeinfo.Project
, employeeinfo.locality
, employeeinfo.[old contract]
FROM employeeinfo INNER JOIN (candidate INNER JOIN progress ON
candidate.[candidate number] = progress.[candidate ID]) ON
employeeinfo.[employee ID] = candidate.[employee ID]
WHERE (((progress.status)="working towards") AND ((progress.award)="care 4")
AND ((employeeinfo.[old contract])=False)) OR
(((progress.status)="working towards") AND ((progress.award)="management 4")
AND ((employeeinfo.[old contract])=False))
GROUP BY employeeinfo.[employee ID]
, progress.[candidate ID]
, progress.status
, employeeinfo.surname
, employeeinfo.forename
, employeeinfo.title
, employeeinfo.[post/role]
, employeeinfo.Project
, employeeinfo.locality
, employeeinfo.[old contract]

lynn atkinson said:
Ok I have got the first bit to work, but I am still getting people listed
twice ie id 180 doing management 4 and id 180 doing care 4. How do I count
this person once only regardless of how many awards they are doing?

John Spencer said:
When I reformat your query, I see employeeinfo.[employee ID] as the
second
item in the query. This is separate from the Count() as Expr1. In a
"Totals" (aggregate) query, you have to GROUP By every column that
doesn't
involve one of the aggregate functions.

SELECT Count(employeeinfo.[employee id]) AS Expr1
, employeeinfo.[employee ID]
, progress.[candidate ID]
, progress.status
, progress.[start date]
, progress.award
, employeeinfo.surname
, employeeinfo.forename
, employeeinfo.title
, employeeinfo.[post/role]
, employeeinfo.Project
, employeeinfo.locality
, employeeinfo.[old contract]
FROM employeeinfo INNER JOIN (candidate INNER JOIN progress ON
candidate.[candidate number] = progress.[candidate ID]) ON
employeeinfo.[employee ID] = candidate.[employee ID]
WHERE (((progress.status)="working towards") AND ((progress.award)="care
4")
AND ((employeeinfo.[old contract])=False)) OR
(((progress.status)="working
towards") AND ((progress.award)="management 4") AND ((employeeinfo.[old
contract])=False))
GROUP BY employeeinfo.[employee ID]
, progress.[candidate ID]
, progress.status
, progress.[start date]
, progress.award
, employeeinfo.surname
, employeeinfo.forename
, employeeinfo.title
, employeeinfo.[post/role]
, employeeinfo.Project
, employeeinfo.locality
, employeeinfo.[old contract]



lynn atkinson said:
SELECT Count(employeeinfo.[employee id]) AS Expr1,
employeeinfo.[employee
ID], progress.[candidate ID], progress.status, progress.[start date],
progress.award, employeeinfo.surname, employeeinfo.forename,
employeeinfo.title, employeeinfo.[post/role], employeeinfo.Project,
employeeinfo.locality, employeeinfo.[old contract]
FROM employeeinfo INNER JOIN (candidate INNER JOIN progress ON
candidate.[candidate number] = progress.[candidate ID]) ON
employeeinfo.[employee ID] = candidate.[employee ID]
WHERE (((progress.status)="working towards") AND
((progress.award)="care
4")
AND ((employeeinfo.[old contract])=False)) OR
(((progress.status)="working
towards") AND ((progress.award)="management 4") AND ((employeeinfo.[old
contract])=False));

I have the query above and it is telling me that employee id is not
part
of
an aggregate function. As far as I can see it is - a count function.
What
am
I not understanding here?

Also, whilst I am here I want to count only unique employee ids ie if
an
employee has qualifications care3 and care4. I only want to count that
person
(employee id) once. How do I do this?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count unique - yet more info 4
complex querie - a follow up 2
problem query 2
problem cascading deleting 5
Need Help With Make Table Query 2
Query with Relationships not working 1
Need to Count by two different criteria in same Totals query 4
what makes this query not updatable 7

Top