Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012
    Posts
    15

    Unanswered: Query Returns Different Results in Access vs Excel?

    Hello,

    I have a very simple query which returns activity related to a certain product code.

    I had realized that certain undesirable test accounts had been creeping into the query, so I isolated them into their own query with the intent of filtering them out of the result set with an outer join. This is where the problem lies.

    This this query in Access correctly recognizes when AccountIDs in TestAccts match the AccountID in sysdba_SALESORDER, and will show the accompanying AccountID and AccountName in Access.

    My Excel workbook has a table which is linked to this query, but AccountID and AccountName are blank in all cases in this query, even when the AccountID is showing as matching when the same query is run through Access.

    Any ideas or help would be appreciated. I was expecting this to be a 5 minute fix and it's taking me all afternoon.

    Main Query:

    Code:
    SELECT sysdba_ACCOUNT.ACCOUNT, sysdba_SALESORDER.PRODUCTACCESSTYPEPATHWAY, sysdba_SALESORDER.MODIFYDATE, sysdba_SALESORDER.CREATEDATE, sysdba_SALESORDER.ORDERTYPE, sysdba_SALESORDER.STATUS, sysdba_SALESORDER.GRANDTOTAL, TestAccts.ACCOUNTID, TestAccts.ACCOUNT
    
    FROM (
              sysdba_SALESORDER INNER JOIN sysdba_ACCOUNT 
              ON 
               sysdba_SALESORDER.ACCOUNTID = sysdba_ACCOUNT.ACCOUNTID
            ) LEFT JOIN 
            TestAccts 
              ON sysdba_ACCOUNT.ACCOUNTID = TestAccts.ACCOUNTID
    WHERE (((sysdba_SALESORDER.PRODUCTACCESSTYPEPATHWAY)="SSR"));
    Contents of TestAccts:

    Code:
    SELECT sysdba_ACCOUNT.ACCOUNTID, sysdba_ACCOUNT.ACCOUNT
    FROM sysdba_ACCOUNT
    WHERE (((sysdba_ACCOUNT.ACCOUNT) Like ("*Test *")));
    Eric

  2. #2
    Join Date
    Jan 2012
    Posts
    15
    Please excuse the bump, but I am still having problems getting these two query results to be the same in Excel and Access.

    In the past, I have found Excel errors in data connections to Access queries which parse null values in some way. It surprises me that the above query would not work correctly, as I am not addressing nulls in this query. I would love to understand more about the discrepancies between how Excel and Access interpret the same query, as I use this functionality quite a lot and am alarmed to see buggy results like this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •