Results 1 to 5 of 5

Error: The specified field could refer to more than one table listed in FROM Clause

  1. #1
    Jkuncas is offline Novice
    Windows XP Access 2002
    Join Date
    Mar 2010
    Posts
    2

    Error: The specified field could refer to more than one table listed in FROM Clause

    Hello,

    I have come across this error in Access 2002 and can't resolve it. I have updated some of other queries in this database in the last few days but not the one I am having the problem with. I did not create this database; it was created before I was at this company I just need to keep it running. Any help with this problem would be greatly appreciated.

    This is the error message that comes up.

    The specified field '[MASTER ATTENDANCE].MANAGER' could refer to more than one table listed in the FROM clause of you SQL statement.

    And here is the SQL code.

    Select [Master Attendance].[Employee Name], [Master Attendance].[Employee Num], [Year Dates].Anniversary, [Master.Attendance].Days,
    [Master Attendance].Manager As Expr1, [Master Attendance].Supervisor As Expr2, [Master Attendance].Department, [Master Attendance].[Deptartment#], [Master Attendance].Dept, [Master Attendance].[Ending Week Date], [Year Dates].[Days Of Week], [Master Attendance].Month, [Master Attendance].Year, Iif([Master Attendance]![Excused From Codes]="Ex",0,Iif([Master Attendance]!Code="Do2",0.35,Iif([Master Attendance]!Code="Do1",0.35,Iif([Master Attendance]!Code="S1",0.35,Iif([Master Attendance]!Code="P1",0.35,0))))) As Dayout, Iif([People Out]!Vacation>0,"Vacation",Iif([People Out]!Holiday>0,"Holiday",Iif([People Out]![Jury Duty]>0,"Jury Duty",Iif([People Out]!Bereavement>0,"Bereavement",Iif([People Out]!Sick>0,"Sick",0))))) As [Type Of Dayout], Iif([Master Attendance]![Excused From Codes]="Ex",0,[Master Attendance]![Points For Extended Lunch]+[Master Attendance]![Points On Late'S]+[Master Attendance]![Points For Earlies]) As Tardiness, [Master Attendance].[Arrived Late], [Master Attendance].[Extended Lunch], [Master Attendance].[Justin'S Lates], [Master Attendance].[Left Early], [Master Attendance].[Day Out], Iif([Dayout]=0.35,0,Iif([Master Attendance]!Description="Holiday",0,..(Continued).

  2. #2
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows XP Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    636
    Can you use proper case.

    Alll upper case is hard to read.

  3. #3
    Jkuncas is offline Novice
    Windows XP Access 2002
    Join Date
    Mar 2010
    Posts
    2

    Proper case

    I have made the changes to the text.

  4. #4
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows XP Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    636
    Thanks for changing the Upper Case. It is much easier on the eyes now.

    I have looked at your SQL and there does not appear to be anything that would cause this error.

    But then you did not include all of the Code, so there could be something wrong with the From Clause.

    Is it possible to export this Query and the Table to a new Database and post it here.

    Please delete all sensitive Data and replace it with two or three records of dummy data.

    I would then be able to see the bigger picture.

  5. #5
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows XP Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    636
    I just noticed a problem.

    Both Year and Month are reserved words. It would be best to change theses to myYear and MyMonth (or Something of your choosing).

    If you can't then put Square brackets around them.

    [Master Attendance].[Month],
    [Master Attendance].[Year],

    I don't think this will solve your current problem but it will help to prevent other problems.

    Correction

    Month is not a reserved word but Year is.

    But I would still advise not using Month, as one Day it may become a reserved Word.
    Last edited by Rainlover; 03-13-2010 at 02:13 PM.

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

Similar Threads

  1. Replies: 0
    Last Post: 10-29-2009, 07:55 PM
  2. Error in field
    By dakwest in forum Access
    Replies: 3
    Last Post: 10-27-2009, 07:13 AM
  3. Refer to main form
    By terryvanduzee in forum Forms
    Replies: 3
    Last Post: 10-23-2009, 06:02 AM
  4. Replies: 1
    Last Post: 03-02-2009, 08:12 PM
  5. FROM Clause error message
    By skylite in forum Queries
    Replies: 2
    Last Post: 10-23-2008, 04:47 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
  •  
Tech Forums: Microsoft Office Forums