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 » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculating the difference between multiple dates in multiple queu



 
 
Thread Tools Display Modes
  #1  
Old May 11th, 2009, 09:56 PM posted to microsoft.public.access.tablesdbdesign
Greg
external usenet poster
 
Posts: 748
Default Calculating the difference between multiple dates in multiple queu

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.
  #2  
Old May 12th, 2009, 09:03 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Calculating the difference between multiple dates in multiple queu

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" wrote:

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.

  #3  
Old May 13th, 2009, 04:34 PM posted to microsoft.public.access.tablesdbdesign
Greg
external usenet poster
 
Posts: 748
Default Calculating the difference between multiple dates in multiple

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" wrote:

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" wrote:

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.

  #4  
Old May 13th, 2009, 05:12 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Calculating the difference between multiple dates in multiple

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.



 




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 02:37 PM.


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