Order Tracking

[aladrach]

OK, so I'll try to explain this the best I can.

I have a table (Time Table) with the fields ID, EmpID, Part Number, Order Number, Start and Finish Time, EmpName, Location, and Quantity.

I've developed a database that has a multi-item form showing all of the non-finished orders. These orders are entered by a subform, with the field lists, and a on-screen number pad for the quantity. The user will scan his Employee ID, the Order Number, Part Number, and Location (what station he's at) barcodes, enter the quantity of parts that he has completed, and hit start. Then when he's done, he'll select his order from the list and hit finish.

And so the part proceeds through the shop, until shipping. When the item has shipped, it is given the location, "SHIPPED".

Now for my dilemma:

I need a mechanism to show a report of all the open order that we have. Easy, right? But there's a hitch. Some orders have multiple parts, and we want to see all the parts, until all of them have been shipped.

The problem is that the way I am tracking the production time of the parts, each location makes a new entry into the table. This is for cost/labor tracking.

I have made a report that shows all open parts, grouped by order number, but as each location is a separate entry, the only item that disappears once the order HAS been completed is the SHIPPED entry. The rest remain. How can I show a report that lists all open orders, and include parts that have been completed until the order is closed? Furthermore, how do I get those other entries to disappear once the last entry has been marked as shipped?

I'm sure I missed something, so if you have questions, fire away.

I've attached our entry-form database, but not sure it it'll help, as it links to another database for the tables.

Here is an sample of an order:

ID--EmpID--Part Number--Order Number-- Start Time------------ Finish Time---------- EmpName------- Location
75--12345--A12----------12552----------6/30/2011 1:28:50 PM-- 6/30/2011 1:30:34 PM--Bob Villa------CNC
83--12345--A35----------12552----------7/1/2011 8:06:54 AM--------------------------John Smither-- SHIPPED


78--12345--A35----------12552----------6/30/2011 1:52:44 PM------------------------ John Smither-- ASSY
73--12345--A35----------12552----------6/30/2011 1:15:20 PM-- 6/30/2011 1:15:59 PM--John Smither-- CNC
80--12345--ASAL1299---- 12552----------6/30/2011 1:53:35 PM------------------------ John Smither-- BANDS
82--10001--ASAL1299---- 12552----------6/30/2011 1:54:25 PM------------------------ Bob Villa------SHIPPING

So as you see, this is all one order. One location line of one part in one order is SHIPPED. What needs to happen is if a location line of shipped is added to all three parts that it is hidden.

[cheshire_smile]

The attachment you provided is not working for me. I get "unrecognized database format". I need to see the tables, fields, and relationships between them.

[aladrach]

OK. I saved it out as an MDB instead, as I use Access 2010. Also notice, I made an update to the original post for clarification, containing one of the tables.

[cheshire_smile]

So from the sample db your provided. You DO NOT have tables listing all the orders and parts?

[aladrach]

Correct. The order and part number is stored in the "Time Table" in the individual record.

[cheshire_smile]

I think I got the gist now. You have orders/parts. The table [Time Table] is basically a status list of each part as it changes hands and is being worked on. Can I presume then that the latest [Start Time] is the current status of the part?

[aladrach]

Correct. That's it. Unless the item is between stations. Then there will be no open time entries.

[cheshire_smile]

This Should give you the current location of every part (with the order number) and exclude any part that has been marked as "Shipped".

 
SELECT [Time Table].[Order Number], [Time Table].[Part Number], [Time Table].[Start Time], [Time Table].Location AS Current_Location
FROM 
     (SELECT [Time Table].[Order Number], [Time Table].[Part Number], Max([Time Table].[Start Time]) AS [Max]
      FROM [Time Table] GROUP BY [Time Table].[Order Number], [Time Table].[Part Number])  AS Max_Status, [Time Table]
      WHERE ((([Time Table].Location)<>"SHIPPED")
      AND (([Time Table].[Order Number])=[Max_Status].[Order Number])
      AND (([Time Table].[Part Number])=[Max_Status].[Part Number])
      AND (([Time Table].[Start Time])=[Max_Status].[Max]));

I HIGHLY recomend you break that table into 3 pieces. Orders, parts, and statuses. This will make the database more efficeient, and allow you to add additional marks such as adding deadlines and priorities to orders and parts.

[aladrach]

Your advice is valued and we might do that at some point.

However, the query you provided me doesn't appear to have the desired effect.

Take my example in the OP:

ID--EmpID--Part Number--Order Number-- Start Time------------ Finish Time---------- EmpName------- Location
75--12345--A12----------12552----------6/30/2011 1:28:50 PM-- 6/30/2011 1:30:34 PM--Bob Villa------CNC
83--12345--A35----------12552----------7/1/2011 8:06:54 AM--------------------------John Smither-- SHIPPED
78--12345--A35----------12552----------6/30/2011 1:52:44 PM------------------------ John Smither-- ASSY
73--12345--A35----------12552----------6/30/2011 1:15:20 PM-- 6/30/2011 1:15:59 PM--John Smither-- CNC
80--12345--ASAL1299---- 12552----------6/30/2011 1:53:35 PM------------------------ John Smither-- BANDS
82--10001--ASAL1299---- 12552----------6/30/2011 1:54:25 PM------------------------ Bob Villa------SHIPPED

Here we have order 12552. Order 12552 contains an A12, A35, and an ASAL1299. As you see, the A35 and the ASAL1299 have been marked as shipped.

Running your query hides all three parts.

We would like to be able to see everything on that order until each part has a line item marking it as shipped. At that point then all three should be hidden.

Does this help you understand our dilemma?

Thanks for your time!

[aladrach]

I appreciate your help, but I've got an answer from another forum. Thanks again!

SELECT [Time Table].[Order Number], [Time Table].[Part Number], [Time Table].Location, [Time Table].[Start Time], [Time Table].[Finish Time]
FROM [Time Table]
WHERE [Time Table].[Order Number]  In
(SELECT DISTINCT [Order Number] FROM [Time Table] AS Q1 WHERE exists
 (SELECT DISTINCT [Part Number] FROM [Time Table] AS Q2 WHERE [Part Number] NOT IN 
(SELECT DISTINCT [Part number] 
FROM [Time Table] AS Q3
WHERE [Location]='Shipped' AND Q2.[Order Number]=Q3.[Order Number])  AND Q2.[Order Number]= Q1.[Order Number] ))