I have created a report in Access 2007.
I am receiving the following error message "The specified field'[Order ID].[Company Name]' could refer to more than one table listed in the FROM clause of your SQL statement.
I have read several reponses to similar error messages and have made sure to qualify the field, however I still get the same message.
The following is my existing statement
SELECT [OrderItems ID].[Order Number] AS [OrderItems ID_Order Number], [Order ID].OrderDate, [Order ID].[Company Name], [Coverall Inventory].[Employee Name], [Order ID].[Coverall ID #], [OrderItems ID].ServiceItem, [OrderItems ID].RepairQty, ServiceItem.RepairRate, [RepairQty]*[RepairRate] AS RepairTotals, [OrderItems ID].detailIID, [Order ID].[Order Number], [Customer Details].Washrate, qryMonthly_Rental_Totals_by_Company.[Company Name], qryMonthly_Rental_Totals_by_Company.MonthlyRental, qryMonthly_Wash_Totals_by_Company.MonthlyWash
FROM ServiceItem INNER JOIN (((([Customer Details] INNER JOIN ([Coverall Inventory] INNER JOIN [Order ID] ON [Coverall Inventory].[Coverall ID #] = [Order ID].[Coverall ID #]) ON ([Customer Details].[Company Name] = [Coverall Inventory].[Company Name]) AND ([Customer Details].[Company Name] = [Order ID].[Company Name])) INNER JOIN qryMonthly_Rental_Totals_by_Company ON [Customer Details].[Company Name] = qryMonthly_Rental_Totals_by_Company.[Company Name]) INNER JOIN [OrderItems ID] ON [Order ID].[Order Number] = [OrderItems ID].[Order Number]) INNER JOIN qryMonthly_Wash_Totals_by_Company ON [Customer Details].[Company Name] = qryMonthly_Wash_Totals_by_Company.[Company Name]) ON ServiceItem.ServiceItem = [OrderItems ID].ServiceItem
GROUP BY [OrderItems ID].[Order Number], [Order ID].OrderDate, [Order ID].[Company Name], [Coverall Inventory].[Employee Name], [Order ID].[Coverall ID #], [OrderItems ID].ServiceItem, [OrderItems ID].RepairQty, ServiceItem.RepairRate, [RepairQty]*[RepairRate], [OrderItems ID].detailIID, [Order ID].[Order Number], [Customer Details].Washrate, qryMonthly_Rental_Totals_by_Company.[Company Name], qryMonthly_Rental_Totals_by_Company.MonthlyRental, qryMonthly_Wash_Totals_by_Company.MonthlyWash
HAVING ((([Order ID].OrderDate) Between [StartDate] And [EndDate]));
All my info comes from 5 tables and 2 queries.
It works fine with the 5 tables and the qryMonthly_Rental_Totals_by_Company. As soon as I add the qryMonthly_Wash_Totals_by_Company I get the error message.
Any help would be greatly appreciated.
Thank you
Li
I am receiving the following error message "The specified field'[Order ID].[Company Name]' could refer to more than one table listed in the FROM clause of your SQL statement.
I have read several reponses to similar error messages and have made sure to qualify the field, however I still get the same message.
The following is my existing statement
SELECT [OrderItems ID].[Order Number] AS [OrderItems ID_Order Number], [Order ID].OrderDate, [Order ID].[Company Name], [Coverall Inventory].[Employee Name], [Order ID].[Coverall ID #], [OrderItems ID].ServiceItem, [OrderItems ID].RepairQty, ServiceItem.RepairRate, [RepairQty]*[RepairRate] AS RepairTotals, [OrderItems ID].detailIID, [Order ID].[Order Number], [Customer Details].Washrate, qryMonthly_Rental_Totals_by_Company.[Company Name], qryMonthly_Rental_Totals_by_Company.MonthlyRental, qryMonthly_Wash_Totals_by_Company.MonthlyWash
FROM ServiceItem INNER JOIN (((([Customer Details] INNER JOIN ([Coverall Inventory] INNER JOIN [Order ID] ON [Coverall Inventory].[Coverall ID #] = [Order ID].[Coverall ID #]) ON ([Customer Details].[Company Name] = [Coverall Inventory].[Company Name]) AND ([Customer Details].[Company Name] = [Order ID].[Company Name])) INNER JOIN qryMonthly_Rental_Totals_by_Company ON [Customer Details].[Company Name] = qryMonthly_Rental_Totals_by_Company.[Company Name]) INNER JOIN [OrderItems ID] ON [Order ID].[Order Number] = [OrderItems ID].[Order Number]) INNER JOIN qryMonthly_Wash_Totals_by_Company ON [Customer Details].[Company Name] = qryMonthly_Wash_Totals_by_Company.[Company Name]) ON ServiceItem.ServiceItem = [OrderItems ID].ServiceItem
GROUP BY [OrderItems ID].[Order Number], [Order ID].OrderDate, [Order ID].[Company Name], [Coverall Inventory].[Employee Name], [Order ID].[Coverall ID #], [OrderItems ID].ServiceItem, [OrderItems ID].RepairQty, ServiceItem.RepairRate, [RepairQty]*[RepairRate], [OrderItems ID].detailIID, [Order ID].[Order Number], [Customer Details].Washrate, qryMonthly_Rental_Totals_by_Company.[Company Name], qryMonthly_Rental_Totals_by_Company.MonthlyRental, qryMonthly_Wash_Totals_by_Company.MonthlyWash
HAVING ((([Order ID].OrderDate) Between [StartDate] And [EndDate]));
All my info comes from 5 tables and 2 queries.
It works fine with the 5 tables and the qryMonthly_Rental_Totals_by_Company. As soon as I add the qryMonthly_Wash_Totals_by_Company I get the error message.
Any help would be greatly appreciated.
Thank you
Li