Key field could refer to multiple tables in SQL statement

[kskelly]

This is the error:

Works in one query but not this one. Why?

This is the query in question:

Notice that the table "SCALEHOU" is specified.

[JoeM]

Can you switch your query to SQL View and post the code here, so we can see your entire query?

[June7]

Date and Day are reserved words (they are both intrinsic functions). Should not use reserved words as names.

Post SQL statements of both queries.

[JamesDeckert]

sounds like you are specifying [Date] somewhere (criteria? field not shown?) and not specifying the table (e.g. [SCALEHOU].[Date]). [Date] is in both tables, so access needs to know which. Or it could be confused because Date is a reserved word

[kskelly]

Here is the SQL statement:

OU.[X2 start 239], SCALEHOU.[Shift change 139], SCALEHOU.[Shift change 239], SCALEHOU.[X3 end 139], SCALEHOU.[X3 end 239], [Shift change 139]-[X2 start 139] AS [X2 139 total], [Shift change 239]-[X2 start 239] AS [X2 239 total], [X3 end 139]-[Shift change 139] AS [X3 139 total], [X3 end 239]-[Shift change 239] AS [X3 239 total], ([Shift change 139]-[X2 start 139])+([Shift change 239]-[X2 start 239]) AS [X2 total], ([X3 end 139]-[Shift change 139])+([X3 end 239]-[Shift change 239]) AS [X3 total], [X2 total]+[X3 total] AS [Belt scale total], [X2 139 total]+[X3 139 total] AS [139 total], [X2 239 total]+[X3 239 total] AS [239 total], IIf((([239 total]+0.0001)/([139 total]+0.0001))-1>200,0,(([239 total]+0.0001)/([139 total]+0.0001))-1) AS [% diff], [X2 start 139]-[Day 139] AS [Day 139 total], [X2 start 239]-[Day 239] AS [Day 239 total], SCALEHOU.[Day 139], SCALEHOU.[Day 239], IIf([SCALEHOU]![INVBDAY]+[SCALEHOU]![MSWRECVD]-[SCALEHOU]![INVBNDAY]+[SCALEHOU]![FERRUSRESMSW]-[SCALEHOU]![msw out]<=0,0,[SCALEHOU]![INVBDAY]+[SCALEHOU]![MSWRECVD]-[SCALEHOU]![INVBNDAY]+[SCALEHOU]![FERRUSRESMSW]-[SCALEHOU]![msw out]) AS MSWPROCESSED, ([Belt scale total]-[MSWPROCESSED]) AS Diff, [139 total]/[LINE1 RUN HOURS] AS [LINE1 TPH], (([X2LINE2RUN]+[X3LINE2RUN])/60)+0.01 AS [LINE2 RUN HOURS], (([X2LINE1RUN]+[X3LINE1RUN])/60)+0.01 AS [LINE1 RUN HOURS], [239 total]/[LINE2 RUN HOURS] AS [LINE2 TPH], SCALEHOU.X2LINE1RUN, SCALEHOU.X3LINE1RUN, SCALEHOU.X2LINE2RUN, SCALEHOU.X3LINE2RUN
FROM SCALEHOU INNER JOIN QRYCALCHOURS ON SCALEHOU.DATE = QRYCALCHOURS.DATE

I suspect the problem in in the red?

[June7]

That is not a complete SQL statement. I don't see SELECT of FROM or WHERE clauses. Joining on date/time type fields should not be an issue.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. No reserved words as names. Title case is easier to read. Better would be X2Line1Run or X2_Line1_Run.