Multiple date math

  • Thread starter Thread starter pmelk6
  • Start date Start date
P

pmelk6

Hello,

I have a table that lists job numbers (JCN) and dates for when each
job went into a particular status as it moved through our laboratory.
AWM is the Awaiting Maintenance field, there are several hold status
fields, an AFD (Awaiting maintanance From Deferred) field, and an ACP
(Awaiting Customer Pickup) field. All of these fields (except JCN) are
date fields. The difference between the hold status dates and the next
higher AFD date is delay time. What I need to do is find out how many
days a job spent in the lab minus any delay time. The problem is that
any one job may go into any, all, or none of the delay status'
multiple times but goes into AFD status each time it comes off hold (if
it went on hold). I need to be able to calculate the delay time by
subtracting the delay status date from the next higher AFD date, add up
all those days and subtract them from the time between the AWM date and
the ACP date. If there wasn't any delay time, I can just subtract the
AWM date from the ACP date, I can at least do THAT on my own.
I could be in way over my head here, so just tell me if this is
going to be too complicated for a relative novice with Access!
 
It would be a lot clearer if you posted some sample data, but I think you can
do this in a totals query:

Group By [JCN], [Hold Date]
Minimum [AFD]-[Hold Date]

This assumes that every hold date is matched with an AFD date. Since the
corresponding AFD date for every [Hold Date] is the one where [AFD]-[Hold
Date] is the smallest value, you can use the above output in a totals query.
Then, you just sum the results for each JCN in a query on these results. HTH
 
You need to normalize your data by adding a table in a one-to-many
relationship from JCN to JCN_Status.

JCN_Status --
JCN_StatusID - Autonumber - primary key
JCN - foreign key relating to JCN table.
Status - Text - Validation - AWP AWM MIP ACP AFD

Then in your query place the JCN_Status table twice (Access will add a sufix
of '_1' to the table name).

Use a criteria of >[JCN_Status].[JCN_StatusID] to pull the following status
from >[JCN_Status_1].[JCN_StatusID].

kingston via AccessMonster.com said:
It would be a lot clearer if you posted some sample data, but I think you can
do this in a totals query:

Group By [JCN], [Hold Date]
Minimum [AFD]-[Hold Date]

This assumes that every hold date is matched with an AFD date. Since the
corresponding AFD date for every [Hold Date] is the one where [AFD]-[Hold
Date] is the smallest value, you can use the above output in a totals query.
Then, you just sum the results for each JCN in a query on these results. HTH

Hello,

I have a table that lists job numbers (JCN) and dates for when each
job went into a particular status as it moved through our laboratory.
AWM is the Awaiting Maintenance field, there are several hold status
fields, an AFD (Awaiting maintanance From Deferred) field, and an ACP
(Awaiting Customer Pickup) field. All of these fields (except JCN) are
date fields. The difference between the hold status dates and the next
higher AFD date is delay time. What I need to do is find out how many
days a job spent in the lab minus any delay time. The problem is that
any one job may go into any, all, or none of the delay status'
multiple times but goes into AFD status each time it comes off hold (if
it went on hold). I need to be able to calculate the delay time by
subtracting the delay status date from the next higher AFD date, add up
all those days and subtract them from the time between the AWM date and
the ACP date. If there wasn't any delay time, I can just subtract the
AWM date from the ACP date, I can at least do THAT on my own.
I could be in way over my head here, so just tell me if this is
going to be too complicated for a relative novice with Access!
Click to expand...
Click to expand...
 
Holy cow you guys are quick!

Thanks for the quick response, I'm going to have to figure out the
"normalizing of data" part. I'm not sure I understand that. I'll
respond again if the rocks in my brain don't make fire when I knock
them together.

My table is as follows:

JCN AWM_date AFD_date AWP_date DMR_date ACP_date
2534 9/7/2006 9/8/2006
2535 9/8/2006 9/18/2006 9/15/2006 9/17/2006 9/22/2006
2535 9/8/2006 9/16/2006 9/15/2006 9/17/2006 9/22/2006
2537 9/8/2006 9/25/2006 9/9/2006 9/25/2006

Where all JCNs will have a AWM_date entry and an ACP_date entry, and
AWP_date and DMR_date are when something goes on hold and AFD is when
it comes off hold. As you can see, JCN 2535 is where there is a
problem. That is the same job, it just went on hold twice before it
was completed. I can get a query to do the math as long as something
doesn't go into a hold status more than once.
 
Back
Top