Choose date in Overdue_POD_Report if date is less than date in Open_POD_Report. (1 Viewer)

[gcarpenter]

SELECT Overdue_POD_Report.Plant, Overdue_POD_Report.[Plant Description], Overdue_POD_Report.[Ship to Customer Name], Overdue_POD_Report.[Business Division], Overdue_POD_Report.[Division Group], Overdue_POD_Report.[Shipping Type], Overdue_POD_Report.[Order Number], Overdue_POD_Report.[Delivery Number], Overdue_POD_Report.[Actual GI Date], Overdue_POD_Report.Incoterms, Overdue_POD_Report.FU, Overdue_POD_Report.FO, Overdue_POD_Report.Carrier, Overdue_POD_Report.[Delivery Quantity], Overdue_POD_Report.[Planned End Date], Overdue_POD_Report.[Delivery Date], Overdue_POD_Report.[POD PostedStatus], Overdue_POD_Report.[Post Delay], Overdue_POD_Report.[Workdays from/to GI Date], Overdue_POD_Report.[Workdays from/to Planned End Date], Overdue_POD_Report.[ETA Delay], Overdue_POD_Report.[Formatted: 02-13-2023 07:20:50 by Gary Carpenter]

FROM Overdue_POD_Report

WHERE Min [Planned End Date] (SELECT [Planned End Date] FROM Open_POD_Report);

 

[arnelgp]

how many records does OPEN_POD_Report has?

 

[gcarpenter]

Overdue table has 10,000 (running total of orders where proof of delivery "POD" is late), open table has 663 (orders where POD isn't due), when an order has the planned end date changed to a future date, I need that record to drop off overdue report.

 

[arnelgp]

other than [Planned End Date] date (on both tables?), which fields can the two table be connected?

 

[gcarpenter]

By field Freight Unit (FU), or by Delivery Number, these fields have no duplicates

 

[arnelgp]

this is but a wild guess:

SELECT Overdue_POD_Report.Plant, Overdue_POD_Report.[Plant Description], Overdue_POD_Report.[Ship to Customer Name], Overdue_POD_Report.[Business Division], Overdue_POD_Report.[Division Group], Overdue_POD_Report.[Shipping Type], Overdue_POD_Report.[Order Number], Overdue_POD_Report.[Delivery Number], Overdue_POD_Report.[Actual GI Date], Overdue_POD_Report.Incoterms, Overdue_POD_Report.FU, Overdue_POD_Report.FO, Overdue_POD_Report.Carrier, Overdue_POD_Report.[Delivery Quantity], Overdue_POD_Report.[Planned End Date], Overdue_POD_Report.[Delivery Date], Overdue_POD_Report.[POD PostedStatus], Overdue_POD_Report.[Post Delay], Overdue_POD_Report.[Workdays from/to GI Date], Overdue_POD_Report.[Workdays from/to Planned End Date], Overdue_POD_Report.[ETA Delay], Overdue_POD_Report.[Formatted: 02-13-2023 07:20:50 by Gary Carpenter]
FROM Overdue_POD_Report
LEFT JOIN OPEN_POD_REPORT T1
ON OVERDUE_POD_REPORT.[Delivery Number]=T1.[Delivery Number]
WHERE
OVERDUE_POD_REPORT.[Planned End Date] > T1.[Planned End Date];

 

[gcarpenter]

Join Expression not supported error

 

[arnelgp]

is it possible for you to upload some data?

 

[gcarpenter]

some info I had to remove, field headers still there.