How to do this in Access

[Wayne Wengert]

The query below works fine in SQL Server 2000 but fails in Access 2003 - it
doesn't seem to accept the "DISTINCT"

How can this be accomplished in Access 2003?

==============================
SELECT ResidentList.Area, Count(DISTINCT ISNULL(ResidentList.Address2,''))
AS CountOfID
FROM ResidentList
GROUP BY ResidentList.Area
ORDER BY ResidentList.Area

 

[Guest]

I am not sure of your problem. If this will not do it for you then post
sample is input data and results you expect.
SELECT ResidentList.Area, ResidentList.Address2,
Count(ResidentList.Address2) AS CountOfAddress2
FROM ResidentList
GROUP BY ResidentList.Area, ResidentList.Address2;

 

[Wayne Wengert]

The problem occurs when I add the DISTINCT predicate. Using any text field
in any table, the error occurs. I am looking for the correct way to specify
DISTINCT in an Access 2003 Query.

Wayne

 

[Douglas J. Steele]

You can't specify distinct for a specific field in Access.

One approach is to create a subquery that returns only the distinct
combinations of Area and Addess2, then use that as the basis for the rest of
your query. (Also, you need to replace the ISNULL with NZ in Access).

The following should do what you're trying to do:

SELECT Area, Count(Address) AS CountOfID
FROM
(SELECT DISTINCT Area, Nz(Address2, "") AS Address
FROM ResidentList) AS SubQ
GROUP BY ResidentList.Area
ORDER BY ResidentList.Area

(other than the difference between Nz and ISNULL, that should also work in
SQL Server)

 

[Wayne Wengert]

Douglas;

That explains it. Your suggestion works fine. Thank you.

Wayne