MS SQL Query in Access via ODBC connecting to SQL Server

Question

Due to a few limitations (which I won't go into here).. our architecture is using queries in Access running via ODBC SQL Server driver.

The following query produces 2 errors:

SELECT Tbl2.columnid, 
       Tbl2.label, 
       Tbl2.group1, 
       Tbl2.group2, 
       Count(Tbl2.columnid) AS Total 
FROM   (SELECT scanned AS Group1, 
               false   AS Group2, 
               scanned AS Label, 
               scanned AS ColumnID 
        FROM   (SELECT *, 
                       ( quantity - productqty ) AS Variance 
                FROM   order_line 
                WHERE  processed = false) AS Tbl1 
        WHERE   wsid = 1 ) AS Tbl2 
WHERE  Tbl2.columnid = false 
GROUP  BY Tbl2.group1, 
          Tbl2.group2, 
          Tbl2.columnid, 
          Tbl2.label 
ORDER  BY Tbl2.group1 DESC, 
          Tbl2.group2

Error 1: Each GROUP BY Expression must contain at least one column that is an outer reference: (#164) Error 2: The ORDER BY position number 0 is out of range of the number of items in the Select list (#108)

Its important to note that "scanned" is a BIT field in SQL Server (and therefore Group1, Label, ColumnId are also bits). I believe this is the reason why GROUP BY and ORDER BY are treating it as a constant (value=0), resulting in these errors.

But I do not know how to resolve these issues. Any suggestions would be great!

PS - The reason why 2 sub queries are being used is due to other constraints, where we are trying to get ID, Label, Counts for a column in Kanban.

order by case when Tbl2.Group1 = 0 then 'A' else 'B' end? you can convert the Boolean to a character and just make up whatever A/B or B/A you want for respective order.. Probably need to apply for grouping too and on each field if it nags you. — DRapp, May 24 '19 at 01:26
@DRapp Yes, that worked. Not very ideal as I need to have type-based code now (as I only need to do this for bit fields), but works. I will add the solution shortly. — smitra, May 24 '19 at 02:50
@DavidBrowne-Microsoft As I understand it, pass-through queries will run directly at SQL server, and it needs DSN to be setup? We wanted to avoid DSNs as well. — smitra, May 24 '19 at 02:51
Passthrough queries don't requre DSNs. Just put a connection string in the query property sheet. If you're using SQL Server you will really want to use TSQL, not Access SQL, if not for this query then for the next one. eg https://stackoverflow.com/questions/10744170/how-do-i-create-a-passthrough-query-in-access-using-a-dsn-less-connection — David Browne - Microsoft, May 24 '19 at 13:19

score 0 · Answer 1 · answered May 24 '19 at 02:54

Based on DRapp's comment and suggestion.. the following works:

SELECT Tbl2.columnid, 
       Tbl2.label, 
       Tbl2.group1, 
       Tbl2.group2, 
       Count(Tbl2.columnid) AS Total 
FROM   (SELECT IIf(scanned=True, 'Y', 'N') AS Group1, 
               'N'   AS Group2, 
               IIf(scanned=True, 'Y', 'N') AS Label, 
               IIf(scanned=True, 'Y', 'N') AS ColumnID 
        FROM   (SELECT *, 
                       ( quantity - productqty ) AS Variance 
                FROM   order_line 
                WHERE  processed = false) AS Tbl1 
        WHERE   wsid = 1 ) AS Tbl2 
WHERE  Tbl2.columnid = 'N' 
GROUP  BY Tbl2.group1, 
          Tbl2.group2, 
          Tbl2.columnid, 
          Tbl2.label 
ORDER  BY Tbl2.group1 DESC, 
          Tbl2.group2

Not ideal (as the first subquery is generated dynamically, and now needs extra handling if group field is bit. But works! Still open to any other solutions.

MS SQL Query in Access via ODBC connecting to SQL Server

1 Answers1