Specified field could refer to more than one table listed in SQL statement

[Alison Dawson]

I keep getting "The specified field "customer" could refer to more than one table listed in the FROM clause of your SQL statement. I understand what it means but I dont know how to change it. I have 11 tables in total.

Below is my statement:

 
 
SELECT informix_opheadm.date_entered, 
informix_opheadm.date_required, 
DateValue([informix_opheadm]![date_entered]) AS Expr1DEnt, 
DateValue([informix_opheadm]![date_required]) AS Expr2DReq, 
DateValue([informix_opheadm]![date_despatched]) AS Expr3DDes, 
informix_opheadm.order_no, 
informix_slcustm.customer, 
informix_slcustm.name, 
informix_opdetm.product, 
informix_opdetm.product_group_a, 
informix_opdetm.order_qty, 
informix_opdetm.value, 
informix_opdetm.warehouse, 
informix_stockm.standard_labour, 
informix_stockm.standard_overhead, 
informix_stockm.standard_material, 
informix_opheadm.transaction_anals1, 
[informix_opheadm]![transaction_anals1]*[Expr1orderqty]/1000 AS Expr1LMEValue, 
[Expr1orderqty]*[standard_labour] AS Expr1StandardLabour, 
[Expr1orderqty]*[standard_overhead] AS Expr1StandardOverhead, 
[standard_material]*[Expr1orderqty] AS Expr1StandardMaterial, 
([value]-[Expr1LMEValue]-[Expr1StandardLabour]-[Expr1StandardOverhead]-[Expr1StandardMaterial]) AS [Expr1Margin£], 
[Expr1Margin£]/[Expr1OrderValue] AS [Expr1Margin%], 
[net_price]*[order_qty] AS Expr1OrderValue, 
informix_opdetm.net_price, 
informix_opdetm.despatched_qty, 
[Expr1StandardLabour]+[Expr1StandardOverhead]+[Expr1StandardMaterial] AS Expr1STDCOST, 
[value]-[Expr1LMEValue] AS Expr1HVLVALUE, 
[Expr1HVLVALUE]/[Expr1OrderValue] AS [Expr1HVL%], 
[order_qty]-[despatched_qty] AS Expr1DES, 
IIf([despatched_qty]>0,[despatched_qty],[order_qty]) AS Expr1orderqty, 
[informix_opheadm]![transaction_anals1]/1000*[Expr1orderqty] AS [Expr1LME DESVALUE], 
([standard_overhead]+[standard_labour]+[standard_material])*[Expr1orderqty] AS Expr1stdvaluedes, 
[Expr1DESmARGIN]/[value] AS [Expr1MARGINDES%], 
[value]-[Expr1LME DESVALUE]-[Expr1stdvaluedes] AS Expr1DESmARGIN, 
informix_opheadm.status, 
informix_slcustm.territory, 
informix_slcustm.class, 
informix_opheadm.status, 
Areas.Area, 
informix_opheadm.order_no, 
informix_opheadm.customer, 
informix_opdetm.order_no
FROM Areas 
INNER JOIN (infoBetween "A001" And "XZ100"rmix_stockm 
INNER JOIN ((informix_slcustm 
INNER JOIN informix_opheadm 
ON informix_slcustm.customer = informix_opheadm.customer) 
INNER JOIN informix_opdetm 
ON informix_opheadm.order_no = informix_opdetm.order_no) 
ON (informix_stockm.product = informix_opdetm.product) 
AND (informix_stockm.warehouse = informix_opdetm.warehouse)) 
ON Areas.Territory = informix_slcustm.territory
WHERE (((DateValue([informix_opheadm]![date_entered])) Between [Forms]![frmFindDates]![cmbStartDate] And [Forms]![frmFindDates]![cmbEndDate]) 
AND ((DateValue([informix_opheadm]![date_required])) Between [Forms]![frmFindDates]![txtDateRequiredStart] And [Forms]![frmFindDates]![txtDateRequiredEnd]) 
AND ((informix_opheadm.order_no) Between "04*" And "06*") AND ((informix_slcustm.customer) Between "A001" And "XZ100") AND ((informix_opdetm.warehouse) Between "10" And "79") 
AND ((informix_opheadm.status) Between [Forms]![frmFindDates]![txtStartStatus] And [Forms]![frmFindDates]![txtEndStatus]));
 
 

[Stewart Ross]

There is no obvious problem with your SQL statement, as all occurrences of the field name 'customer' are qualified with the relevant table names. It is a relatively complex statement with many clauses in the WHERE part. My feeling is that the error message is really reflecting the query engine finding the statement too complex to interpret.

Given that the SQL you show has no obvious syntax problem I'd suggest breaking this complex query down into at least two separate, simpler, queries which can be joined together to create the final result.

Breaking such a query into simpler components will also make it much easier to confirm by testing that the WHERE clauses are selecting the correct records and that any computed results are accurate.

-Stewart