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