Count & Distinct Query Not Working

[hhzyousafi]

I keep getting an error with the following query:

SELECT DISTINCT (AssignedTo) AS Expr1, (SELECT COUNT(DISTINCT (AssignedTo)) FROM tblTasks) AS Expr2
FROM tblTasks;

The error is "Undefined Function 'DISTINCT' in Expression". What am I doing wrong? The "References" are okay because the first "DISTINCT" in the query works; however the second one is giving me an error.

 

[Dlovan-IT]

if you create another query , the same error display ?

 

[hhzyousafi]

Yes I get the same error.

 

[Dlovan-IT]

try this :

SELECT DISTINCT (AssignedTo) AS Expr1, (SELECT COUNT(*) distinct (AssignedTo)) FROM tblTasks) AS Expr2

 

[hhzyousafi]

That is giving me a syntax error. Access 2007 is saying the issue is with "(SELECT COUNT(*) DISTINCT(AssignedTo))".

 

[Mihail]

Because my limited knowledge in SQL I always solve this type of problems by using multiple queries... that become subqueries... that become subqueries... until I obtain the desired result. One action (result) at a time.

 

[JHB]

I keep getting an error with the following query:

SELECT DISTINCT (AssignedTo) AS Expr1, (SELECT COUNT(DISTINCT (AssignedTo)) FROM tblTasks) AS Expr2
FROM tblTasks;

The error is "Undefined Function 'DISTINCT' in Expression". What am I doing wrong? The "References" are okay because the first "DISTINCT" in the query works; however the second one is giving me an error.

What are you trying to accomplish?
The DISTINCT must stand together with SELECT statement, (why are you using ()?)

SELECT DISTINCT AssignedTo AS Expr1, (SELECT DISTINCT COUNT(AssignedTo) FROM tblTasks) AS Expr2

What result do you get if you omit the second DISTINCT, (because for me it isn't looking correct then only one result is returned by the Count function)?

SELECT DISTINCT AssignedTo AS Expr1, (SELECT COUNT(AssignedTo) FROM tblTasks) AS Expr2

 

[jdraw]

I think this syntax would work, but I agree with JHB --What are you trying to accomplish?

SELECT Count(*) FROM
  (Select distinct Assigned_to FROM tblTasks)

 

[hhzyousafi]

I am trying to accomplish a simple count. The query is based on a table, tblTasks, and the query is supposed to tell me the unique names of who the tasks are assigned to and then how many tasks have been assigned to them and how many they have completed. I tried what each of you suggested but that didn't work.

What I ended up doing, and what worked beautifully, is that I used a DCOUNT formula instead. It's strange that I was writing exactly the same thing in SQL however Access was reacting differently to the SQL as compared to the DCOUNT. The DCOUNT is definitely the way to go.

 

[Mihail]

I am trying to accomplish a simple count. The query is based on a table, tblTasks, and the query is supposed to tell me the unique names of who the tasks are assigned to and then how many tasks have been assigned to them and how many they have completed. I tried what each of you suggested but that didn't work.

What I ended up doing, and what worked beautifully, is that I used a DCOUNT formula instead. It's strange that I was writing exactly the same thing in SQL however Access was reacting differently to the SQL as compared to the DCOUNT. The DCOUNT is definitely the way to go.

Not for sure.
Upload a simple (Access 2003) and will see.

 

[hhzyousafi]

For example I was writing the following in SQL:

SELECT COUNT(AssignedTo) FROM tblTasks WHERE AssignedTo = Expr1;
​

However Access responded by saying it could only return one record at a time with this particular query. Then I used the following DCOUNT and got the desired result:

DCOUNT("AssignedTo", "tblTasks", "AssignedTo = '" & Expr1 & "'")
​

The DCOUNT was very effective and efficient here whereas the SQL was sluggish and unreliable. If there is an error in my SQL syntax then please feel free to point that out to me.

 

[jdraw]

the query is supposed to tell me the unique names of who the tasks are assigned to and then how many tasks have been assigned to them and how many they have completed.

and you solved that query with

DCOUNT("AssignedTo", "tblTasks", "AssignedTo = '" & Expr1 & "'")

How about showing all the code involved?

 

[JHB]

For example I was writing the following in SQL:

SELECT COUNT(AssignedTo) FROM tblTasks WHERE AssignedTo = Expr1;
​

DCOUNT("AssignedTo", "tblTasks", "AssignedTo = '" & Expr1 & "'")
​

It is a different on the two:
In the first query you have:

...AssignedTo = Expr1

What should AssignedTo be equal to, (a field in the table tblTasks or what)?
In the second query you have:

AssignedTo = '" & Expr1 & "'")

Here is it clear, AssignedTo should be equal to value coming from a control or a variable with the name Expr1.