Calculating the difference between multiple dates in multiple queu

[Greg]

I am trying to calculate the total # of days between start and end dates for
multiple events when they go back and forth from different queues. Example:

Date - Queue1 Pending
Date - Queue1 In Process
Date - Queue1 Complete
Date - Queue2 Pending
Date - Queue2 In Process
Date - Queue2 Complete

I can calculate if each event has a pending, in process and complete event
but there are cases where an item is being tracked and is in process in
queue2 then goes to a research queue with an eventy type of in process then
back to queue2 as in process. My question is if a queue doesn't have an
event of pending to begin an event and an event of complete to end an event,
how to calculate the difference between dates if an event only has a pending
and in process. I need to calculate the number of days in each queue even if
there is only a pending event and an in process event or an in process event
and a complete event. The dates are from a tracking system that tracks new
documents from receipt through completion.

 

[Fred]

I noticed that nobody answered.

Whether or not such was intended, this is a structure question. In order to
help on those, we need a clear description of the key items of the situaiton
that you want to database. You used a lot of terms (queue, queue1, queue2,
event, in process event, pending, complete, which have important YOU-SPECIFIC
definitions that you've not told us about.

Taking a guess, I'm thinking that you have a table where a record
constitutes a document that has come into your system. I'm guessing that you
have two main sequences that these run through, and that you are using the
word "queue" to loosely refer to that sequence, and not the more traditional
definition which would be the set of documents that are "waiting". And that
you the dates of three "mileposts" for a document flowing through each of
these sequences.

Assuming that these are date/time fields, your can calculate differences
between any of the entered dates at will, as well as the differences from
the current date.

If the above guesses are right, you could use the date diff function (on
Pending ande Complete fields) to calculate the total time in the Queue for
completed items, and it on "Pending" and now() for those where such has not
been entered.
with an iif function to automatically decide which gets calculated.

 

[Greg]

Hi Fred,

The table structure contains a unique identifier field, an event name field
(queue name) and a date field which contains the date that the document is
assigned to each queue. Those events with a pending, in progress and
complete date I can calculate the # of days correctly using DateDiff function
but not all events have a pending, in progress and complete date associated
with them. Anytime in the process the document can be sent to a research
queue. When someone sends to the research queue, the 1st event is In Process
and after the research is completed usually doesn't have a complete event.
The document can be sent to the research queue multiple times during the
process. The problem I am having is calculating the # of days each time the
document is sent to a research queue. What is happening is I am using min
and max in the query, the result I receive is from the beginning of the 1st
time in research to the last time was in research rather than being able to
have the total # of days each time the document goes to research.

 

[Fred]

Greg,

I mean this in a manner-of-fact (not negative) way, but I think that your
second post inadvertently ignored my first response and so I got nowhere to
go with trying to help.

Not know the key info, my wild guess is that you need a structural change,
with a table for documents, and a (linked) table of instances of the document
entering new statuses, and that such would support what you are trying to do.


Sorry I could not be of more help.