Skip values on a list...

[EDUARDOELLOCO]

Hello guys,

Imagine I have 3 tables: the first is named Releases, the second Shipping and the third Accounting.

Every time a product is released, a combobox (or list) on Shipping will add its ReleaseID to it. Then, that product is shipped and marked as so. Next time I open Shipping and it lists the ReleaseIDs, I don't want to see the already shipped ones listed.

The same with Accounting. Every time a product is shipped, a combobox (or list) on Accounting will add its ReleaseID to it. When the product is invoiced and I open Accounting, I don't want to see the already invoiced listed.

I started assuming that Releases, Shipping and Accounting were tables but some could be queries, I guess.

How can I achieve the above explained?

 

[s_woodbury]

How about adding a column to identify when these events happen? Use an if statement to identify if a ReleaseID is present and then filter the column to blanks?

 

[UncleCatwoman]

I'm going to make a couple of assumptions here ... but this is what I did when I had to track orders and sales:

Start With 5 tables:

tbl_Products - your master list of products you sell. Each item has an ID field [ProdID] as well as name, source, price, etc ...
NOTE - If you're just tracking the status the orders (and not the items in them) you don't need this table

tbl_Statuses - your master list of statuses for an order/sale. I would name the main field [StatID] and use 'Received', 'Packed', 'Shipped', Invoiced', 'Closed' and anything else you need

tbl_Orders (or, for you, Releases) - your orders. Each record needs an ID field [OID], all the appropriate customer information and a true/false field for whether the order is still open or [Closed]?

tbl_OrderItems - each order gets a record for each item on it. Start with fields [OID], [ProdID] and [Quantity] and make sure you have a relationship so it can't contain an item that isn't in the master tbl_Products table.
NOTE - If you're just tracking the status the orders (and not the items in them) you don't need this table

tbl_OrderStatus – four fields here: [RecID] (autonumber field to give a unique ID to each record), [OID], [StatDate] and [StatID] - listing the date each order hits the different statuses.

Build a query that pulls the latest RecID for each open order:

qry_CurrentOrderStatus_subquery
SELECT tbl_OrderStatus.OID, MAX(tbl_OrderStatus.RecID) AS LastRec
FROM tbl_OrderStatus INNER JOIN tbl_Orders ON tbl_OrderStatus.OID = tbl_Orders.OID
WHERE tbl_Orders.Closed = False
GROUP BY tbl_OrderStatus.OID
ORDER BY tbl_OrderStatus.OID

Then build a query that uses the last query to pull the current status of each open order:

qry_CurrentOrderStatus
SELECT tbl_OrderStatus.OID, tbl_OrserStatus.StatID
FROM tbl_OrderStatus INNER JOIN qry_CurrentOrderStatus_subquery ON tbl_OrderStatus.OID = qry_CurrentOrderStatus_subquery.OID AND tbl_OrderStatus.RecID = qry_CurrentOrderStatus_subquery.LastRec
ORDER BY OID

Then use qry_CurrentOrderStatus as the source for your shipping and invoiced lists:
SELECT *
FROM qry_CurrentOrderStatus
WHERE (((qry_CurrentOrderStatus.StatID) <> ‘Invoiced’));

Hope this helps!

UC

 

[EDUARDOELLOCO]

Thanks for your answer.

I'll give it a try as soon as I have some time and I update you then