The Microsoft Access Database Engine Does not Recognize ‘[tbl_AssignmentDetails].[Position Name]’ as a valid field name or expression

I have created the below crosstab query off a find duplicates query. When I run it, I get the error message:

“The Microsoft Access database engine does not recognize ‘[tbl_AssignmentDetails].[Position Name]’ as a valid field name or expression.”

Any clues?

TRANSFORM Sum(qry_DuplicatedAssignmentDetails.[IOS Contract Lenght in Months]) AS [SumOfIOS Contract Lenght in Months]
SELECT qry_DuplicatedAssignmentDetails.[Full Name], qry_DuplicatedAssignmentDetails.[Staff Number], qry_DuplicatedAssignmentDetails.[Position Name]
FROM qry_DuplicatedAssignmentDetails
GROUP BY qry_DuplicatedAssignmentDetails.[Full Name], qry_DuplicatedAssignmentDetails.[Staff Number], qry_DuplicatedAssignmentDetails.[Position Name]
PIVOT qry_DuplicatedAssignmentDetails.[Appointment Type];

SELECT tbl_AssignmentDetails.[Full Name], tbl_AssignmentDetails.[Position Name], tbl_AssignmentDetails.ID, tbl_AssignmentDetails.[Staff Number], tbl_AssignmentDetails.Nationality, tbl_AssignmentDetails.[Recruitment List], tbl_AssignmentDetails.Gender, tbl_AssignmentDetails.[Date Of Birth], tbl_AssignmentDetails.[Email Address], tbl_AssignmentDetails.[Service Time (UN)], tbl_AssignmentDetails.[Service time (Current Organization)], tbl_AssignmentDetails.[Effective Date], tbl_AssignmentDetails.[Major Office], tbl_AssignmentDetails.Country, tbl_AssignmentDetails.Location, tbl_AssignmentDetails.[Official Duty Station], tbl_AssignmentDetails.[Duty Station Start Date], tbl_AssignmentDetails.[Admin Place Of Assignment], tbl_AssignmentDetails.[APA Start Date], tbl_AssignmentDetails.[Cluster Name], tbl_AssignmentDetails.Organization, tbl_AssignmentDetails.Department, tbl_AssignmentDetails.[Job Name], tbl_AssignmentDetails.[Classified Grade Of Position], tbl_AssignmentDetails.[Nature Of Position], tbl_AssignmentDetails.[First Incumbency Start Date], tbl_AssignmentDetails.[End Date Maximum Functional Length], tbl_AssignmentDetails.[Position Geographical Distribution Exclusion Indicator], tbl_AssignmentDetails.[Assignment Geographical Distribution Exclusion Indicator], tbl_AssignmentDetails.[Rotation Indicator], tbl_AssignmentDetails.[Mobility Indicator], tbl_AssignmentDetails.[Entry On Duty Date Who], tbl_AssignmentDetails.[Entry On Duty Date Un System], tbl_AssignmentDetails.[Assignment Category], tbl_AssignmentDetails.[Appointment Type], tbl_AssignmentDetails.[Assignment Reason], tbl_AssignmentDetails.[Start Date On Appointment Type], tbl_AssignmentDetails.[Normal Assignment Length], tbl_AssignmentDetails.[Contract Start Date], tbl_AssignmentDetails.[Contract End Date], tbl_AssignmentDetails.[Contract Length (Years)], tbl_AssignmentDetails.[Contract Length (Months)], tbl_AssignmentDetails.[Contract Length (Days)], DateDiff("m",[tbl_AssignmentDetails]![Contract Start Date],[tbl_AssignmentDetails]![Contract End Date]) AS [IOS Contract Lenght in Months], tbl_AssignmentDetails.[Maximum Contract End Date on Type of Assignment], tbl_AssignmentDetails.[Current Grade], tbl_AssignmentDetails.[Current Step], tbl_AssignmentDetails.[Current Grade Start Date], tbl_AssignmentDetails.[Grade On Appointment], tbl_AssignmentDetails.[Step On Appointment], tbl_AssignmentDetails.[Expatriate Status], tbl_AssignmentDetails.[Interagency Transfer Or Exchange], tbl_AssignmentDetails.[Secondment Indicator], tbl_AssignmentDetails.[Reporting lines (name of supervisor)], tbl_AssignmentDetails.[Retirement Age], tbl_AssignmentDetails.[Retirement Date], tbl_AssignmentDetails.[Probation Start Date], tbl_AssignmentDetails.[Probation End Date], tbl_AssignmentDetails.[Probation Length (Duration)], tbl_AssignmentDetails.[Probation Length (Unit)], tbl_AssignmentDetails.[Probation Status], tbl_AssignmentDetails.[Absence Type], tbl_AssignmentDetails.[Absence Type Reason], tbl_AssignmentDetails.[Separation Type], tbl_AssignmentDetails.[Projected Termination Date], tbl_AssignmentDetails.[Effective Termination Date], tbl_AssignmentDetails.[Acting Up Indicator], tbl_AssignmentDetails.[Reassignment Indicator], tbl_AssignmentDetails.[Wigi Due Date], tbl_AssignmentDetails.[Date Last Wigi Granted], tbl_AssignmentDetails.[Percentage Of Employment], tbl_AssignmentDetails.[Type Of Agreement], tbl_AssignmentDetails.[Funding Arrangement], tbl_AssignmentDetails.[Secondment Agreement Start Date], tbl_AssignmentDetails.[Secondment Agreement End Date], tbl_AssignmentDetails.[Name of the Other Organization], tbl_AssignmentDetails.[Payrolled By], tbl_AssignmentDetails.[Supporting Documentation Complete], tbl_AssignmentDetails.Comments
FROM tbl_AssignmentDetails
WHERE (((tbl_AssignmentDetails.[Full Name]) In (SELECT [Full Name] FROM [tbl_AssignmentDetails] As Tmp GROUP BY [Full Name],[Position Name] HAVING Count(*)>1  And [Position Name] = [tbl_AssignmentDetails].[Position Name])) AND ((tbl_AssignmentDetails.[Position Name]) Is Not Null) AND ((tbl_AssignmentDetails.[Appointment Type])="Temporary Appointment under Staff Rule 420.4") AND ((tbl_AssignmentDetails.[Contract Start Date]) Between DateSerial(2020,1,1) And DateSerial(2020,12,31)) AND ((DateDiff("m",[tbl_AssignmentDetails]![Contract Start Date],[tbl_AssignmentDetails]![Contract End Date])) Is Not Null))
ORDER BY tbl_AssignmentDetails.[Full Name], tbl_AssignmentDetails.[Position Name];

Answer

.. FROM [tbl_AssignmentDetails] As Tmp GROUP BY [Full Name],[Position Name] HAVING .. [Position Name] = [tbl_AssignmentDetails].[Position Name]))…

Which table is [Position Name] from? The table in the main query, or the table in the subquery? And which [tbl_AssignmentDetails].[Position Name] is it equal too?

It’s ambiguous, and when it’s ambiguous the query optimizer becomes confused. Use an alias for one of the tables.

Advertisement

Answer