A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Seemingly (and probably) complex turnaround time



 
 
Thread Tools Display Modes
  #1  
Old March 21st, 2005, 08:34 AM
rgrantz
external usenet poster
 
Posts: n/a
Default 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  
Old March 21st, 2005, 10:19 AM
Andreas
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:38 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.