Trouble retrieving data from two tables (1 Viewer)

[atrium]

I have a parts order form to suppliers and the order form is made up of item lines. The Order header and the order item lines are linked by the order number.

My problem is I have some fields on the item line that are not on the order header and I want to create a file that has all the Orders that have a particular filed from the item lines.
Customers order products and then the company creates an order to a supplier made up of many items that have been ordered. I need to view all those orders that have an item with customer no 0201
E.g, Customer number is on the item line but not on the order header

scaled down structure
Orders Table = OrdersId, OrderNo, Supplier, Date Ordered
Item Table = ItemId, Quantity, PartNo, Desc, CustomerNo, OrderNo, DateCreated

I need to report all Orders with Customer no 0201
I need to report all Orders with PartNo 123456


I'm not sure how I should create a table that would meet my needs

Any help would be most appreciated

 

[arnelgp]

you create a Query first joining the two tables:

1. Query1.
select a.ItemID, a.Quantity, a.PartNo, a.Desc, a.CustomerNo, a.OrderNo, a.DateCreated,
b.Supplier, b.OrderDate
from [Item Table] as a Left Join [Orders Table] as b
on a.OrderNo = b.OrderNo;

1. from Query1, you can filter it on CustomerNo = 0201
2. from Query1, you can filter it by PartNo = 123456

 

[atrium]

Thanks arnelgp, I will try to implement that. It sounds what I need