Query - PO Numbers with multiple receives

[ch74home]

Hello All! Im running into an issue with a query I need in Access. Im getting data duplication in my query results.



The end goal is to do vendor evaluations based on the date products are ordered, date promised, and date received. Our erp software stores this data in 3 access tables.

I setup a query linked by PO number to all 3 tables. When I run the query, I get the correct results, but multiplied by the number of RECEIVES on the PO. If the PO does contain multiple receives on it, there is another field in one of the tables that contains the "Receive Number" field. When we order multiple products from a vendor on one PO and they deliver those products on 2 different dates, the data gets multiplied by the number of times received against the PO indicated by the receive number. So the only true correct result are those where the PO contained only 1 receive the way I current have the query setup.

Product A and Product B are ordered on 3/1/2019 PO Number 1234. The vendor promised to deliver both products by 3/7/2019. We received Product A on 3/6/2019 which would create PO 1234 Receipt 1 and product B on 3/9/2019 which would create PO 1234 Receipt 2. I would get 4 results, 2 receives X 2 products.

Data looks like this:
PO-1234, RECEIPT NUMBER-1, Product-A, PO DATE-3/1/2019, DUE DATE-3/7/2019, REC DATE-3/6/2019
PO-1234, RECEIPT NUMBER-1, Product-A, PO DATE-3/1/2019, DUE DATE-3/7/2019, REC DATE-3/6/2019
PO-1234, RECEIPT NUMBER-2, Product-B, PO DATE-3/1/2019, DUE DATE-3/7/2019, REC DATE-3/9/2019
PO-1234, RECEIPT NUMBER-2, Product-B, PO DATE-3/1/2019, DUE DATE-3/7/2019, REC DATE-3/9/2019

Data should look like this:
PO-1234, RECEIPT NUMBER-1, Product-A, PO DATE-3/1/2019, DUE DATE-3/7/2019, REC DATE-3/6/2019
PO-1234, RECEIPT NUMBER-2, Product-B, PO DATE-3/1/2019, DUE DATE-3/7/2019, REC DATE-3/9/2019


So the question is, how can I accommodate for the possibility that multiple receives might happen on a PO? Could someone point me in the right direction please? Let me know if im not clear enough in my description. Thank you!

[ranman256]

change the query to ignore duplicates:
SELECT DISTINCT * FROM TABLE

(aka set query property UNIQUE VALUES = TRUE)
You may have an extra field in the query preventing unique.

[June7]

If you enter PO and Receipt records for each product, perhaps do a compound join on PO and Product.