Query multiple Dates

[Guest]

I have two tables T_Actions and T_CaseNbr. They are related by the field
“CaseNbrâ€. There can be many actions for one
CaseNbr. The actions are input on a datasheet subform linked to the main
CaseNbr Form using Parent/Child CaseNbr.

The Actions are dated with the Now() function when they are originated. The
fields are ActionDate, Dept, Status, Sent, Initials.

I want to make a report that will show the number of days between each
action for a case number. I tried a query that had a MAX and MIN
ActionDate, but that doesn’t get me the days between each action. (it could
also be Hours, Min, etc). Since the fields are all called
ActionDate, how can I subtract ActionDate From ActionDate?

It seems simple, but I can’t seem to get the report I need. The object of
the report is to see how long it takes for a particular department to perform
an Action, then pass it to another Dept, they do THEIR Action and pass it to
the Next Dept. The final step is the last Dept sends the “Action†to print
and marks the Action “Completeâ€.

I hope someone can make sense of this and help me find a way to do this.

Thanks,
Connie

 

[Guest]

DateDiff is your friend. You can return d as days and so on.

Type DateDiff and press return in help for all the choices.

The thing to remember its the difference between two dates
t_actions.actionDate and t_caseNbr.actionDate.... so a couple of days = 48
hours... do you see.

You need to actually find the smallest nominator, seconds by the sound of
it, and keep dividing up. 190 seconds / 60 = 3..... 3*60 = 180..... 190-180
= 10 seconds, hence 3 minutes 10 seconds. Are you sure you want to do this!?

 

[Guest]

I would be satisfied if the difference being anything less than 1 day
defaulted to 0 days, then if that wasn't close enough I will deal with
hours/min/sec. But I'm still a little unclear as to how to set this up for
a report. Do I first make a query using both tables T_CaseNbr and T_Actions.
List the T_Action.ActionDate But there isn't a T__CaseNbr.ActionDate ??
The link field is "CaseNbr". How do I get to the DateDiff you described?

Hope I'm not being too dense on this one... <smile>
Connie

 

[Guest]

The Actions are dated with the Now() function when they are originated.
The Now() function include a time component, not just date.

Do you have a ActionDate field in each of the tables?

 

[Guest]

No. The ActionDate is only in the T_Action Table. The link between the two
is the CaseNbr. Many Actions to One Case Number.

The reason for the Now() function in the ActionDate is so that it would show
what times during the day that the "Action" was done and passed to the next
person.

The goal is to see how long it takes between actions and then also total
days(?) between the first action and the "complete" action.

Am I explaining it OK?
Thanks,
Connie

 

[Guest]

This is an exciting one...

I think I know what you're after, but had to make a quick mock up... I hope
your SQL is OK!

I'm only illustrating one difference, you would need a query on a query for
each Action type. I've created your two tables and only called my actions
"1" and "2". but its expandable.

First query is:

SELECT T_CaseNbr.CaseNbr, T_CaseNbr.blah, T_Actions.Status, T_Actions.date,
T_Actions_1.Status, T_Actions_1.date
FROM (T_CaseNbr INNER JOIN T_Actions AS T_Actions_1 ON T_CaseNbr.CaseNbr =
T_Actions_1.CaseNbr) INNER JOIN T_Actions ON T_CaseNbr.CaseNbr =
T_Actions.CaseNbr
WHERE (((T_Actions.Status)="1") AND ((T_Actions_1.Status)="2"));

Summary: blah is just a field... whatever you're collecting in the CaseNbr
table... its not really needed here. The key aspect is I've added T_Actions
table in twice to query... Access creates the name T_Actions_1 automatically.
You have to manually drag the T_CaseNbr Primary Key across to the second
table... T_Actions_1

For the first T_Action table I set the column criteria for Status as "1"...
the first action... for the 2nd T_Action table I set the column criteria for
Status to "2"... so I'm selecting all Cases with a Status of 1 and a Status
of 2... ones not at this stage aren't selected.

We move on to query number 2.

SELECT Query1.CaseNbr, Query1.blah,
DateDiff("d",Query1![T_Actions.date],Query1![T_Actions_1.date]) AS
difference, Query1.T_Actions.date, Query1.T_Actions_1.date
FROM Query1;

Much simpler... but so am I.

No tables (you might need more data for your report though) just the query1.

Summary, I've now gathered my data... so I can now manipulate it or use it
for calculations, hence the two steps instead of one.

Column name: DataDiff("d", StartDate, EndDate)

My startDate is Query1![T_Actions.date]
My endDate is Query1![T_Actions_1.date]

I could have renamed the columns in query1 but I don't think it would be so
clear.

Let me know how you get on... I've only wine for company.

Cheers.

 

[Guest]

WOW! Thanks for taking the time to analyze this for me! Now, I need to
print this out and study it. I think I was on the right track - I had two
queries, but hadn't attacked it like you did.... and I didn't quite know what
to do after the two queries. I actually built to temporary tables to house
the info....then got stuck and came here.
So, now I will review, but probably won't return until tomorrow at work again.

I can't thank you enough for putting me on the right track. Let's hope we
can use that wine to celebrate a victory...this issue is KEY to do what we
need.

Thanks,thanks,thanks,
Connie

 

[Guest]

Ok. I studied this last night and tried to apply it to my situation. I'm
not sure how this will work.
1. I don't know how many Actions I will have for each CaseNbr. Do I
have to make a copy of the T_Action table in the first query for EACH Action?
Each CaseNbr record will be different ?? So How many do I create?
2. T_Action.Status is a field already used for information. I can't
use it for criteria like "1". (So I created a new field in the table to try
to substitute for it in the formula... I called it "ActNbr".) So your formula
now would read "T_Actions_1.ActNbr = 1".

3. Now, I'm confused as to where to go because I need an answer to my
question 1. How many of these tables do I need to create in Query 1?

If you can help me here to continue, I'll keep going. Thanks for sticking
with me on this. If you can think of a cleaner way to track these actions
I'm also open to changing the structure of my tables at this point.

Thanks once again for your help.
Connie

WOW! Thanks for taking the time to analyze this for me! Now, I need to
print this out and study it. I think I was on the right track - I had two
queries, but hadn't attacked it like you did.... and I didn't quite know what
to do after the two queries. I actually built to temporary tables to house
the info....then got stuck and came here.
So, now I will review, but probably won't return until tomorrow at work again.

I can't thank you enough for putting me on the right track. Let's hope we
can use that wine to celebrate a victory...this issue is KEY to do what we
need.

Thanks,thanks,thanks,
Connie

This is an exciting one...

I think I know what you're after, but had to make a quick mock up... I hope
your SQL is OK!

I'm only illustrating one difference, you would need a query on a query for
each Action type. I've created your two tables and only called my actions
"1" and "2". but its expandable.

First query is:

SELECT T_CaseNbr.CaseNbr, T_CaseNbr.blah, T_Actions.Status, T_Actions.date,
T_Actions_1.Status, T_Actions_1.date
FROM (T_CaseNbr INNER JOIN T_Actions AS T_Actions_1 ON T_CaseNbr.CaseNbr =
T_Actions_1.CaseNbr) INNER JOIN T_Actions ON T_CaseNbr.CaseNbr =
T_Actions.CaseNbr
WHERE (((T_Actions.Status)="1") AND ((T_Actions_1.Status)="2"));

Summary: blah is just a field... whatever you're collecting in the CaseNbr
table... its not really needed here. The key aspect is I've added T_Actions
table in twice to query... Access creates the name T_Actions_1 automatically.
You have to manually drag the T_CaseNbr Primary Key across to the second
table... T_Actions_1

For the first T_Action table I set the column criteria for Status as "1"...
the first action... for the 2nd T_Action table I set the column criteria for
Status to "2"... so I'm selecting all Cases with a Status of 1 and a Status
of 2... ones not at this stage aren't selected.

We move on to query number 2.

SELECT Query1.CaseNbr, Query1.blah,
DateDiff("d",Query1![T_Actions.date],Query1![T_Actions_1.date]) AS
difference, Query1.T_Actions.date, Query1.T_Actions_1.date
FROM Query1;

Much simpler... but so am I.

No tables (you might need more data for your report though) just the query1.

Summary, I've now gathered my data... so I can now manipulate it or use it
for calculations, hence the two steps instead of one.

Column name: DataDiff("d", StartDate, EndDate)

My startDate is Query1![T_Actions.date]
My endDate is Query1![T_Actions_1.date]

I could have renamed the columns in query1 but I don't think it would be so
clear.

Let me know how you get on... I've only wine for company.

Cheers.

 

[Guest]

One more time... (I haven't let you answer inbetween...sorry)<smile> But I
think I've figured out my own questions. I still had to do some minuplating
in the report, but I finally got the results I needed. I have to say I'm
amazed that the way to get the difference between two dates in the same table
in the manner I need, was harder than I thought. I wonder if anyone has a
better way of storing the data that would prevent all the extra work to find
the times between one action and the next action, then the time from THAT
action to the NEXT Action, and soforth....

Thanks, tho, and here are the two queries as they ended up:

SELECT T_CaseNbr.CaseNbr, T_Actions.StatusDesc, T_Actions.ActionDate AS
StartDate, T_Actions_1.ActionDate AS EndDate
FROM (T_CaseNbr INNER JOIN T_Actions ON T_CaseNbr.CaseNbr =
T_Actions.CaseNbr) INNER JOIN T_Actions AS T_Actions_1 ON T_CaseNbr.CaseNbr =
T_Actions_1.CaseNbr;


SELECT Q_NEW_ACTIONS.CaseNbr, Q_NEW_ACTIONS.StatusDesc,
Q_NEW_ACTIONS.StartDate, Q_NEW_ACTIONS.EndDate,
DateDiff("d",[StartDate],[EndDate]) AS DAYSBTWN
FROM Q_NEW_ACTIONS;

Then in the actual report, I had to blank all duplicates, sort by StartDate,
Place the Data in the footer of the StatusDesc. This is a similar look to
my report:

CASE NBR: 07890M
Sent to Enrollment Dept 8/1/05 10:00:00 AM
Sent to Enrollment Supv 8/1/05 10:30:00 AM
Sent to Processing Dept 8/3/05 9:30:00 AM 2
Sent to Processing Supv 8/4/05 8:00:00 AM 1
Sent to Accting Dept 8/4/05 9:00:00 AM
Sent to Accting Supv 8/5/05 1:00:04 PM 1
Sent to Print Chk 8/6/05 7:00:00 AM
1
Check Mailed 8/7/05 8:00:00 AM
1

Total Days from Beg
6

This not only shows the full tracking, but will also show the areas that the
process slows down.

Once again, I say thanks... and if anyone has a cleaner or better way of
handling
this, please feel free to comment.
Connie

This is an exciting one...

I think I know what you're after, but had to make a quick mock up... I hope
your SQL is OK!

I'm only illustrating one difference, you would need a query on a query for
each Action type. I've created your two tables and only called my actions
"1" and "2". but its expandable.

First query is:

SELECT T_CaseNbr.CaseNbr, T_CaseNbr.blah, T_Actions.Status, T_Actions.date,
T_Actions_1.Status, T_Actions_1.date
FROM (T_CaseNbr INNER JOIN T_Actions AS T_Actions_1 ON T_CaseNbr.CaseNbr =
T_Actions_1.CaseNbr) INNER JOIN T_Actions ON T_CaseNbr.CaseNbr =
T_Actions.CaseNbr
WHERE (((T_Actions.Status)="1") AND ((T_Actions_1.Status)="2"));

Summary: blah is just a field... whatever you're collecting in the CaseNbr
table... its not really needed here. The key aspect is I've added T_Actions
table in twice to query... Access creates the name T_Actions_1 automatically.
You have to manually drag the T_CaseNbr Primary Key across to the second
table... T_Actions_1

For the first T_Action table I set the column criteria for Status as "1"...
the first action... for the 2nd T_Action table I set the column criteria for
Status to "2"... so I'm selecting all Cases with a Status of 1 and a Status
of 2... ones not at this stage aren't selected.

We move on to query number 2.

SELECT Query1.CaseNbr, Query1.blah,
DateDiff("d",Query1![T_Actions.date],Query1![T_Actions_1.date]) AS
difference, Query1.T_Actions.date, Query1.T_Actions_1.date
FROM Query1;

Much simpler... but so am I.

No tables (you might need more data for your report though) just the query1.

Summary, I've now gathered my data... so I can now manipulate it or use it
for calculations, hence the two steps instead of one.

Column name: DataDiff("d", StartDate, EndDate)

My startDate is Query1![T_Actions.date]
My endDate is Query1![T_Actions_1.date]

I could have renamed the columns in query1 but I don't think it would be so
clear.

Let me know how you get on... I've only wine for company.

Cheers.

 

[Guest]

HELP! This ended up NOT working. I still can't get the difference between
two (or more) ActionDates.

Could someone take another look at this and see if you can help.
Thanks so much!!
Connie

One more time... (I haven't let you answer inbetween...sorry)<smile> But I
think I've figured out my own questions. I still had to do some minuplating
in the report, but I finally got the results I needed. I have to say I'm
amazed that the way to get the difference between two dates in the same table
in the manner I need, was harder than I thought. I wonder if anyone has a
better way of storing the data that would prevent all the extra work to find
the times between one action and the next action, then the time from THAT
action to the NEXT Action, and soforth....

Thanks, tho, and here are the two queries as they ended up:

SELECT T_CaseNbr.CaseNbr, T_Actions.StatusDesc, T_Actions.ActionDate AS
StartDate, T_Actions_1.ActionDate AS EndDate
FROM (T_CaseNbr INNER JOIN T_Actions ON T_CaseNbr.CaseNbr =
T_Actions.CaseNbr) INNER JOIN T_Actions AS T_Actions_1 ON T_CaseNbr.CaseNbr =
T_Actions_1.CaseNbr;


SELECT Q_NEW_ACTIONS.CaseNbr, Q_NEW_ACTIONS.StatusDesc,
Q_NEW_ACTIONS.StartDate, Q_NEW_ACTIONS.EndDate,
DateDiff("d",[StartDate],[EndDate]) AS DAYSBTWN
FROM Q_NEW_ACTIONS;

Then in the actual report, I had to blank all duplicates, sort by StartDate,
Place the Data in the footer of the StatusDesc. This is a similar look to
my report:

CASE NBR: 07890M
Sent to Enrollment Dept 8/1/05 10:00:00 AM
Sent to Enrollment Supv 8/1/05 10:30:00 AM
Sent to Processing Dept 8/3/05 9:30:00 AM 2
Sent to Processing Supv 8/4/05 8:00:00 AM 1
Sent to Accting Dept 8/4/05 9:00:00 AM
Sent to Accting Supv 8/5/05 1:00:04 PM 1
Sent to Print Chk 8/6/05 7:00:00 AM
1
Check Mailed 8/7/05 8:00:00 AM
1

Total Days from Beg
6

This not only shows the full tracking, but will also show the areas that the
process slows down.

Once again, I say thanks... and if anyone has a cleaner or better way of
handling
this, please feel free to comment.
Connie

This is an exciting one...

I think I know what you're after, but had to make a quick mock up... I hope
your SQL is OK!

I'm only illustrating one difference, you would need a query on a query for
each Action type. I've created your two tables and only called my actions
"1" and "2". but its expandable.

First query is:

SELECT T_CaseNbr.CaseNbr, T_CaseNbr.blah, T_Actions.Status, T_Actions.date,
T_Actions_1.Status, T_Actions_1.date
FROM (T_CaseNbr INNER JOIN T_Actions AS T_Actions_1 ON T_CaseNbr.CaseNbr =
T_Actions_1.CaseNbr) INNER JOIN T_Actions ON T_CaseNbr.CaseNbr =
T_Actions.CaseNbr
WHERE (((T_Actions.Status)="1") AND ((T_Actions_1.Status)="2"));

Summary: blah is just a field... whatever you're collecting in the CaseNbr
table... its not really needed here. The key aspect is I've added T_Actions
table in twice to query... Access creates the name T_Actions_1 automatically.
You have to manually drag the T_CaseNbr Primary Key across to the second
table... T_Actions_1

For the first T_Action table I set the column criteria for Status as "1"...
the first action... for the 2nd T_Action table I set the column criteria for
Status to "2"... so I'm selecting all Cases with a Status of 1 and a Status
of 2... ones not at this stage aren't selected.

We move on to query number 2.

SELECT Query1.CaseNbr, Query1.blah,
DateDiff("d",Query1![T_Actions.date],Query1![T_Actions_1.date]) AS
difference, Query1.T_Actions.date, Query1.T_Actions_1.date
FROM Query1;

Much simpler... but so am I.

No tables (you might need more data for your report though) just the query1.

Summary, I've now gathered my data... so I can now manipulate it or use it
for calculations, hence the two steps instead of one.

Column name: DataDiff("d", StartDate, EndDate)

My startDate is Query1![T_Actions.date]
My endDate is Query1![T_Actions_1.date]

I could have renamed the columns in query1 but I don't think it would be so
clear.

Let me know how you get on... I've only wine for company.

Cheers.