Unique Values within a Filtered Query??

[chrishannah8386]

Hello

I'm wondering if anyone can point me in the right direction.
I want to count the number of Distinct Customers in my table that have a Lead assigned and a Level and a Start Date

I'm trying the Distinct function but that can retrieve a list of unique Customers fine but then when I add the Lead field and add NOT NULL and the Level NOT NULL and Start Date NOT NULL it duplicates the customers (I realise that this is because those records/rows are DISTINCT but i only care about the Customer being distinct

I want to end up with the number of unique customers that have these 3 criteria.

SELECT DISTINCTROW [Account Activity].Customer, [Account Activity].[Lead], [Account Activity].[Level], [Account Activity].[Start Date]
FROM [Account Activity]
GROUP BY [Account Activity].Customer, [Account Activity].[Activ8 Lead], [Account Activity].[Level], [Account Activity].[Start Date]
HAVING ((([Account Activity].[Lead]) Is Not Null) AND (([Account Activity].[Level]) Is Not Null) AND (([Account Activity].[Start Date]) Is Not Null));


I dont know a lot about this sort of thing and i've managed to muddle through till now, but i'm stuck.

 

[Micron]

You are not using DISTINCT, you are using DISTINCT ROW - there is a difference, but it does not always matter. It depends on your data structure, which I cannot see. Try DISTINCT. Easiest to do this in SQL view, but you can also right click on the query design background (not grid) and look at the property sheet where it says "Unique Values" and "Unique Records" (DISTINCTROW).

Did you turn on the totals feature but not create any aggregate functions (SUM, AVG, etc)? Looks like, because there is no WHERE clause, but instead you show "HAVING".

 

[chrishannah8386]

You are not using DISTINCT, you are using DISTINCT ROW - there is a difference, but it does not always matter. It depends on your data structure, which I cannot see. Try DISTINCT. Easiest to do this in SQL view, but you can also right click on the query design background (not grid) and look at the property sheet where it says "Unique Values" and "Unique Records" (DISTINCTROW).

Did you turn on the totals feature but not create any aggregate functions (SUM, AVG, etc)? Looks like, because there is no WHERE clause, but instead you show "HAVING".

Hello Micron

thanks for getting back to me, i have tried Distinct and distinct row and both give me the same result so i do not believe it is this.
I don't know a lot about this but the reason that DISTINCT is in there is because Access has put it in. So i have taken this out and added the criteria as a Where Clause and this appears to work well.

SELECT [Account Activity].Customer
FROM [Account Activity]
WHERE ((([Account Activity].[Lead]) Is Not Null) AND (([Account Activity].[Level])<>"none") AND (([Account Activity].[Start Date]) Is Not Null))
GROUP BY [Account Activity].Customer;

 

[Micron]

i have tried Distinct and distinct row and both give me the same result so i do not believe it is this.

Your second sql is a different matter - you are only asking for one field to be returned based on 3 criteria fields. You will get Customer as many times as all 3 are satisfied regardless of DISTINCT or not, as long as the criteria fields are different. This will not be apparent to you because you are not showing them. Turn them on and you'll see what I mean. What I wrote should apply to your first sql.