Report/Query Filtering

[Markis]

I'm trying to make a custom report/query. What I have is a purchase order
database. The tables are "Customers", "PurchaseOrders",
"PurchaseOrderDetails" and "Products". The products have a part number and
are either a "sample" or "full-part". What I want to do is produce a report
that shows only the purchase orders where a customer ordered a "sample" AND a
"full-part" of that sample. For instance...if John Smith ordered a sample of
"XYZ Gadget" and then ordered the actual part "XYZ Gadget", I would want it
to show on the report. Similarly, if Jack Bell ordered a sample for "XYZ
Gadget" and didn't end up ordering that part or maybe ordered a "XYT Gizmo",
I would not want that to show up on the report. Any help is appreciated.

 

[Markis]

If I am understanding you correctly, I do not have a separate table. All
parts are stored in a single table. There is a field in that table called
"classification" which links to another table that stores all classification
types, ie "Sample", "Molded", etc. All classifications are a "full-part"
except for "Sample". So when a customer orders a part, they can order "XYZ
Gadget-full-part", qty. 5; and they can have an order for "XYZ
Gadget-sample", qty. 1. I want to capture all those instances that have a
customer ordering that sample and full-part.

I hope I explained it better. It is a bit confusing. Thanks.

 

[Markis]

This may help. Here are results of a query. The [CustomerID] and [LastName]
are in the Customers table; the [ProductModelNumber] is in the Products table
and [ProductClassification] is in the Classifications table. What I can’t
seem to filter out are those records that are with the " * ". I only want to
return those records.

CustomerID Last name Product model number Product classification
2 Barry D-70-5555-1 FullPart
3 Jones D-80-INT-665 Samples
3 Jones D-80-INT-667 Samples
4 Davis D-80-INT-8 Samples
4 Davis DL-200-INT-8 Samples
5 Minto XX-100-0-176 FullPart
5 Minto XX-100-0-1 FullPart
**6 Allen XX-155-INT-344 Samples
**6 Allen XX-155-INT-7 Samples
**6 Allen XX-155-0-344 FullPart
7 Rundi XX-100-0-1 FullPart
7 Rundi XX-100-0-1 FullPart
7 Rundi XX-100-0-1 FullPart
7 Rundi XX-100-0-1 FullPart
7 Rundi XX-100-0-1 FullPart
7 Rundi CMP-RC-0-0 FullPart
7 Rundi CMP-RD-0-0 FullPart
**8 Crumb XX-155-INT-1 Samples
**8 Crumb XX-155-INT-176 Samples
**8 Crumb XX-155-INT-224 Samples
**8 Crumb XX-155-INT-14 Samples
**8 Crumb XX-155-INT-38 Samples
**8 Crumb XX-155-INT-15 Samples
**8 Crumb XX-155-0-1 FullPart