sqlms-accessms-query

The specified field [Report].[Field] could refer to more than one table listed in the FROM clause of your SQL statement


I am creating a report based on a query in Microsoft Access, and I keep receiving an error message when switching from Design View to Report view.

The specified field '[Customer Report].[Need Date]' could refer to more than one table listed in the FROM clause of your SQL statement.

I have searched through questions that have already been asked, and it seems as though the answer is simple: I just need to specify what table the [Need Date] is coming from in the SQL coding. However, I've already done that, and the error message continues to happen. Here is the coding for the query that my report is based on:

SELECT 
    [UID Only].[Our PN], 
    [UID Only].[Customer PN], 
    [UID Only].Description, 
    [UID Only].[Order #], 
    [UID Only].[Customer PO], 
    [UID Only].[Need Date], 
    [Customer Report].[Need Date], 
    [UID Only].[Unique ID]
FROM ([UID Only] 
LEFT JOIN [UID, Date] ON [UID Only].[Unique ID] = [UID, Date].[Unique ID]) 
LEFT JOIN [Customer Report] ON [UID Only].[Unique ID] = [Customer Report].[Unique ID]
WHERE [UID, Date].[Unique ID] Is Null;

Solution

  • The error comes from the report, not from the query itself. The records resulting from the query shown above contains two fields named [Need Date] and the report cannot differentiate them. Give them different aliases

    SELECT
        [UID Only].[Our PN], 
        [UID Only].[Customer PN], 
        [UID Only].Description, 
        [UID Only].[Order #], 
        [UID Only].[Customer PO], 
        [UID Only].[Need Date] AS UO_NeedDate,
        [Customer Report].[Need Date] AS CR_NeedDate,
        [UID Only].[Unique ID]
    FROM ([UID Only] 
    LEFT JOIN [UID, Date] ON [UID Only].[Unique ID] = [UID, Date].[Unique ID]) 
    LEFT JOIN [Customer Report] ON [UID Only].[Unique ID] = [Customer Report].[Unique ID]
    WHERE [UID, Date].[Unique ID] Is Null;
    

    Now, in the report you can refer to them as UO_NeedDate and CR_NeedDate.