query doesn't work at all

[Guest]

Hi. I am in desperate need to get my Append Query working. This is the SQL I
have used:

INSERT INTO tblOrders ( CompanyID, ProductCode, Quantity, OrderSent )
SELECT tblProducts.CompanyID, [qryPercentage Of Stock].ProductCode,
tblProducts.Quantity, Now() AS AppendThisDate
FROM ([tblCompany's] INNER JOIN tblOrders ON [tblCompany's].CompanyID =
tblOrders.CompanyID) INNER JOIN (tblProducts INNER JOIN [qryPercentage Of
Stock] ON tblProducts.ProductCode = [qryPercentage Of Stock].ProductCode) ON
(tblProducts.ProductCode = tblOrders.ProductCode) AND
([tblCompany's].CompanyID = tblProducts.CompanyID)
WHERE ((([qryPercentage Of Stock].PercentageOfStock)<=0.25));

The query looks to see if the Stock Level is below 25% from a qry which
lists the percentages of Items in stock. The only exception is, it doesn't
find these records to show in Datasheet View!
The weird thing is, it used to work before but for some reason now it fails.

Any help, please?

 

[Guest]

Hi Buckaroo,

The insert part of your query is good, so I would suggest cutting and
pasting the select part of your query into a blank SQL query and running it
to see what happens... without having your table structure here it's a treat
trying to work out what might be going wrong...

HOWEVER... when you use so many inner joins, you have the potential for
disaster. The reason is that an Inner join relies on having a matching
record in each table to return a result... therefore the more inner joins you
have, the more tables that must have a record to return a result, therefore
one table nested deep down that doesn't contain data could have unintended
consequences. Is there the possibility in your data of using some left joins?

Anyway, that should get you on the right track - get the select working then
stick it back into the insert query.

Hope that helps.

Damian.