Specific field could refer to more than one table listed

[vnicosia]

ACCESS 2010: I have a report in ACCESS 2010 that when I try to run it says: "The specific field"[Contract Data].TERM" could refer to more than one table listed in the FROM clause of your SQL statement." It ran perfectly until I tried to add a new query that totaled the number of units taught and added a summing query to the report query. They all use the same tables. Not sure what to do.

[twinnyfo]

Please list your SQL statement and we can try to debug for you. Otherwise, we have nothing to go on.

[vnicosia]

SELECT   [Contract Data].[Cont ID]
       , [Contract Data].[Full Name]
       , [Faculty Data].[LAST NAME]
       , [Contract Data].CD
       , [Faculty Data].[FIRST NAME]
       , [Contracted Services].[Course Abv]
       , [Contracted Services].[Course #]
       , [Contracted Services].Section
       , [Contracted Services].Rate
       , [Contracted Services].Units
       , [Contracted Services].TERM
       , [Contract Data].SB
       , [Contract Data].SE
       , [Contract Data].PB
       , [Contract Data].PE
       , Round(([days of pay]/15)+1,0) AS NOPAYMENT
       , DateDiff("y",[pb],[pe]) AS [Days of Pay]
       , [Faculty Data].[STREET ADDRESS]
       , [Faculty Data].CITY
       , [Faculty Data].STATE
       , [Faculty Data].ZIP
       , [Faculty Data].Appointment
       , [Faculty Data].Rank
       , [Contract Data].Year
       , [Contract Data].TERM
       , [Contracted Services].MEASURE
       , [Contract Data].Type
       , [Contract Data].PROGRAM1
       , [Contract Data].PROGRAM2
       , [Faculty Data].PROGRAM
       , [Faculty Data].Step
       , [Faculty Data].[Units Needed]
       , [Faculty Data].[EDAD Step]
       , [Faculty Data].[EDAD Units]
       , [step]+1 AS [Next Step]
       , [Faculty Data].FT
       , [Contract Data].[ACCT NUMBER]
       , IIf(IsNull([edad units]),"n/a"," Lecturer " & ([edad step]+1) & "  (ELP)") AS edadnxtstep
       , IIf(IsNull([edad units]),"n/a"," Lecturer " & ([edad step]+1) & "  (Ed.D)") AS eddnxtstep
       , (" Lecturer " & [EDAD Step] & "  (Ed.D)") AS eddrankstep
       , (" Lecturer " & [EDAD Step] & "  (ELP)") AS EDADrankstep
       , [rate]*[units] AS totlsal
       , [Faculty Data].Email
       , [STEP AUDIT PT FACULTY].STEP1
       , [STEP AUDIT PT FACULTY].Nextstep
       , [STEP AUDIT PT FACULTY].[N/A]
FROM     [STEP AUDIT PT FACULTY]
         INNER JOIN
         ([Faculty Data]
         INNER JOIN
         ([Contract Data]
         INNER JOIN
         [Contracted Services]
  ON     [Contract Data].[Cont ID] = [Contracted Services].ID)
  ON     [Faculty Data].[FULL NAME] = [Contract Data].[Full Name])
  ON     [STEP AUDIT PT FACULTY].[Full Name] = [Contract Data].[Full Name]
WHERE    ((([Faculty Data].[LAST NAME]) Like [Please Enter All or Part of Last Name] & "*")
  AND    (([Contract Data].CD)=[Please Enter Contract Date:])
  AND    (([Contract Data].Type)="Lecturer"))
ORDER BY [Contract Data].[Full Name]
       , [Contract Data].CD;

[vnicosia]

There is also a sub report query:

SELECT   [Contracted Services].TERM
       , Switch([term]="Summer","a",[term]="Fall","b",[term]="Jan","c",[term]="Spring","d",[term]="Annual","e") AS CustomSort
       , [Contracted Services].MEASURE
       , [Contracted Services].Units
       , [Contracted Services].Rate
       , [Contracted Services].ID
       , [Contracted Services].[Description/Comments]
       , [Contracted Services].[Course Abv]
       , [Contracted Services].[Course #]
       , [Contracted Services].Section
       , IIf(IsNull([course abv]),[Course abv],[course abv] & " " & [course #] & " - " & [section]) AS cws
       , [Contracted Services].Section
FROM     [Contracted Services]
ORDER BY Switch([term]="Summer","a",[term]="Fall","b",[term]="Jan","c",[term]="Spring","d",[term]="Annual","e")
       , [Contracted Services].ID;

[twinnyfo]

Your problem is clearly with your first SQL Block. When the Query is run apart from the Report does it produce the same error?

One potential problem could be in your WHERE clause:

(([Contract Data].CD)=[Please Enter Contract Date:])

If this is a Date, you should probably include the "#" marks to indicate such:

(([Contract Data].CD)=#[Please Enter Contract Date:]#)

But that should not be causing the particular error you are getting.

To trouble shoot, I would keep this Query saved as is, but then gradually re-build it from scratch, testing to make sure that each step along the way, it gives the results you want. Then, identify exactly where along the line it generates the error.

As a side note (and it is too late to do this right now, it seems), but as a general principle, your Table Names and Field names should never have any spaces in them. This always causes problems with query building when you do, and makes trouble-shooting a bear, trying to sort out all the brackets.

Sorry I can't specifically help more, but it looks like you are only referring to [Contract Data].TERM once in your query. It is also impossible to tell if all these fields are from Tables only, or queries also. It does not look like this query is dependent upon any text boxes on any forms.

If you provide more information, perhaps we can work forward to a better solution.

[vnicosia]

No. It runs fine as a query. It is only when I assign the query to the report I get the message. If I remove the subReport it will run. I have even deleted all of the "TERM" Fields and even tried changing the field names in the tables to separate them from the 2 queries. But I still got the message.

[twinnyfo]

1: What is the parent/child field that the report/sub-report are using?

2: Does the sub-report have any grouping or sorting done?

3: Any calculated controls on the report or sub-report or are all controls bound to values in your queries?

I am grasping at straws at this point. The best indicator for trouble shooting is that the query runs as a standalone query. So it appears the problem is truly with the report. I'm just trying to think of possible causes.

[Rabbit]

You have the field term defined twice in your first query. Which is fine when it doesn't have to pick one when it's only displaying the data. But when you ask for that field somewhere else, there's no way for it to know which one you want from the first query.

[NeoPa]

Are you sure the error message is exactly as specified? The field [TERM] is specified twice but only once each as [Contracted Services].TERM and [Contract Data].TERM. I wouldn't expect this to trip up the SQL engine unless you have a reference somewhere just to [TERM] - in which case the error message would have been different (Hence the request to check that carefully).

As Twinnyfo says, it seems like it may be in your report rather than the SQL per se - especially as that runs fine on its own.

@Twinnyfo.
FYI: A reference wouldn't need the # characters. Only literal values need those in SQL. See Literal DateTimes and Their Delimiters (#) for more on this.

PS. Having just reformatted post #4 so that it's recognisable as real SQL I see there is a reference in there to [term]. However, the domain from which this data is read is defined separately so this would not explain the problem (even though earlier comments might lead one to believe otherwise).

[aleciagibbons14]

will this thread i think already answer, but thanks for those shared idea we also learn from it.

[vnicosia]

Got it to work. It was a field in the sort of the subreport. Thank you all!