get the next record and compared dates (1 Viewer)

lestats · Sep 2, 2016

Hi,

I have been googling around for this question but I'm still lost.

I have the following table:

case number action date 1 calll 1/1/2014 1 UMAIL 1/1/2013 2 report 3/5/2015 2 umail 4/6/2014 3 final 6/5/2011 3 write 5/5/2010 3 umail 5/7/2009

I would like for each case number to datediff the umail date with the next action date.

For instance for case number 3 it would be "umail"' 5/7/2009 minus "write" 5/5/2010.

How can I do that in a query ?

pbaldy · Sep 2, 2016

Does this work for you?

http://allenbrowne.com/subquery-01.html#AnotherRecord

plog · Sep 2, 2016

The easiest way is via a DMin (https://www.techonthenet.com/access/functions/domain/dmin.php) inside your DateDiff.

What you do is use the DMin to get the action date of the lowest action date that is higher than your current record for the same case. Roughly, it would look like this:

DaysToNextAction: DateDiff("d", ActionDate, DMin("[ActionDate]", "YourTableName", "[CaseNumber]=" & [CaseNumber] & " AND [ActionDate]>" & [ActionDate]))

lestats · Sep 2, 2016

I have uploaded a sample of the file to provide a better example. I don't know why in my initial post the table didn't format correctly. I'm bad with PHP I guess.

I don't think the DMIN will work as I have other transactions before the UMAIL action - see case number 2 and 3 . Sorry for the confusion.

I will look at Allen's website to see if I can figure out something from it.

Thx for your quick replies.

Lestats

plog · Sep 2, 2016

DMin is the way to go. In the criteria, along with the CaseNumber and Date I laid out, you would use the action as well.

lestats · Sep 2, 2016

Thx Plog for your reply.

In the query, the expression field returns the value error. I will try to figure out why. I think we are close to something.

lestats · Sep 2, 2016

It works in the file I uploaded but whenI try it in my real database it doesn't work. It might be just a little thing that I have to find out. Thx.

lestats · Sep 2, 2016

See the attachment to see the error I receive in my real database when I run the query. I have tried to fix it but can't find the solution.

In SQL the expression I have is :

SELECT DateDiff("d",[Action_Date_Formatted],DMin("[Action_Date_Formatted]","umail_all_transactions","[Case_NO]=" & [Case_NO] & " AND [Action_Date_Formatted]>" & [Action_Date_Formatted])) AS Expr1
FROM umail_all_transactions;

plog · Sep 2, 2016

I think you need to put # around the date on the right side of the comparison.

lestats · Sep 2, 2016

I think it's because my case_no field is in text. The case number field in the database its format is i.e. 000021254L2. That's why it returns a error I believe.

pbaldy · Sep 2, 2016

Both actually:

DMin("[Action_Date_Formatted]","umail_all_transactions","[Case_NO]='" & [Case_NO] & "' AND [Action_Date_Formatted]>#" & [Action_Date_Formatted] & "#")

lestats · Sep 6, 2016

Hi,

The Solution that works for we is the following. Thx for you help much appreciated. I have learned a lot.

SELECT T.casenumber, T.actionDate, T.NextDate, T.action, DateDiff("d",actionDate,NextDate) AS Expr1
FROM (SELECT casenumber, actionDate, action, ( SELECT MIN(actionDate) FROM 1 T2 WHERE T2.casenumber = T1.casenumber AND T2.actionDate > T1.actionDate ) AS NextDate FROM 1 AS T1) AS T
ORDER BY T.casenumber, T.actionDate;

get the next record and compared dates (1 Viewer)

lestats

Registered User.

pbaldy

Wino Moderator

plog

Banishment Pending

lestats

Registered User.

Attachments

plog

Banishment Pending

lestats

Registered User.

lestats

Registered User.

lestats

Registered User.

Attachments

plog

Banishment Pending

lestats

Registered User.

pbaldy

Wino Moderator

lestats

Registered User.

Similar threads

Users who are viewing this thread