Using Count Distinct ??

[tEkHEd]

Hey there.. in my never ending quest to get this DB up n running I have come across another hurdle to overcome...

I am trying to generate a report from an Access 2K front end, with a pass through query..

the problem that I have is that I don't seem able to use the COUNT DISTINCT formulae in Access.. I have checked the code below with Query analyser, and do not recieve an error, however when I put the same code (with a little tweaking to add more ' 's) I get the following error:


Syntax Error (missing operator) in query expression 'COUNT (DISTINCT dbo.tblSWInstallBase.System)'


Can anyone help with this?? here is my SQL code...

SELECT     dbo.tblSWInstallBase.Manufacturer, dbo.tblSWInstallBase.Product, dbo.tblSWInstallBase.Version, dbo.tblSWInstallBase.Release, 
                 COUNT (DISTINCT dbo.tblSWInstallBase.System) AS Installs, dbo.tblSWInstallBase.CostCenter AS CostCentre, 
                 dbo.tblMLCostCenters.StrategicBU AS BusinessUnit
FROM       dbo.tblMLCostCenters INNER JOIN dbo.tblSWInstallBase ON dbo.tblMLCostCenters.CostCenter = dbo.tblSWInstallBase.CostCenter INNER JOIN
                 dbo.tblSWLink ON dbo.tblSWInstallBase.Manufacturer = dbo.tblSWLink.Manufacturer AND dbo.tblSWInstallBase.Product = dbo.tblSWLink.Product AND 
                 dbo.tblSWInstallBase.Version = dbo.tblSWLink.Version AND dbo.tblSWInstallBase.Release = dbo.tblSWLink.Release
GROUP BY dbo.tblSWInstallBase.Manufacturer, dbo.tblSWInstallBase.Product, dbo.tblSWInstallBase.Version, dbo.tblSWInstallBase.Release, dbo.tblMLCostCenters.StrategicBU, dbo.tblSWInstallBase.CostCenter
HAVING    ((dbo.tblSWInstallBase.Manufacturer LIKE ("'" & '[forms]![frmSWSelect]![cboSWMan]' & "'")) AND (dbo.tblSWInstallBase.Product LIKE ("'" & '[forms]![frmSWSelect]![cboSWProd]' & "'")) AND (dbo.tblSWInstallBase.Version LIKE ("'" & '[forms]![frmSWSelect]![cboSWVer]' & "'")))

I think that this is because Access doesn't support Count Distinct... I have tried to run the query through VBA also, but again without success... :¬(

Thanks in adv for any help

 

[VicM]

tEkHEd:

DISTINCT is a reserved word and applies to the values in a record. So in a one table recordset, DISTINCT will return only unique records.

Put DISTINCT after the SELECT word and the COUNT function should be used without the DISTINCT keyword.

This should give you what you want.

Hope this helps,

Vic

 

[tEkHEd]

OK, I have tried that, and am now getting a prob with the INNER JOIN function with the error:


JOIN Expression NOT supported


URGH...

Tks

 

[VicM]

tEkHEd:

The SQL statement you've written using JOINS is confusing Access because it doesn't know how to parse the ANDs. I suggest you enclose each JOIN group within parentheses.

If you are having trouble resolving that, why not open a query in design mode and create your query. Test it out. If it works, then switch over to the SQL window to see how the syntax is displayed.

You can then copy that string and paste it into your code.

Hope this helps,

Vic

 

[tEkHEd]

Hey Vic

To be honest that was how I was setting up the query.. I have to create pass through queries due to the size of tables, and have created the query in SQL Query Analyser... I find that there are several instances when SQL code has to be amended and tweaked quite a lot to work with Access

I will try to put the joins in ( )'s but I don't have much hope for that...

What I am trying to do is parse the query above through to a report template, but am unsure how I can code the VBA for that... I find that setting up the queries this way is more effective..

Private Sub cmdRunRpt_Click()
 On Error GoTo Err_cmdRunRpt_Click

Dim strSQLQry3 As String

strSQLQry3 = "SELECT tblSWInstallBase.Manufacturer, tblSWInstallBase.Product, tblSWInstallBase.Version, tblSWInstallBase.Release, " _
                    & "COUNT(DISTINCT tblSWInstallBase.System) AS Installs, tblSWInstallBase.CostCenter AS CostCentre, " _
                    & "tblMLCostCenters.StrategicBU AS BusinessUnit " _
                    & "FROM tblMLCostCenters INNER JOIN " _
                    & "tblSWInstallBase ON tblMLCostCenters.CostCenter = tblSWInstallBase.CostCenter INNER JOIN " _
                    & "tblSWLink ON tblSWInstallBase.Manufacturer = tblSWLink.Manufacturer AND tblSWInstallBase.Product = tblSWLink.Product AND " _
                    & "tblSWInstallBase.Version = tblSWLink.Version And tblSWInstallBase.Release = tblSWLink.Release " _
                    & "GROUP BY tblSWInstallBase.Manufacturer, tblSWInstallBase.Product, tblSWInstallBase.Version, tblSWInstallBase.Release, " _
                    & "tblMLCostCenters.StrategicBU , tblSWInstallBase.CostCenter " _
                    & "HAVING ((tblSWInstallBase.Manufacturer LIKE " & "'" & "%" & ([Forms]![frmSWSelect]![cboSWMan]) & "%" & "'" & ")"

I want to do something like:

DoCmd.OpenReport "rptSWInstalls", acViewPreview

with the sql above dictating the output..

 

[VicM]

tEkHEd:

Thought just occurred. Have you checked out the pass-through_query help in Access2K?

According to the way I'm reading it, you can write your query in the syntax the ODBC database recognizes, i.e., you don't need to convert to the syntax Access requires.

When creating the query, you need to tell Access it's a pass-thru, and you also need to set the ODBC string in the query property sheet. There are other options, but they are discussed in the help.

With the pass-thru query created, you can then use a control button, or whatever mechanism you need, to execute the query directly. Since the query is tagged as a pass-thru, Access doesn't try to interpret anything, and just hands it off to the ODBC for execution.

I hope I'm not insulting you, but I've never really worked with pass-thrus, and just decided to get some background with the help screen.

Let me know if this helped you.

Vic

 

[tEkHEd]

Hey Vic...

Yeah that is kind of how I did it...

I created a View on the SQL Server with the following:

CREATE VIEW dbo.vSWInstances
AS
SELECT     TOP 100 PERCENT RTRIM(dbo.tblSWInstallBase.CostCenter) AS CostCentre, RTRIM(dbo.tblSWInstallBase.Manufacturer) AS Manufacturer, 
                      RTRIM(dbo.tblSWInstallBase.Product) AS Product, RTRIM(dbo.tblSWInstallBase.Version) AS Version, RTRIM(dbo.tblSWInstallBase.Release) 
                      AS Release, COUNT(DISTINCT dbo.tblSWInstallBase.System) AS Instances, dbo.tblMLCostCenters.StrategicBUName
FROM         dbo.tblSWInstallBase LEFT OUTER JOIN
                      dbo.tblMLCostCenters ON dbo.tblSWInstallBase.CostCenter = dbo.tblMLCostCenters.CostCenter
GROUP BY RTRIM(dbo.tblSWInstallBase.CostCenter), RTRIM(dbo.tblSWInstallBase.Manufacturer), RTRIM(dbo.tblSWInstallBase.Product), 
                      RTRIM(dbo.tblSWInstallBase.Version), RTRIM(dbo.tblSWInstallBase.Release), dbo.tblMLCostCenters.StrategicBUName
ORDER BY dbo.tblMLCostCenters.StrategicBUName

This will list ALL the data in the table, with the required fields...

I then created a dynamic pass through query, which points to the View:

SELECT * FROM vSWInstances WHERE Product LIKE '%DYNAMICALLY UPDATING FIELD%'

The query is updated by the selection from a combobox, I had to code this in VBA:

Private Sub cmdRunRpt_Click()
 On Error GoTo Err_cmdRunRpt_Click
   
    Dim dbs As Database, qdf As QueryDef
    Dim strSQLQry3 As String
    Dim strSWProd As String

    ' Return reference to current database.
    Set dbs = CurrentDb
        
    ' Refresh QueryDefs collection.
    dbs.QueryDefs.Refresh
    
    
    strSQLQry3 = "SELECT * FROM vSWInstances WHERE Product LIKE '%" & Me.cboSWProd & "%' "
                    '& "AND Version LIKE '%" & Me.cboSWVer & "%' "
    
    
    For Each qdf In dbs.QueryDefs
        If qdf.Name = "qrySWInst" Then
            qdf.SQL = strSQLQry3
        End If
    Next qdf
    
   DoCmd.OpenReport "rptSWInstalls", acViewPreview


Exit_cmdRunRpt_Click:
    Exit Sub

Err_cmdRunRpt_Click:
    MsgBox Err.Description
    Resume Exit_cmdRunRpt_Click
    

End Sub

Thats it... it took some time to figure this little lot out I can tell you.. but I think that it is a much better way to retrieve the data sets that I require, than goin about it the way I was previously by pointing the RecordSource of the Report to a pass through query.....

The next section that I have to work on now is yet another combo box which lists the available report types.. (the one above will report software installations per BusinessUnit) then create another dynamic query which will be dependant upon user selection for SWProduct AND ReportType....

BTW passthrough queries are very simple to set up and use, the majority of my queries are passthrough already...

Hope that this solution helps u also...