CODEWITHSUNDEEP
sqlms-access
carmelle
carmelle

Reputation: 9

Table alias not recognized in subquery Access

I have created a query to find the previous row with matching ID criteria. In the WHERE statement, my table alias "t1" is not being recognized as an alias and when running the query I am prompted to enter parameter value for T1. From my understanding subqueries MUST have a table alias. How do I make the "enter parameter value" prompt go away? My SQL code is below. 

SELECT t.NUM, t.ID, tprev.Date_ AS previous_date, tprev.Measurement AS previous_measurement
FROM Table1 AS t LEFT JOIN Table1 AS tprev ON (tprev.Date_ < t.Date_) AND (tprev.id = t.id)
WHERE not exists 
        (select 1
        from Table1 AS t1
        where 
            t1 = t.ID
            and t1.Date_ < t.Date_
            and t1.Date_ > tprev.Date_);

Upvotes: 0

Views: 229

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You have a table alias, but no column. Presumably you intend:

where not exists (select 1
                  from Table1 AS t1
                  where t1.ID = t.ID and
--------------------------^ this part
                        t1.Date_ < t.Date_ and
                        t1.Date_ > tprev.Date_
                 );

Upvotes: 1

Related Questions