0 Replies Latest reply on Feb 27, 2012 6:13 AM by Gregg Ferguson

    Why doesn't Tableau like this query - Connection to Access query is stuck and returns no records

    Gregg Ferguson

      I am trying to make a connection to the below Access query via a data extract.  This query runs in about a second on Access and returns approx 400,000 records - however Tableau seems to have issues with it - after about 30 minutes of attempting to import data zero records have been returned so far.

       

      The purpose of the quey is to return certain characteristics of loans in a pool - the fields Deal_Name, LoanID and Statu Lag0 are from the current period date for a given LoanID and the fields Status Lag1 and Value are for the same LoanID only from the prior period date where the dates are 1 month apart.

       

      SELECT [Master].DEAL_NAME,

       

      [Master].LoanID,

       

      [Master].DATE,

       

      (SELECT TOP 1 Status FROM [Master] AS Dupe WHERE Dupe.LoanID = [Master].LoanID AND Dupe.DATE=dateadd("m",-1, [Master].DATE)) AS [Status Lag1],

       

      [Master].Status AS [Status Lag0],

       

      (SELECT TOP 1 Balance FROM [Master] AS Dupe WHERE Dupe.LoanID = [Master].LoanID AND Dupe.DATE=dateadd("m",-1, [Master].DATE)) AS [Value]

      FROM [Master];

       

          Can anyone see what the issue is or suggest a better way to handle?