openrecordset select distinct

[blammo04]

I am having trouble with getting distinct software to filter with openrecordset select distinct.

Here is my openrecordset and select statement.

Set rst = dbs.OpenRecordset("SELECT DISTINCT Software.[Software Name], Software.Version, Software.[Operating System], " & _
            "Software.Status, Software.[Status Date], Software.[Approved Platforms], " & _
            "Software.[Code Type], CUsage.[Cost Center], CUsage.[Entered On] " & _
            "FROM Software INNER JOIN CUsage " & _
            "ON Software.[Software Name] = CUsage.[Software Name] " & _
            "AND Software.Version = CUsage.Version " & _
            "AND Software.[Operating System] = CUsage.[Operating System] " & _
            "WHERE Software.[Software Name] = '" & strSoftwareNameOI & "' " & _
            "AND Software.Version = '" & strVersionOI & "' " & _
            "AND Software.[Operating System] = '" & strOSOI & "'" & _
 "ORDER BY Status DESC ", dbOpenDynaset, dbReadOnly)

My output is showing like this.....

Software Name	Version	Operating System	Status	Status Date	Approved Platforms	Code Type	Cost Center	Entered On
2000Times	1.0	Windows	Pending	1/6/2010			2011	2/17/2010
2000Times	1.0	Windows	Pending	1/6/2010			2012	2/17/2010
2000Times	1.0	Windows	Active	1/20/2010		Non-controlled Access	2011	2/17/2010
2000Times	1.0	Windows	Active	1/20/2010		Non-controlled Access	2012	2/17/2010

But it needs to look like this...

Software Name	Version	Operating System	Status	Status Date	Approved Platforms	Code Type	Cost Center	Entered On
2000Times	1.0	Windows      Pending	1/6/2010			2011	2/17/2010
                                 Pending	1/6/2010			2012	2/17/2010
                                 Active	1/20/2010		Non-controlled Access	2011	2/17/2010
                                 Active	1/20/2010		Non-controlled Access	2012	2/17/2010

If anyone could help I would greatly appreciate it. Thanks

[patjones]

The DISTINCT keyword works only on entire records. In your example, all four of the records are different. For instance, the Status Date is 2011 in the first record and 2012 in the second record. That difference makes these two records non-distinct, and so DISTINCT will have no effect.

If this is for a report, one way for you to get your desired result might be to open the report in Design view, and use the Group & Sort window to group by the Software Name, Version, and Operating System columns - which would allow you to put those columns in a group header and leave the other columns below the header.

I don't believe there is an obvious SQL solution for this situation.

Pat