Records Discrepancy

[Guest]

Thanks For Looking at Thi

Two Tables With Similar Table Structure and Field Name
Table 1 Purchase Order
Field 1 POID Type Long Numbe
Field 2 PONum Type Strin
Field 3 Qty Type Long Numbe
Field 4 ItemID Type Long Numbe

Table 2 Invoice Detail
Field 1 DetailID Type Lon
Field 2 PONum Type Strin
Field 3 Qty Type Lon
Field 4 ItemID Type Lon

I have tried to create a query that will select only those records from table 1 where the PONum Fields are the same but the ItemID from Table 1 is not found in Table 2 or the Qty in Table 2 is Less Than the Qty in Table 1. I am trying to identify parts that were ordered for a particular invoice but subsequently did not show up in the sales invoice. Any help would be appreciated

 

[Graham R Seach]

This should do it.

SELECT * FROM Table1 As T1
INNER JOIN Table2 As T2
ON T2.PONum = T1.PONum
WHERE T1.ItemID <> T2.ItemID
AND T2.Qty < T1.Qty

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Thanks For Looking at This

Two Tables With Similar Table Structure and Field Names
Table 1 Purchase Orders
Field 1 POID Type Long Number
Field 2 PONum Type String
Field 3 Qty Type Long Number
Field 4 ItemID Type Long Number

Table 2 Invoice Details
Field 1 DetailID Type Long
Field 2 PONum Type String
Field 3 Qty Type Long
Field 4 ItemID Type Long

I have tried to create a query that will select only those records from

table 1 where the PONum Fields are the same but the ItemID from Table 1 is
not found in Table 2 or the Qty in Table 2 is Less Than the Qty in Table 1.
I am trying to identify parts that were ordered for a particular invoice but
subsequently did not show up in the sales invoice. Any help would be
appreciated.

 

[Gary Walter]

Two Tables With Similar Table Structure and Field Names
Table 1 Purchase Orders
Field 1 POID Type Long Number
Field 2 PONum Type String
Field 3 Qty Type Long Number
Field 4 ItemID Type Long Number

Table 2 Invoice Details
Field 1 DetailID Type Long
Field 2 PONum Type String
Field 3 Qty Type Long
Field 4 ItemID Type Long

I have tried to create a query that will select only those records from table 1

where
--the PONum Fields are the same but the ItemID from Table 1 is not found in Table 2
--or the Qty in Table 2 is Less Than the Qty in Table 1.

I am trying to identify parts that were ordered for a particular invoice
but subsequently did not show up in the sales invoice.PMFBI

When I read the post I see it differently.

Maybe a simple example will help to see
if I have misunderstood. If your data looked
something like below..

[Puchase Orders]
POID PONum Qty ItemID
1 A 3 1
1 A 5 2
1 A 10 3

[Invoice Details]
DetailID PONum Qty ItemID
1 A 3 1
2 A 4 2

In this simple example it is easy to
see that

-ordered 5 of item 2, but invoiced only 4
-ordered 10 of item 3, but invoiced none

I believe the following query

SELECT
PO.POID,
PO.PONum,
PO.ItemID,
PO.Qty,
Inv.Qty
FROM [Purchase Orders] AS PO
LEFT JOIN [Invoice Details] AS Inv
ON
(PO.ItemID = Inv.ItemID)
AND
(PO.PONum = Inv.PONum)
WHERE
(((Inv.ItemID) Is Null))
OR
(((PO.Qty)<>[Inv].[Qty]));

will identify your problem orders

POID PONum ItemID PO.Qty Inv.Qty
1 A 2 5 4
1 A 3 10

Apologies again for butting in.....
especially if I was wrong!

Please respond back if I have misunderstood.

Good luck,

Gary Walter