using multiple functions in a single select clause (1 Viewer)

[subbu]

I want to write a query as :
select ID, count(distinct(name))
from user
where id is not null
group by ID
having count(distinct(name))>1

but looks like this query fails in access due to multiple functions.
Can someone help me how should i be writing this.
NOte. Id is not a primary key for this table.

 

[David R]

Try HAVING (([ID] Is Not Null) AND (Count([NameField])>1));

Name would be a reserved fieldname.

 

[subbu]

David,
Thanks for your reply.
This query works fine in oracle.
In access it is giving me error on the first line where i have count(distinct(name).
Looks like Access can not take nested functions.
I am new to access.

 

[David R]

Access does have issues with nested SQL statements at times. I usually separate the Distinct work into a query and then base my second query on that one.

However: I may be misreading your query, but if you're grouping by NameField, what is the point of getting DISTINCT NameFields?

For example, this:
SELECT DISTINCT tableShipDays.LocID, Count(tableShipDays.ShipDay) AS CountOfShipDay
FROM tableShipDays
GROUP BY tableShipDays.LocID
HAVING (((tableShipDays.LocID) Is Not Null) AND ((Count(tableShipDays.ShipDay))>1));

had the same result as this:
SELECT tableShipDays.LocID, Count(tableShipDays.ShipDay) AS CountOfShipDay
FROM tableShipDays
GROUP BY tableShipDays.LocID
HAVING (((tableShipDays.LocID) Is Not Null) AND ((Count(tableShipDays.ShipDay))>1));

 

[subbu]

David,
What you interpreted looks logical if the two columns have got unique combinations.
But in my case there can be pairs like say
ID Name
1 aa
1 aa
1 aa
2 bb
2 cc
3 dd
4 rr
4 tt
4 ss
In this case
I want is
ID count(name)
2 2
4 3

Note that I dont want my query to rerieve 1 as although it has got multiple rows in
all the rows name has got same value "aa".

And if i write a query without count(distinct(name) clause then my query retrieves
row 1 3
+ all the other rows...
I dont want my query to retrieve
this 1 id

I hope you are getting what i need.

Thanks once again for taking time to look into my problem !

 

[subbu]

David,
I am trying to break my query into two to take care of nested function.
But not able to figure out how should i be doing it.
Can you please help me?

 

[David R]

Okay, now I understand what you're asking. I don't know if one is faster than the other, but there are two ways to make the first query:
SELECT DISTINCT ID, Code
FROM tableCodes;
or
SELECT ID, Code
FROM tableCodes
GROUP BY ID, Code;

After that your second query can Group By Id and Count Code:
SELECT ID, Count(Code)
FROM FirstQuery
GROUP BY ID
HAVING ((ID Is Not Null) AND (Count(Code)>1));

 

[subbu]

Hi David,
Yes that was perfect!!
Thanks a lot!!
I had never thought writing my Oracle working query in Access will be that different!!

Thanks a lot!!

Subbu.