If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Seemingly (and probably) complex turnaround time
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. |
#2
|
|||
|
|||
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 datasheet view, then back to design view - Now place the relevant criteria in the last 4 columns - Check result in datasheet 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. |
Thread Tools | |
Display Modes | |
|
|