Results 1 to 3 of 3
  1. #1
    daz2932 is offline Novice
    Windows Vista Access 2003
    Join Date
    Aug 2011
    Posts
    4

    nesting error

    I need to bow down to someones infinite wisdom and help me to fix an error when trying to nest this query (i think access refers it as a sub query) below, instead of refering to just the query name

    NOT NESTED BUT WORKING
    SELECT p.Service_No, p.Lastname, r.Rank, t.total/(Date()-p.TOS) AS ratio
    FROM tbl_personnel AS p, tbl_rank AS r, Q_Total_duties_done_list AS t
    WHERE (((r.Rank_ID)=p.rank) And ((t.Service_No)=p.service_no))
    ORDER BY t.total/(Date()-p.TOS);


    QURRY TO NEST -Q_Total_duties_done_list
    SELECT p.Service_No, p.Rank, p.Lastname, Count(a.Dates) AS Total
    FROM Tbl_Personnel AS p LEFT JOIN Tbl_Gd_Allocation AS a ON p.Service_No=a.Service_No
    GROUP BY p.Service_No, p.Rank, p.Lastname;

    I have tried putting in this format

    SELECT p.Service_No, p.Lastname, r.Rank, t.total/(Date()-p.TOS) AS ratio
    FROM tbl_personnel AS p, tbl_rank AS r,
    [SELECT p.Service_No, p.Rank, p.Lastname, Count(a.Dates) AS Total
    FROM Tbl_Personnel AS p LEFT JOIN Tbl_Gd_Allocation AS a ON p.Service_No=a.Service_No


    GROUP BY p.Service_No, p.Rank, p.Lastname;] AS t
    WHERE (((r.Rank_ID)=p.rank) And ((t.Service_No)=p.service_no))
    ORDER BY t.total/(Date()-p.TOS);

    I just get an invalid bracketing of name error really confused why doesnt like the query written in full but works if just refering to the query name. if database required please ask, any information greatly appreciated thanks

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Is there a specific reason you want it all in one query instead of two? There is no harm in the efficiency of the query to keep it as two separate queries. Personally I don't do things the way you are trying so maybe someone more versed can help I'm just curious as to the why.

  3. #3
    daz2932 is offline Novice
    Windows Vista Access 2003
    Join Date
    Aug 2011
    Posts
    4
    Solved it relace [] with () and take out the ; in the sub query. was mainly doing it as need to convert to another sql format which needs to be nested.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error 2501 displays with Error handling
    By agent- in forum Programming
    Replies: 13
    Last Post: 08-05-2011, 02:20 PM
  2. Error 3464 Reserved Error
    By e.badin in forum Reports
    Replies: 6
    Last Post: 07-18-2011, 02:47 PM
  3. Error 13 Type Mismatch error
    By GlennBurg in forum Programming
    Replies: 1
    Last Post: 06-21-2011, 03:05 AM
  4. Replies: 8
    Last Post: 05-16-2011, 06:01 PM
  5. Nesting IIf query
    By peter_lawton in forum Queries
    Replies: 3
    Last Post: 02-19-2010, 10:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums