Query gives me a warning when I ru it...

[Phil Smith]

This query runs OK, but I get a warning.:

The specified field '[Item_price_link].[price] could refer to more than
one table listed in the FROM caluse of your SQL statement.

Any idea why? It was an update query which fails with an attempt to to
assign a field to null. I can't find any null valuewhen I look at it as
a select query.


SELECT item_price_link.price, PriceListSRP.price,
item_price_link.company_id, price_type.name
FROM SellableItemsList INNER JOIN (price_type INNER JOIN
(item_price_link INNER JOIN (item_types INNER JOIN (PriceListSRP RIGHT
JOIN (PriceListWholesale RIGHT JOIN (PriceMaintenanceMap INNER JOIN item
ON PriceMaintenanceMap.Type_ID = item.item_type) ON
PriceListWholesale.item_id = item.item_id) ON PriceListSRP.item_id =
item.item_id) ON item_types.type_id = item.item_type) ON
item_price_link.item_id = item.item_id) ON price_type.price_type_id =
item_price_link.price_type_id) ON SellableItemsList.item_id = item.item_id
WHERE (((PriceListSRP.price)=0) AND ((item_price_link.company_id)=1) AND
((price_type.name)="SRP"));

Phil

 

[Allen Browne]

The error indicates that JET is having difficulty understanding which table
the price field comes from. From your SQL statement, there's no obvious
reason why this would be so directly, so perhaps there is something else
going on.

Suggestions:
1. Make sure that Name AutoCorrect is turned off. Explanation of why:
http://allenbrowne.com/bug-03.html

2. Then compact/repair to really get rid of that stuff.

3. Clear these properties of the query: Filter, OrderBy. If there's a
reference to 'price' in these properties, it could be ambiguous.

4. Alias one of the price fields, so you don't have 2 with the same name,
e.g.:
SELECT item_price_link.price,
PriceListSRP.price AS ListPrice,
item_price_link.company_id,
price_type.[name]
FROM ...

5. Add square brackets around the 'name' field (as shown above.) Lots of
things in Access have a Name property, so it can get confused.

6. Are some of the source tables for this query actually queries? If so, do
those queries actually alias the same table with different names? There are
times when JET manages to get confused between the alias name and the source
table name in stacked queries.

7. Outer joins: I didn't follow this in detail, but if you have an outer
join that goes to another outer join, JET may ignore the bracketing of the
FROM clause, and results can be problematic. To avoid this, stack this query
on another one, so the outer joins are in 2 separate queries. (This is
probably not related to your error message: just mentioned for
completeness.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This query runs OK, but I get a warning.:

The specified field '[Item_price_link].[price] could refer to more than
one table listed in the FROM caluse of your SQL statement.

Any idea why? It was an update query which fails with an attempt to to
assign a field to null. I can't find any null valuewhen I look at it as a
select query.

SELECT item_price_link.price,
PriceListSRP.price,
item_price_link.company_id,
price_type.name

FROM SellableItemsList
INNER JOIN (price_type
INNER JOIN (item_price_link
INNER JOIN (item_types
INNER JOIN (PriceListSRP
RIGHT JOIN (PriceListWholesale
RIGHT JOIN (PriceMaintenanceMap
INNER JOIN item

ON PriceMaintenanceMap.Type_ID = item.item_type)
ON PriceListWholesale.item_id = item.item_id)
ON PriceListSRP.item_id = item.item_id)
ON item_types.type_id = item.item_type)
ON item_price_link.item_id = item.item_id)
ON price_type.price_type_id = item_price_link.price_type_id)
ON SellableItemsList.item_id = item.item_id

WHERE (((PriceListSRP.price)=0)
AND ((item_price_link.company_id)=1)
AND ((price_type.name)="SRP"));