Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    DISTINCTROW (v2003 sp2)

    I'm having a brain freeze. I've done a search and can't figure out why I can't get unique records in my query. I've concatenated the FUNCTION, ACTIVITY, and QUALIFIER fields. I want unique records based on that. DISTINCTROW won't work for me. What am I doing wrong? Thanks.


    SELECT DISTINCTROW [ACTIVITY] & "." & [FUNCTION] & "." & [QUALIFIER] AS AFQ, Afq_wmmon.OP_ID, Afq_wmmon.OP_NAME, Afq_wmmon.STATUS, Afq_wmmon.FUNCTION, Afq_wmmon.ACTIVITY, Afq_wmmon.DESC, Afq_wmmon.QUALIFIER
    FROM wmmon_active_model_qry LEFT JOIN Afq_wmmon ON wmmon_active_model_qry.[OP #] = Afq_wmmon.OP_ID
    WHERE (((Afq_wmmon.STATUS)="active"));
    Attached Files Attached Files
    thanks
    christine

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DISTINCTROW (v2003 sp2)

    Since the records have different OP_IDs and OP_NAMEs, the query returns multiple records for the same AFQ. If you want one record per AFQ, you must omit all OP_ID and OP_NAME from the query.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DISTINCTROW (v2003 sp2)

    So there is no way to designate which field I wish to perform the distinct function against?
    thanks
    christine

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DISTINCTROW (v2003 sp2)

    No, DISTINCTROW specifies that the underlying entire record must be unique, DISTINCT that the combination of the selected fields must be unique.

    It wouldn't make sense to specify uniqueness on a single field. What would the non-unique fields be supposed to display?

    You can create a Totals query (select View | Totals) and specify that you want to group on one or more fields, and that you want to aggregate the other fields. For those fields, you can return the sum, minimum, last, count etc.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DISTINCTROW (v2003 sp2)

    I was just able to get back into today. I tried your proposal to nest the queries and once I stripped each one down of the unnecessary fluff, I was able to get the unique response that I needed. Thanks for the direction.
    thanks
    christine

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •