Hi. I'm relatively new to SQL so I apologise if this question misses something basic. I'm building an access database for tracking stock / inventory of non-discrete items, and I'm trying to build a list of purchase orders that can be user-filtered based on sub-table properties.
--- Background ---
I have a table "Orders" which contains various order-specific data. Each order has associated with it a number of ordered-items (table "OrderItems"). Deliveries arrive with an associated OrderID ("deliveries" table). Received items have a separate table ("ReceivedItems") with an associated OrderID, OrderItemID and DeliveryID. Multiple deliveries may may exist for any given ordered-item, and several received items may stem from a single ordered-item.
So basically it's a one-to-many relationship from Orders to [Ordered-Items, Deliveries, Received-Items], and then on to other sub-linked tables. I would like to allow searches on the related tables, while only listing orders that have matching sub-table data.
I've tried using Inner-Join between Orders and ["OtherTable"], but I get repeated order-rows. I've tried using "Select count" in the where condition, but I can't seem to restrict the "count" to only the record in question. Select First does not seem to work as I'd imagined.
----- The Question -----
Could someone suggest an appropriate SQL statement? For example, if I have the following:
Orders.ID
Deliveries.OrderID [Links to Orders.ID]
Deliveries.LocationID
How can I specify LocationID = X, and show only Order records which have an associated Delivery where Deliveries.LocationID = X?
I suppose an alternative would be to add a "tmpSelect" boolean field to the Orders table, then use VBA code to set this to True/False for each record after applying a filter. I could then requery the table to show only flagged records. But I'd prefer to avoid this if it can be solved with a simple query.
Thanks in advance for any help you can offer!