Outer Join query with function not working

[davebrads]

Hi

I have solved my problem with a work around, but I thought I would share it anyway in case anybody can shed any light on why it didn't work.

The application is for handling periodic stock takes.

I have a table containing a list of parts and quantities exported from the accounts application, with a field containing a foreign key that indicates the date the data was exported. A second table contains a summary of the counts from the stock take, again with the same foreign key field.

The following query is supposed to give a list of all the stock items where there is a variance in the stock quantity between the accounts application and the actual stock count:

SELECT T0.PartNo, T0.DESCRIPTION, T0.QTY_IN_STOCK, nz([T1].[Quantity],0) AS StockCount,


FROM (SELECT PartNo, Description, QTY_IN_STOCK FROM tblStockBeforeStockEntry WHERE StockTakeKey=Stock_Take_Key()) AS T0
LEFT JOIN (SELECT PartNo, Quantity, StockTakeKey FROM tblStockTakeExplodedStock WHERE StockTakeKey=Stock_Take_Key()) AS T1
ON T0.PartNo=T1.PartNo
WHERE (((Round([T0].[QTY_IN_STOCK],0)-Round(nz([T1].[Quantity],0),0))<>0))
ORDER BY T0.PartNo;

Stock_Take_Key() is a vba function that returns the currently selected foreign key.

When I run the above query the outer join does not work, so that parts that have a stock quantity in the accounts application but no stock count records are not shown. If I hard code the current foreign key into the sql statement to replace Stock_Take_Key() then the outer join works fine.

The above query is the data source for a report. My work around was to generate the above query including the hard-coded foreign key in vba and store it in a global variable, then set the record source to this variable in the On Open event procedure for the report.

[boblarson]

You can't have a query with an Outer Join and have criteria on any field that is on a field in the table/query that is the part outer joined (except using Is Null or Is Not Null). If you have criteria that needs to be applied on that part, you need to create a separate query for that and then you can Outer Join THAT query into this one.

[davebrads]

But if I enter the query as follows:

SELECT T0.PartNo, T0.DESCRIPTION, T0.QTY_IN_STOCK, nz([T1].[Quantity],0) AS StockCount
FROM (SELECT PartNo, Description, QTY_IN_STOCK FROM tblStockBeforeStockEntry WHERE StockTakeKey=16) AS T0
LEFT JOIN (SELECT PartNo, Quantity, StockTakeKey FROM tblStockTakeExplodedStock WHERE StockTakeKey=16) AS T1
ON T0.PartNo=T1.PartNo
WHERE (((Round([T0].[QTY_IN_STOCK],0)-Round(nz([T1].[Quantity],0),0))<>0))
ORDER BY T0.PartNo;

it does work, which contradicts what you are saying. So maybe my question should be "Why does this work?".

Also, when I originally constructed the query, the two sub queries were saved as separate queries so that the main query read:

SELECT qry2.PartNo, qry2.DESCRIPTION, qry2.QTY_IN_STOCK, nz([qry3].[Quantity],0) AS StockCount
FROM qry2
LEFT JOIN qry3
ON qry2.PartNo=qry3.PartNo
WHERE (((Round([qry2].[QTY_IN_STOCK],0)-Round(nz([qry3].[Quantity],0),0))<>0))
ORDER BY qry2.PartNo;

and it still didn't show all the records.

[boblarson]

What is the datatype of the Quantities fields? Why are you using Round on them? I suspect those two questions may be what the main problem hinges on.

Quantity fields shouild be either Integer or Long Integer. If you use Single or Double, then they can suffer from Floating Point errors which can cause problems if you are trying to compare them and the Round Function suffers from some problems as well. You might need to resort to the Round function from Ken Getz:
http://access.mvps.org/access/modules/mdl0054.htm

If you use that one, rename the function ERound and change the value assignment in the function to use ERound as well, so it doesn't conflict with the existing Round function.

You might also look at these and see if it has anything to do with your situation:
http://allenbrowne.com/round.html
http://allenbrowne.com/BugOuterJoinExpression.html

[davebrads]

It is necessary that the data types are double, as some of the parts are raw materials and are used by weight or length. I have used round on the quantities so that if there is a small discrepancy between these values it is ignored, just to save on a bit of work entering data back into the accounts application. I have just now run the query again without using Round to see what would happen, but I still have the same problem.

Though the link to Allen Browne's Outer Join bug seems to describe a very similar situation, though I can't get my head around whether it has the same cause.