Question 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.

 

[spikepl]

I suspect you have A2010 -very few people have that.

You'd need to base your report on a query that displays what you currently display, but with some Order Numbers excluded. so

SELECT some fields FROM TimeTable WHERE [Order Number] NOT IN (SELECT [Order Number] FROM TimeTable WHERE Location ='Shipped' )

 

[aladrach]

I suspect you have A2010 -very few people have that.

You'd need to base your report on a query that displays what you currently display, but with some Order Numbers excluded. so

SELECT some fields FROM TimeTable WHERE [Order Number] NOT IN (SELECT [Order Number] FROM TimeTable WHERE Location ='Shipped' )

Wow. Very nice. One caveat. One order may have several parts. We need it to display all entries for an order until each part in the order has a "SHIPPED" entry.

Your SQL command hides the order if ANY entry displays "SHIPPED" is this even possible?

 

[spikepl]

Ok I misunderstood . try this:

SELECT some fields FROM TimeTable WHERE [Order Number ] IN (SELECT DISTINCT [Order Number] FROM TimeTable WHERE Location <>'Shipped' )

or, if you have a query already, use that:
SELECT * FROM yourquery WHERE [Order Number ] IN (SELECT DISTINCT [Order Number] FROM TimeTable WHERE Location <>'Shipped' )

This should give you alle the orders where at least one location <>shipped

 

[aladrach]

OK. That just gives blank results...

 

[spikepl]

I just updated it - there was a NOT too much

 

[aladrach]

That doesn't seem to remove anything... It just shows all orders.

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 of shipped is added to all three parts that it is hidden.

 

[aladrach]

Perhaps it would help if you had a useable copy of the database. I've attached a coverted local edition to an MDB for your viewing pleasure

 

[spikepl]

So if all parts of an order each have a line with location=shipped, then the order must not be displayed, is that so?

 

[aladrach]

Precisely.

 

[spikepl]

OK let me sweat for a while

 

[spikepl]

This is a bit trickier than I thought, so be patient. If anyone else wants a shot at this please do.

I am not giving up, so you'll get feed back

 

[spikepl]

Try this - it seems to work (but Murphy is always lurking in the background) :

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]) Not In (SELECT DISTINCT [Order Number] FROM [Time Table] WHERE [Part Number] NOT IN 
(SELECT DISTINCT [Part Number] FROM [Time Table] AS Q1 WHERE [Part Number] NOT IN 
(SELECT DISTINCT [Part number] 
FROM [Time Table] AS Q2
WHERE [Location]='Shipped' AND Q2.[Order Number]=Q1.[Order Number])))));

 

[spikepl]

And Murphy strikes again - hold on, I'll be back

 

[spikepl]

Ok I tested it on more orders and it seems to work (unless I missed something)

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] ))

 

[aladrach]

Ok I tested it on more orders and it seems to work (unless I missed something)

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] ))

By George, I think you've done it!! Many, many, many thanks!

Now a quick n' easy one. What query would I run if I wanted to see orders modified 1)today and 2)this week, regardless of status?

 

[spikepl]

As you say, it's not that difficult. I'd suggest you give it a go and seek help when stuck.

If you want a ready-made solution, my fee for that would be 150,000 DRN (dimes Roman nouveaux) + GST (which is currently at -12.34 % and therefore functions as a mail-in rebate, thanks to the corrupt government's efforts to promote industry)