Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How to do this in Access

Discussion in 'Information Technology' started by Wayne Wengert, Jul 28, 2006.

Tags:
  1. Wayne Wengert

    Wayne Wengert
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    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" wrote:

    > 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
    >
    >
    >
     
  4. Wayne Wengert

    Wayne Wengert
    Expand Collapse
    Guest

    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

    "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message
    news:E89623EE-C860-4840-B633-745EC54ECB7A@microsoft.com...
    >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" wrote:
    >
    >> 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
    >>
    >>
    >>
     
  5. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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)

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Wayne Wengert" <wayneSKIPSPAM@wengert.org> wrote in message
    news:OTUJNqCnGHA.3784@TK2MSFTNGP03.phx.gbl...
    > 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
    >
    > "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message
    > news:E89623EE-C860-4840-B633-745EC54ECB7A@microsoft.com...
    >>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" wrote:
    >>
    >>> 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
    >>>
    >>>
    >>>

    >
    >
     
  6. Wayne Wengert

    Wayne Wengert
    Expand Collapse
    Guest

    Douglas;

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

    Wayne

    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:uNqekFDnGHA.2256@TK2MSFTNGP04.phx.gbl...
    > 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)
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "Wayne Wengert" <wayneSKIPSPAM@wengert.org> wrote in message
    > news:OTUJNqCnGHA.3784@TK2MSFTNGP03.phx.gbl...
    >> 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
    >>
    >> "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message
    >> news:E89623EE-C860-4840-B633-745EC54ECB7A@microsoft.com...
    >>>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" wrote:
    >>>
    >>>> 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
    >>>>
    >>>>
    >>>>

    >>
    >>

    >
    >
     

Share This Page