Results 1 to 11 of 11
  1. #1
    Chris1112 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    31

    The Specified Field Could Refere to More Than One Table


    I'm receiving this error when I attempt to run a query based on two tables. They do have the same field names but the SQL query shows them both as fully qualified. I've been researching this issue but not having much luck. Any help is greatly appreciated.

    SQL:
    Code:
    SELECT [GHI#FILE_JOBMST1].SUBDIV, [GHI#FILE_JOBMST1].[JOB#], [GHI#FILE_JOBMST1].JBTYPE, [GHI#FILE_JOBMST1].MODEL, [GHI#FILE_JOBMST1].LOT, [GHI#FILE_JOBMST1].BLOCK, [GHI#FILE_JOBMST1].SECTN, [GHI#FILE_JOBMST1].ADDRS, [GHI#FILE_JOBMST1].STREET, [GHI#FILE_JOBMST1].START, [GHI#FILE_JOBMST1].CMPLT, [GHI#FILE_JOBMST1].POUR, [GHI#FILE_JOBMST1].CLSDTE, [GHI#FILE_JOBMST1].CLSPRC, [GHI#FILE_JOBMST1].SLSPRC, [GHI#FILE_JOBMST1].PURCH, [GHI#FILE_JOBMST1].SLSDTE, [GHI#FILE_JOBMST1].ECMPLT, [GHI#FILE_JOBMST1].MTGCO, [GHI#FILE_JOBMST1].[LOAN#], [GHI#FILE_JOBMST1].[LOAN$], [GHI#FILE_JOBMST1].DRAW, [GHI#FILE_JOBMST1].LOANDT, [GHI#FILE_JOBMST1].SUPER, [GHI#FILE_JOBMST1].SWING, [GHI#FILE_JOBMST1].PLAN, [GHI#FILE_JOBMST1].ELEV, [GHI#FILE_JOBMST1].WRKSHT, [GHI#FILE_JOBMST1].ALCINT, [GHI#FILE_JOBMST1].FNDDTE, [GHI#FILE_JOBMST1].[SALE#], [GHI#FILE_JOBMST1].VCHPST, [GHI#FILE_JOBMST1].LSTPRC, [GHI#FILE_JOBMST1].REMOVE, [GHI#FILE_JOBMST1].PLNMOD, [GHI#FILE_JOBMST1].LOANGL, [GHI#FILE_JOBMST1].WAREXP, [GHI#FILE_JOBMST1].COMMNT, [GHI#FILE_JOBMST1].[INTGL#], [GHI#FILE_JOBMST1].FILING, [GHI#FILE_JOBMST1].ALLOW, [GHI#FILE_JOBMST1].ENGNO1, [GHI#FILE_JOBMST1].ENGNO2, [GHI#FILE_JOBMST1].PERMIT, [GHI#FILE_JOBMST1].[SOIL#], [GHI#FILE_JOBMST1].FNDCDE, [GHI#FILE_JOBMST1].PRODOH, [GHI#FILE_JOBMST1].FRMSVY, [GHI#FILE_JOBMST1].ALWWIP, [GHI#FILE_JOBMST1].ALWWAR, [GHI#FILE_JOBMST1].PRJCLS, [GHI#FILE_JOBMST1].TAKDWN, [GHI#FILE_JOBMST1].BSTCM1, [GHI#FILE_JOBMST1].BSTCM2, [GHI#FILE_JOBMST1].BSTCM3, [GHI#FILE_JOBMST1].BSTCM4, [GHI#FILE_JOBMST1].[TRANS#], [GHI#FILE_JOBMST1].PLSRCD, [GHI#FILE_JOBMST1].SCHED, IsNumeric([JOB#]) AS Expr1, [GHI#FILE_PSDETL].STAGEFROM [GHI#FILE_JOBMST1] INNER JOIN [GHI#FILE_PSDETL] ON ([GHI#FILE_JOBMST1].[JOB#] = [GHI#FILE_PSDETL].[JOB#]) AND ([GHI#FILE_JOBMST1].SUBDIV = [GHI#FILE_PSDETL].SUBDIV)
    WHERE ((([GHI#FILE_JOBMST1].START)>"0") AND (([GHI#FILE_JOBMST1].CLSDTE)="") AND (([GHI#FILE_JOBMST1].PURCH)<>"") AND ((IsNumeric([JOB#]))=True) AND (([GHI#FILE_PSDETL].STAGE)>="2"));

  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,642
    Is JOB# in both tables? By the way, spaces and symbols aren't a good idea in object names.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,610
    Could it be that the field JOB# needs to be qualified
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,610
    Sorry Paul, didn't see your post.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    No biggie Bob. Looks like we both spotted the same thing.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Chris1112 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    31
    Yes, the field JOB# is in both tables, but the references to it in SQL are fully qualified.

    I know symbols are bad design. Sadly this is not my beast. It's actually an AS400 antique database that I need to report from.

  7. #7
    Chris1112 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    31
    Unless my eyes are deceiving me, JOB# is qualified in the SQL code. Am I missing something? I've double-checked.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    No, it isn't:


    ...AND ((IsNumeric([JOB#]))=True) AND...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,610
    What about:
    ...IsNumeric([JOB#]) AS Expr1...
    shouldn't that also be qualified
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  10. #10
    Chris1112 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    31
    Thank you very much Bob and Paul both. That did the trick. I'll get my eyes checked ASAP.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    Quote Originally Posted by Bob Fitz View Post
    What about:
    ...IsNumeric([JOB#]) AS Expr1...
    shouldn't that also be qualified
    Absolutely. I assumed Chris would fix in design view, which would fix both places. Assumptions have a way of backfiring, so I should have been more clear.
    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. Replies: 12
    Last Post: 03-17-2012, 04:46 AM
  2. Replies: 1
    Last Post: 08-31-2011, 04:03 PM
  3. Replies: 3
    Last Post: 08-26-2011, 12:11 PM
  4. Replies: 5
    Last Post: 01-20-2011, 11:36 PM
  5. Selecting a corresponding table field based on text field.
    By michaeljohnh in forum Programming
    Replies: 5
    Last Post: 10-08-2010, 10:33 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