Results 1 to 6 of 6
  1. #1
    ham.sajjadi is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2019
    Posts
    3

    Error in using subquery in Join query

    I need to use the result of a query as a lookup field for another query. What I tried was using a sub query aliased as something, but finally I got some error saying "enter parameter value" for variz.variz and Y.varizname.


    I've searched in forums, but I can't find any similar problem.
    Code:
    SELECT sahmeharkas.attrib, 
    Code:
        [sahmeharkas]![expenseper] AS Expense,
        variz.variz
    FROM sahmeharkas
    LEFT JOIN 
    (SELECT variz.varizname FROM variz GROUP BY variz.varizname) as Y ON sahmeharkas.attrib =  Y.variz.varizname


  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The alias is used instead of the table name, so it's just:

    Y.varizname

    not


    Y.variz.varizname
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ham.sajjadi is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2019
    Posts
    3
    thank u. i made your suggested changes.but the problem stands yet.i did it :
    Code:
    SELECT sahmeharkas.attrib, -[sahmeharkas]![expenseper] AS مانده, Y.variz FROM sahmeharkas LEFT JOIN (SELECT variz.varizname AS namee FROM variz GROUP BY variz.varizname) AS Y ON sahmeharkas.attrib = Y.varizname

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    variz is not a field returned by the SELECT clause of the subquery, only "variz.varizname AS namee" so you should be able to pull

    Y.namee

    variz is the table name, not a field name (at least that's the way it appears).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    ham.sajjadi is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2019
    Posts
    3

    Thumbs up

    Quote Originally Posted by pbaldy View Post
    variz is not a field returned by the SELECT clause of the subquery, only "variz.varizname AS namee" so you should be able to pull

    Y.namee

    variz is the table name, not a field name (at least that's the way it appears).

    • Wow Great!!! it works as it should! it solved my old problem.thank u . i will learn a lot from it.thank u thank u






  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. sql subquery alias join from clause error
    By rwhite7 in forum Access
    Replies: 1
    Last Post: 12-16-2014, 02:06 PM
  2. SubQuery Error
    By aboondocksaint2 in forum Queries
    Replies: 12
    Last Post: 06-16-2014, 11:54 AM
  3. subquery error
    By slimjen in forum Queries
    Replies: 16
    Last Post: 06-10-2014, 01:30 PM
  4. Replies: 11
    Last Post: 11-08-2013, 06:14 PM
  5. ambiguous join query error
    By Helystra in forum Queries
    Replies: 3
    Last Post: 10-24-2013, 11:10 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