MS Access to SQL query issue

Question

I have this query that is supposed to be used in MS Access, but the database is an SQL database. When I run this query in an SQL environment, it works perfectly. However, when ran in MS Access, I get errors. I know little about SQL to begin with (coming from MySQL), and even less about MS Access.

The query is supposed to give me the total number of people within a certain bidder type who bid on an item (whether they won it or not), the total price of items won within that bidder type, and the bidder type, all for a single auction. Here is the query below.

SELECT     Total.count, SUM(dbo_tblItem.item_premium + dbo_tblItem.item_pr) AS SumTotal, dbo_tblBidder.bidder_type
FROM         dbo_tblBidder LEFT OUTER JOIN
                  dbo_tblItem ON dbo_tblItem.item_bidder_number = dbo_tblBidder.bidder_number   AND 
                  dbo_tblItem.item_sale_id = dbo_tblBidder.bidder_sale_id LEFT OUTER JOIN
                      (SELECT     COUNT(bidder_type) AS count, bidder_type
                        FROM          dbo_tblBidder AS tblBidder_1
                        WHERE      (bidder_sale_id = 235)
                        GROUP BY bidder_type) AS Total ON dbo_tblBidder.bidder_type = Total.bidder_type
WHERE     (dbo_tblBidder.bidder_sale_id = 235)
GROUP BY dbo_tblBidder.bidder_type, Total.count
ORDER BY dbo_tblBidder.bidder_type

MS Access tells me:

Syntax error (missing operator) in query expression ".

Then, it highlights "mber" from dbo_tblBidder.bidder_number where it says:

dbo_tblItem ON dbo_tblItem.item_bidder_number = dbo_tblBidder.bidder_number

I don't know if the highlighting is actually part of anything or not.

Answer 1

When you join more than 2 tables, Access requires parentheses and is very finicky about their placement. (Although one of your query's data sources is a subquery rather than an actual table, it's treated same as a table regarding joins and parentheses.) Suggest you build this as a new query in Access' query designer simply to see how it places the parentheses for your joined tables.

"count" is a reserved word, so I bracketed that name everywhere it appeared in the query to reduce the chance of confusing the database engine.

Use LEFT JOIN instead of LEFT OUTER JOIN for Access' db engine.

I think this may be close to what you need.

SELECT
    Total.[count],
    SUM(dbo_tblItem.item_premium + dbo_tblItem.item_pr) AS SumTotal,
    dbo_tblBidder.bidder_type
FROM         
    (dbo_tblBidder LEFT JOIN dbo_tblItem
        ON (dbo_tblItem.item_bidder_number = dbo_tblBidder.bidder_number
           AND dbo_tblItem.item_sale_id = dbo_tblBidder.bidder_sale_id)
        )
    LEFT JOIN (
        SELECT     COUNT(bidder_type) AS [count], bidder_type
        FROM          dbo_tblBidder
        WHERE      bidder_sale_id = 235
        GROUP BY bidder_type
        ) AS Total
        ON dbo_tblBidder.bidder_type = Total.bidder_type
WHERE     dbo_tblBidder.bidder_sale_id = 235
GROUP BY dbo_tblBidder.bidder_type, Total.[count]
ORDER BY dbo_tblBidder.bidder_type;

Answer 2

Try putting brackets around your ON clause, e.g.,

left outer join dbo_tblItem on (dbo_tblItem.item_bidder_number = dbo_tblBidder.bidder_number
    and dbo_tblItem.item_sale_id = dbo_tblBidder.bidder_sale_id)

Answer 3

Your question sounds like you have the SQL Server tables linked in Access.
Are you trying to execute the query by code or in the Access query designer?

No matter how exactly you execute the query, it is possible to send it directly to SQL Server as well, circumventing Access.

Doing it this way has the advantage that you can use SQL Server's SQL dialect, which IMO is more powerful than Access's SQL dialect.
Plus, SQL Server is faster than Access in my experience.

If you are using the query designer:

You can create a pass-through query in the query designer:
How to create an SQL pass-through query in Access

If you want to execute the query by code:
Here is an example function which will send a query directly to SQL Server and return a Recordset:

Public Function OpenRecordset(ByVal SQL As String) As DAO.Recordset    
    Dim QD As QueryDef
    Set QD = CurrentDb.CreateQueryDef("")
    With QD
        .Connect = "Your connection string to the SQL Server database"
        .ReturnsRecords = True
        .SQL = SQL
        Set OpenRecordset= QD.OpenRecordset
        QD.Close
    End With
    Set QD = Nothing    
End Function

Usage example:

Public Function Test()    
    Dim RS As DAO.Recordset        
    Set RS = OpenRecordset("select getdate()")        
    MsgBox RS.Fields(0)        
    RS.Close
    Set RS = Nothing    
End Function

This will return the current date and time by using the SQL Server function getdate().