Re: Seemingly (and probably) complex re: turnaround time

From: Andreas (nobody_at_home.com)
Date: 03/21/05 

Date: Mon, 21 Mar 2005 21:19:54 +1200

Hi,

Step 1:
- Create a query based on "tblProduction". Let's call it
"qryAnalysis01LatestProductionDateForEachOrder"
- Bring in the OrderID
- Create a calculated column: Production: NZ(ProductionDate,Date())
- Change the query to a summary totals query (Sigma on toolbar)
- For OrderID, the totals row should say "Group By"
- For the calculated column, the totals row should say "Max"
= This should give you a single entry for each order, showing the
OrderID and the latest date something was produced for this order (or
the current date if it has not yet been filled in)

Step 2:
- Create a query based on "tblProduction" and
"qryAnalysis01LatestProductionDateForEachOrder". Lets call it "qryAnalysis"
- Link the 2 on the "OrderID" field
- Bring in the relevant fields, including the four date fields
- Create calculated columns:
Entry: Nz(OrderEntry,Date())
Design: Nz(OrderDesign,Date())
ReceiptToEntry: Entry - OrderReceipt
EntryToDesign: Design - Entry
DesignToProduction: Production - Design
Total: Production - Entry
- Check result in data*** view, then back to design view
- Now place the relevant criteria in the last 4 columns
- Check result in data*** view, smile, have a break :-)

Regards,
Andreas

rgrantz wrote:
> This is mostly complex because of the existence of 2 tables, not just one,
> but I imagine I'd have a tough time with just the one anyway.
>
> I'm trying to build a query around the turnaround time of several different
> phases of an order. Turnaround meaning the amount of time elaspsed between
> each phase. Thankfully, it's all about complete days by date rather than 24
> hour periods or whatever, but here's the deal:
>
> There are 4 different phases of an order (all are Dates, not Time):
>
> - OrderReceipt (date order was received by customer)
> - OrderEntry (date order was entered into the "system")
> - OrderDesign (order is configured and built)
> - OrderProduction (Order is manufactured and packaged, ready to ship)
>
> We're trying to analyze the amount of time (days) between each phase. The
> important factor is not only which phase takes longer than it's supposed to
> (differs between each phase), but also which phase is taking longER than
> it's supposed to. For instance, if the time between Receipt and Entry is
> supposed to be 2 days, I need to not only show the time it actually took,
> but also the time it is TAKING. Meaning, if OrderEntry - OrderReceipt > 2,
> but ALSO if OrderEntry is null (hasn't been entered yet) AND (Date() -
> OrderReceipt) > 2. This is very complex to me, because it applies to each
> and all phases. For instance, I want to show every Turnaround where NOT
> ONLY did it take longer than it was supposed to (ie. from Receipt to Entry
> is supposed to be 1 day, from Entry to Design is supposed to be 1 day, from
> Design to Production is supposed to be 2 days, from Receipt to Production
> is supposed to be 5 days, etc.), but if the last phase is null (not entered
> in the db, ie. the order was received, but there's no date entered for
> Entry, or it was entered and there's no date for Design), if Today's date
> (Date()) minus the previous phase's date entry is more than it's supposed to
> be, I want to show that record.
>
> To explain further:
>
> - From Receipt to Entry should be 1 day
> - From Entry to Design should be 1 day
> - From Design to Production should be 2 days
> - From Receipt to Production should be 5 days (not necessarily the previous
> phases combined, we add an extra day for whatever complications may happen)
>
> So, if an order is received 1/1/05 and is Entered 1/2/05, great. But if
> it's received 1/1/05 and is entered 1/3/05, we want to see it. LIKEWISE, if
> it's received 1/1/05 but has no Entry Date yet, AND today's date minus the
> Received Date is more than 1 (Entry is null AND ((Date()-Receipt)>1)), we
> want to see that also. Same applies for every phase. We want to see the
> problems:
>
> - From Received to Entry > 1 day (or there is no EntryDate and Date() -
> Received >1)
> - From Entry to Design > 1 day (or there is no DesignDate and Date() -
> Entered > 1)
> - From Design to Production > 2 days (or there is no ProductionDate and
> Date() - Design > 2)
>
> The complicating factor, by the way, is that the ProductionDate comes from a
> different table that has a many-to-one relationship with the Order Table
> (which has Receipt, Entered, and Design), because one order may have
> multiple identical items that are produced at different times, so really we
> need the date of the LAST one. But excluding that, I still need help with
> this, even assuming all dates come from ONE table.
>
> Trying to get this all together in one query, and I'm having a hell of a
> time. Again, feel free to provide help even assuming all dates are in one
> table, but if you happen to know how to include the whole "Produced" date
> being in a different table for multiple items, hell yeah.
>
> Can anyone help with this?
>
> Thanks for reading, and in advance for responding.
>
>