Count items in order

[garywood84]

I'm building an order entry database. It has two tables (amongst others) called tblOrders and tblOrderDetails, related together on the OrderID field. For every order, there is one record in tblOrder, and as many records in tblOrderDetails as there are individual lines in the order (so, if, for example, the order is for 10 pencils and 2 pens, then tblOrderDetails has 2 records).

Each entry in tblOrderDetails has a Status field, which indcates whether or not the items have are in manufacture, shipped, delivered, etc).

I need a way to get Access to show me only "Open" orders (i.e. ones in which not every item has been shipped). Can I set up a query to determine how many lines each order consists of, and then is there a way to get access to check if all of these are "Shipped"?

Many thanks in advance for any pointers,

Gary

 

[wazz]

put all the fields that you want from both tables into your query. add your 'status' criteria (Like "manufacture" or Like "delivered" (or use IDs or whatever (check syntax)). that should do it.

but it seems like you want to see all the rows, including the shipped??

try:
- check the query above that it's doing what you want.
- count the rows returned.
- remove all the fields except the OrderID and Status (with criteria). check the row count again to make sure nothing wierd happened. this will give you every Order that hasn't been shipped.
- make a new query just like the first one (or copy the first one before removing all the rows, but WITHOUT criteria, so you have every order and detail.
- add the first query to this one and link the OrderIDs. This will limit the second query to only Orders that haven't been shipped, but will give you every detail row.

- off the top of my head...hth.