Get first unique values 2

[DBLoser]

I've searched extensively for a solution for this but can't seem to find one I can get to work although there are a few similar ones out there. Anyway... here's a sample of my data (Using Access 2000):

ActionID DMRNum Date XXXX XXXX
500001 1695 xx/xx/xxxx xxxx xxxx
500002 1695 xx/xx/xxxx xxxx xxxx
500003 1695 xx/xx/xxxx xxxx xxxx
500004 1696 xx/xx/xxxx xxxx xxxx
500005 1697 xx/xx/xxxx xxxx xxxx
500006 1697 xx/xx/xxxx xxxx xxxx

In my table there are over 700 ActionIDs (and growing). I need to build a series of charts based on unique DMRNums. There is also a date field but when I built queries using dates and grouped by DMRNums it seemed to work but I got multiple counts of duplicate DMRNums because some of them fell in different time periods (ie, yearly, quarterly, monthly).

I tried using the DISTINCT statement but it only seems to work when I use only the DMRNum field. When I include all of the fields in the SELECT statement the DISTINCT statement doesn't seem to have any effect at all.

Here is the SQL statement that works properly (simple one really):

SELECT DISTINCTROW drtrend.DMRNUMBER
FROM drtrend
GROUP BY drtrend.DMRNUMBER;

But when I add the other fields from the table it doesn't work anymore, here it is:

SELECT DISTINCTROW drtrend.DMRNUMBER, drtrend.ACTIONID, drtrend.DATEOPEN, drtrend.NON_CONFOR, drtrend.CAUSEREASO, drtrend.CORRECTIVE, drtrend.ROOTCAUSEI, drtrend.CHARACTER0, drtrend.FIELD1
FROM drtrend
GROUP BY drtrend.DMRNUMBER, drtrend.ACTIONID, drtrend.DATEOPEN, drtrend.NON_CONFOR, drtrend.CAUSEREASO, drtrend.CORRECTIVE, drtrend.ROOTCAUSEI, drtrend.CHARACTER0, drtrend.FIELD1;

I think the problem has something to do with the DISTINCTROW in that the rows are not the same but only values within. I am at a loss as to how to do this.

Many thanks in advance,

Bruce Brannan

 

[PHV]

And what about SELECT DISTINCT ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DBLoser]

I tried this using only the field DMRNum:

SELECT DISTINCT drtrend.DMRNUMBER
FROM drtrend;

And it works perfectly giving 424 records. But when I add the rest of the fields it no longer works. I'm know very little about SQL so it is probably the way the SQL statement is written but here's the statement (I only added two more fields to keep the statement shorter):

SELECT DISTINCT drtrend.DMRNUMBER, drtrend.ACTIONID, drtrend.DATEOPEN
FROM drtrend;

This runs without error but gives all 757 records.

 

[PHV]

Given your posted sample, what is your expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DBLoser]

From the list of records I need the unique DMRNums but no duplicates of them. I also need the rest of the fields to accompany but the determining one is the DMRNum.

Expected result:

1695
1696
1697

 

[PHV]

I also need the rest of the fields to accompany
But from which row ? The last ActionID for each DMRNum ?
SELECT A.DMRNUMBER, A.ACTIONID, A.DATEOPEN, A.NON_CONFOR, A.CAUSEREASO, A.CORRECTIVE, A.ROOTCAUSEI, A.CHARACTER0, A.FIELD1
FROM drtrend A INNER JOIN
(SELECT DMRNUMBER, Max(ACTIONID) As LastAction FROM drtrend GROUP BY DMRNUMBER
) B ON (A.DMRNUMBER=B.DMRNUMBER) AND (A.ACTIONID=B.LastAction)
;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

All of these records would be considered DISTINCT:

ActionID DMRNum Date Field1 Field2
500001 1695 10/15/2004 1 2
500002 1695 10/15/2004 2 2
500003 1695 10/16/2004 2 2

because there is SOMETHING different on each one. The first two records have the same date and the same field2, but field1 is different. Record 3 has a different date.

If you want a single record returned from this set, how do you determine which ONE record you want? The one with the highest ActionID? The one with the latest date? The one with the earliest date?


Leslie

 

[DBLoser]

You asked two really good questions, I hadn't thought about them. I guess I really do lose the row data when I get only the first occurence of each DRNum. So... I guess I would pick the row of data accompanying the date of the first occurrence of DMRNum. I can't get it both ways.

 

[PHV]

I would pick the row of data accompanying the date of the first occurrence of DMRNum
SELECT A.DMRNUMBER, A.ACTIONID, A.DATEOPEN, A.NON_CONFOR, A.CAUSEREASO, A.CORRECTIVE, A.ROOTCAUSEI, A.CHARACTER0, A.FIELD1
FROM drtrend A INNER JOIN
(SELECT DMRNUMBER, Min(ACTIONID) As FirstAction FROM drtrend GROUP BY DMRNUMBER
) B ON (A.DMRNUMBER=B.DMRNUMBER) AND (A.ACTIONID=B.FirstAction)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DBLoser]

Thanks PHV and Lespaul for your help. I now have a better understanding of the DISTINCT commands and how MINs and MAXs work.

The code worked perfectly.