If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Correlated Subquery doesn't work
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; |
#2
|
|||
|
|||
Correlated Subquery doesn't work
Dear D:
You have misspelled "Gauge" as "Guage" That should do it! Tom Ellison "D Witherspoon" wrote in message ... 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; |
#3
|
|||
|
|||
Correlated Subquery doesn't work
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. "Tom Ellison" wrote in message ... Dear D: You have misspelled "Gauge" as "Guage" That should do it! Tom Ellison "D Witherspoon" wrote in message ... 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; |
#4
|
|||
|
|||
Correlated Subquery doesn't work
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 "D Witherspoon" wrote in message ... 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; |
#5
|
|||
|
|||
Correlated Subquery doesn't work
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 "D Witherspoon" wrote in message ... 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. "Tom Ellison" wrote in message ... Dear D: You have misspelled "Gauge" as "Guage" That should do it! Tom Ellison "D Witherspoon" wrote in message ... 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; |
#6
|
|||
|
|||
Correlated Subquery doesn't work
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" wrote in message ... 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. "Tom Ellison" wrote in message ... Dear D: You have misspelled "Gauge" as "Guage" That should do it! Tom Ellison "D Witherspoon" wrote in message ... 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; |
#7
|
|||
|
|||
Correlated Subquery doesn't work
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; "Tom Ellison" wrote in message ... 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 "D Witherspoon" wrote in message ... 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. "Tom Ellison" wrote in message ... Dear D: You have misspelled "Gauge" as "Guage" That should do it! Tom Ellison "D Witherspoon" wrote in message ... 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; |
#8
|
|||
|
|||
Correlated Subquery doesn't work
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. "Tom Ellison" wrote in message ... 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 "D Witherspoon" wrote in message ... 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. "Tom Ellison" wrote in message ... Dear D: You have misspelled "Gauge" as "Guage" That should do it! Tom Ellison "D Witherspoon" wrote in message ... 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; |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Please Help?? Auto Update Field in table | A. Smart | General Discussion | 8 | February 2nd, 2006 01:19 PM |
Tricky query grouping | JLamb | Running & Setting Up Queries | 4 | November 2nd, 2005 09:38 PM |
how do I create a work order form | cher952 | Using Forms | 1 | March 14th, 2005 05:09 PM |
Work Areas in a database | LMB | New Users | 3 | October 4th, 2004 11:31 PM |
"Work Offline" Menu and "Exit" Menu | MSHUME | General Discussion | 1 | September 27th, 2004 09:17 PM |