Correlated Subquery doesn't work

[D Witherspoon]

Whenever I try and run this query a pop-up box comes up asking for the value
of tblWorkLog_Main.GuageID.

Why is this happening. According to the following 2 articles my query
should work. I've never had this problem in SQL Server.
http://www.tek-tips.com/faqs.cfm?fid=4058
http://support.microsoft.com/?kbid=209066

Both queries work fine independantly (as long as I remove the reference to
tblWorkLog_Main in the sub query if I run it by itself).

I'm using MS Access 2000.


SELECT WorkLogID, GaugeID, NextCertDue,

(SELECT TOP 1 NextCertDue
FROM tblWorkLog wl
WHERE GaugeID =tblWorkLog_Main.GuageID
AND
WorkLogID <>1
ORDER BY NextCertDue Desc) AS DueDate


FROM tblWorkLog tblWorkLog_Main
ORDER BY GaugeID, NextCertDue DESC;

 

[Tom Ellison]

Dear D:

You have misspelled "Gauge" as "Guage"

That should do it!

Tom Ellison

 

[D Witherspoon]

Geesh ! Thanks...

Now I get this problem however....

It seems that it doesn't like using Top 1. Arg!

At most one record can be returned by this subquery. (Error 3354)
A subquery of this kind cannot return more than one record. Revise the
SELECT statement of the subquery to request only one record.

 

[Michel Walsh]

Hi,


Typo. Guage (goo-age) is not Gauge (go-ge).

Probably late in the day (or early in the morning... )

Hoping it may help,
Vanderghast, Access MVP

 

[Tom Ellison]

Dear Geesh:

If there is a tie for first place, TOP 1 will return more than one row. In
the context of your subquery this would mean you have multiple rows for the
specified GaugeID with the same maximum value of NextCertDue. You might
want to check this.

I recommend changing the subquery:

(SELECT MAX(NextCertDue)
FROM tblWorkLog wl
WHERE GaugeID =tblWorkLog_Main.GuageID
AND WorkLogID <>1) AS DueDate

I believe that should fix the new problem.

Tom Ellison

 

[Michel Walsh]

Hi,


ah. Try

.... (SELECT MAX(NextCertDue) FROM ... ) ...


instead of

.... ( SELECT TOP 1 NextCertDue FROM ... ORDER BY NextCertDue )


since it may occur you there is more than just one record with a given
NextCertDue value, and thus, the TOP 1 would return more than one record
(in Jet, by default, in MS SQL Server, through option WITH TIES).



Hoping it may help,
Vanderghast, Access MVP

 

[D Witherspoon]

I read somewhere about Top 1 possibly returning two records if there is a
tie. However upon testing I wrote a query that would return two identicle
records and used top 1 and only one record was produced.

Also, I tried the following query using a group by statment with the same
error. A little frustrating, This is essentially the same query you just
wrote down.

SELECT WorkLogID, GaugeID, NextCertDue,

(SELECT Max(NextCertDue)
FROM tblWorkLog wl
WHERE GaugeID =tblWorkLog_Main.GaugeID
AND
WorkLogID <> tblWorkLog_Main.WorklogID
Group By NextCertDue) AS DueDate
FROM tblWorkLog tblWorkLog_Main
ORDER BY GaugeID, NextCertDue DESC;

 

[D Witherspoon]

I think I got it to work now....

I modified the subquery a little bit and got rid of the group by. Not sure
why it makes a difference.