[RESOLVED] MS Access 2000 - Query problem(Agregate function)

[Giftx]

Need some help and clarification.
I created an access query in design view. Now, I am trying to run the query using values from two linked tables.

I have selected the fields I need from both tables, I am using a Count function on one field and a sum function on another field but I am getting this error:

"You tried to execute a query that does not include the specified expression “EmployeeName” as part of an aggregate function"

How can I get this query to work?

Below is my SQL view:

SELECT DISTINCT Count([PassNumber]) AS HeadCount, tblUnclaimed.EmployeeName
FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1) AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber)
WHERE (((tblUnclaimed.ReIssued_Check_Date) Between #3/1/2008# And #9/30/2008#) AND ((EmployeeInfo.Status) Like 'N*') AND ((EmployeeInfo.Union_Code) In ('00','01','32','33')) AND ((tblUnclaimed.Status) In ('E','U'))) OR (((tblUnclaimed.ReIssued_Check_Date) Between #3/1/2008# And #10/1/2008#) AND ((EmployeeInfo.Union_Code) In ('00','01','32','33')) AND ((tblUnclaimed.Status) In ('E','U')) AND ((Len([EmployeeInfo].[Status]))=1))
ORDER BY EmployeeInfo.L3, EmployeeInfo.L5;

Any input will be appreciated.
Giftx.

[si_the_geek]

The problem is that you are using an aggregate function (sum/count/max/...) on one field, but not on the other - you just have Count, and despite what you said you do not have Sum on the other.

When you do that, you need to use a Group By clause which includes all columns that do not use an aggregate function (as the Group By clause says how to aggregate those columns, which is by eliminating duplicates).

eg:

...
GROUP BY tblUnclaimed.EmployeeName
ORDER BY EmployeeInfo.L3, EmployeeInfo.L5;

When you use a Group By, there is no need to use the keyword Distinct in the Select clause.

Note that you might now get errors for the Order By clause, as those fields will not exist in the aggregated results... if so, removing the Order By is the simplest way to get past that.

[Giftx]

Thanks Si.
It worked.

Giftx.