Creating a query to catch exceptions in tables?

[TKTheKid]

Hello everyone,



In the attached database file, you'll see two tables of inventory data (tableWarehouseData and tableKWIData).

What I had the query successfully(?) do was subtract the quantity amounts in each table to find ones that didn't match and then only print those records.

However, I'm trying to test for exceptions and I'm running into problems. What I did was add a fake record into the tableWarehouseData for ARTICLE 1111111111 and QUANTITY 218051. I was hoping this would cause the query to pick this up as a "difference" as well, but it does not.

How would I make the query catch exceptions like these when there is ARTICLE data only in tableWarehouseData or ARTICLE data only in tableKWIData?

I hope I explained myself well enough.

Thank you,
Tony

[June7]

If you want the check for exceptions to go both directions, need a data source of all possible article numbers to be found in both tables. Create that source with a UNION query of the two tables.

SELECT ARTICLE FROM tableKWIData
UNION SELECT ARTICLE FROM tableWarehouseData;

Then:

SELECT Articles.ARTICLE, tableKWIData.QUANTITY, tableWarehouseData.QUANTITY, tableKWIData.ARTICLE, tableWarehouseData.ARTICLE, [tableKWIData.Quantity]-[tableWarehouseData.Quantity] AS Difference
FROM tableWarehouseData RIGHT JOIN (tableKWIData RIGHT JOIN Articles ON tableKWIData.ARTICLE = Articles.ARTICLE) ON tableWarehouseData.ARTICLE = Articles.ARTICLE
WHERE ((([tableKWIData.Quantity]-[tableWarehouseData.Quantity])>0)) OR (((tableWarehouseData.ARTICLE) Is Null)) OR (((tableKWIData.ARTICLE) Is Null));

[TKTheKid]

Ah, okay. When I run queryDifference it now shows the two fake exception records I expected to see from each table, but why doesn't the difference appear in the Difference column? I'm guessing because it's trying to subtract from NULL, but is there any way that the difference for those exceptions records could still appear in the Difference column? I would need that to create the report at the end.

Also, speaking of the report, when I try to access my report reportDifference, I get the following message now:

"The specified field 'tableKWIData.QUANTITY' could refer to more than one table listed in the FROM clause of your SQL statement."

What does that mean? 'tableKWIData.QUANTITY' only refers to one table, doesn't it?

Tony

[TKTheKid]

I used the NZ function and that solved my problem of the difference not appearing for those exceptions:

Difference: Nz([tableKWIData].[Quantity],0)-Nz([tableWarehouseData].[Quantity],0)

--------------------------------------------------

But I'm still having the problem with below:


"Also, speaking of the report, when I try to access my report reportDifference, I get the following message now:

'The specified field 'tableKWIData.QUANTITY' could refer to more than one table listed in the FROM clause of your SQL statement.'

What does that mean? 'tableKWIData.QUANTITY' only refers to one table, doesn't it?"

[June7]

Odd that a form based on the same design as the report did not error.

The issue is the UNION, although I don't why. I made a table from the UNION and eliminated the query and used the new Articles table in report and it works.