none
Access 2016: summary of union data gives too big results with SQL back end RRS feed

  • Question

  • Odd results from a long-standing Access query which behaves differently now it has a SQL back end.

    The user wants to see all the rows and columns of a crosstab, even where there is no data.

    The row and column values are codified in tables.

    In order to create the default cells for the crosstab, I do a cross-product of the two code tables and put a zero record counter and value on them.

    I then create a "real" query from the data with the correct values.

    The next step is to union the two queries together.

    So the first query looks like "select c1.RowValue, c2.ColumnValue, 0 As RowCount, 0 As RowValue from RowCodes c1, ColumnsCodes c2"

    And the second query looks like "select d.RowValue, d.ColumnValue, 1 As RowCount, d.RowValue from RealData d Where VariousCriteria"

    The union looks like "select * from FirstQuery UNION ALL select * from SecondQuery"

    The union query has the correct rows/values in it.

    My problem comes when I try to add anything up.

    Running "select sum(RowCount) from UnionQuery" doesn't give a count of the rows from the real data, it gives an eleven-digit integer seemingly unrelated to the number of rows.

    As I said, this didn't happen with Access as a back end, only when the query is passed to SQL Server.

    What is going on? How can I get the correct figures?

    Many thanks in advance,
    e.


    Tuesday, June 5, 2018 12:37 PM

All replies

  • Very curious. 

    To debug this, what is the sum over just the second part of the union query?

    Also, in SSMS you can turn on the execution plan, and perhaps gain further insights.


    -Tom. Microsoft Access MVP

    Tuesday, June 5, 2018 1:11 PM
  • Very curious. 

    To debug this, what is the sum over just the second part of the union query?

    Also, in SSMS you can turn on the execution plan, and perhaps gain further insights.


    -Tom. Microsoft Access MVP

    "Select sum(RowCount) from SecondQuery" gives the expected number of rows.

    "in SSMS you can turn on the execution plan" How?

    Thanks,
    e.

    Tuesday, June 5, 2018 1:44 PM
  • Hallo exotericist,

    if think, RowCount is a SQL Server key word - it could be that this is a problem.
    Try [RowCount] or another name.

    regards

    Volker

    Tuesday, June 5, 2018 2:12 PM
  • The real (non-anonymised) code doesn't use the word RowCount.

    Try using "RowCounter" instead.

    Thanks,
    e.

     
    Tuesday, June 5, 2018 3:36 PM
  • By choosing Help > View Help, and searching for Execution Plan.

    -Tom. Microsoft Access MVP

    Wednesday, June 6, 2018 2:21 AM
  • By choosing Help > View Help, and searching for Execution Plan.

    -Tom. Microsoft Access MVP

    That gives me lots of info on how to see the execution plan for a query in SSMS.

    I found this, though, which shows how to turn tracing on Optimizing Microsoft Office Access Applications Linked to SQL Server.

    Many thanks for your help.

    I'll post what Access is doing if I find it.

    (The numbers are being multiplied by 4639656 (27617*8*3*7) for what it's worth.)

    Regards,
    e.

    Wednesday, June 6, 2018 9:39 AM
  • This is what the trace tells me Access requests from SQL.

    Which is what I'd expect.

    The error must be happening inside Access.

    Bug?

    Thanks,
    e


    SQLExecDirect: SELECT Config, nValue FROM MSysConf
    
    SQLExecDirect: SELECT "dbo"."RowValues"."RowValueID","dbo"."ColumnValues"."ColumnValueID" FROM "dbo"."RowValues","dbo"."ColumnValues"
    
    SQLPrepare: SELECT "RowValueID","RowValueText"  FROM "dbo"."RowValues"  WHERE "RowValueID" = ? OR "RowValueID" = ? OR "RowValueID" = ? OR "RowValueID" = ? OR "RowValueID" = ? OR "RowValueID" = ? OR "RowValueID" = ? OR "RowValueID" = ? OR "RowValueID" = ? OR "RowValueID" = ?
    
    SQLExecute: (MULTI-ROW FETCH)
    
    SQLPrepare: SELECT "ColumnValueID","ColumnValueText"  FROM "dbo"."ColumnValues"  WHERE "ColumnValueID" = ? OR "ColumnValueID" = ? OR "ColumnValueID" = ? OR "ColumnValueID" = ? OR "ColumnValueID" = ? OR "ColumnValueID" = ? OR "ColumnValueID" = ? OR "ColumnValueID" = ? OR "ColumnValueID" = ? OR "ColumnValueID" = ?
    
    SQLExecute: (MULTI-ROW FETCH)
    
    SQLExecute: (MULTI-ROW FETCH)
    
    SQLExecute: (MULTI-ROW FETCH)
    
    SQLExecDirect: (SELECT 2018 ,"dbo"."RowValues"."RowValueID" ,"dbo"."ColumnValues"."ColumnValueID" ,0  FROM "dbo"."RowValues","dbo"."ColumnValues" ) UNION ALL (SELECT "FinYear" ,"RowValue" ,"ColumnValue" ,"BodyValue"  FROM "dbo"."CrosstabData" )

    Wednesday, June 6, 2018 2:06 PM