DISTINCTROW (v2003 sp2)

Topic

New Reply

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”));

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote