The difference between two dates in one column

[Falcon88]

hiii i have a table that contains visits of some emploees . I want to know the difference between the current visit and the visit, which has been before, for each employee by days.

 

[plog]

First, EmpName shouldn't be in the Orders table. Instead you should have an Employee table with a primary key (http://www.w3schools.com/sql/sql_primarykey.asp). Then in the Orders table you use that key to identify employees. I mean, how many Ahmed's are in the world?

Then to your issue, you need to get the last Order data for each record. You can do that by using a DMax (http://www.techonthenet.com/access/functions/domain/dmax.php). You would use the EmployeeID and their current record's date in the criteria. With that, you could then use your DateDiff function.

 

[Falcon88]

Ok sir plog :

i makes that as Example only.
but
for using the Dmax:
i want the diffrence between the current visit and only the visit that has been directly befor, not the last visit.

 

[plog]

Yes, I fully understood your post. Dmax will get you there if you use the correct criteria. That criteria should be based on the EmployeeID and the current' record's date.

 

[Falcon88]

please see to my new changes can you help me to use Dmax function ?

 

[plog]

There's no Dmax in that database. Give it a shot yourself and I will help.

 

[Falcon88]

i try to do some work, but give me Negative values.
i try :

SELECT tblOrders.OrderID, tblOrders.EmpNo, tblOrders.OrderDate, DateDiff("d",Nz(DMax("  [OrderDate] ","[tblOrders]","  [EmpNo] =" & [EmpID] And "[OrderDate]<= #" & [OrderDate] & "#"),[OrderDate]),[OrderDate]) AS DatesBetween
FROM tblEmploees INNER JOIN tblOrders ON tblEmploees.EmpID = tblOrders.EmpNo
ORDER BY tblOrders.OrderID;

 

[plog]

I wouldn't go for a touchdown all at once, get the Dmax to work like it should, don't put it in the DateDiff yet. Also, the criteria is parsed incorrectl--you never started your quote markes after your first variable. Last, you don't want a date <= to the current date, you want a date less than the current date. I think this is what you want:

DMax("[OrderDate] ","[tblOrders]","[EmpNo] =" & [EmpID] & "And [OrderDate]<#" & [OrderDate] & "#") AS LastOrderDate

 

[Falcon88]

but this gives me the last [OrderDate]. i want to know how much days between this [OrderDate] and the [OrderDate] that is directly before. i think that Dmax fun. coudn't achieve our destination.

 

[sneuberg]

I find Allen Browns examples really good for this see http://allenbrowne.com/subquery-01.html In the attached database I wrote the query Prior Visits just by substituting your problem into the "Get the value from another record example"

I also included a query, "Day Between Prior Visits" which does the math. Excuse that name. It doesn't really fit.

This query doesn't handle the case where there was no prior visit. You can deal with that with the iff function. If you have problems let me know.

Note: I used the first database you uploaded.

 

[plog]

Once you get your DMax working, then you can put your DateDiff around it to calculate the difference in days. Did your DMax return the correct date for each record?

 

[Falcon88]

I find Allen Browns examples really good for this see http://allenbrowne.com/subquery-01.html In the attached database I wrote the query Prior Visits just by substituting your problem into the "Get the value from another record example"

I also included a query, "Day Between Prior Visits" which does the math. Excuse that name. It doesn't really fit.

This query doesn't handle the case where there was no prior visit. You can deal with that with the iff function. If you have problems let me know.

Note: I used the first database you uploaded.

this is what's exactly i want. but this work with a Few of the records. when i work with 1300 records in tblOrders this give me: an error: At most one record can be returned by this subquery. (Error 3354) A subquery of this kind cannot return more than one record. Revise the SELECT statement of the subquery to request only one record.

 

[sneuberg]

If the subquery has TOP 1 in it I don't understand how it could be returning more than one record. Could you upload the database. Maybe I can figure out what's wrong.

 

[Falcon88]

Once you get your DMax working, then you can put your DateDiff around it to calculate the difference in days. Did your DMax return the correct date for each record?

How can I use this function, please

 

[Falcon88]

If the subquery has TOP 1 in it I don't understand how it could be returning more than one record. Could you upload the database. Maybe I can figure out what's wrong.

i think that because some emploees having more than one Order in the same date.

 

[sneuberg]

If you look at my Days Between Prior Visits you will see I use it there as:

Expr1: DateDiff("d",[Prior Vists]![PriorVist],[Prior Vists]![OrderDate])

So what you want would be similar except the DMax would take the place of [Prior Vists]![PriorVist] in this example

 

[sneuberg]

i think that because some emploees having more than one Order in the same date.

That's it. If fact Allen Brown discusses this problem in that link I put in my previous post. The fix is to include the primary key in the sort as I have down in the query below. The sort order had Dupe.EmpName before.

SELECT tblOrders.EmpName, tblOrders.OrderID, tblOrders.OrderDate, (SELECT     TOP 1 Dupe.OrderDate                 
FROM tblOrders AS Dupe                     
WHERE Dupe.EmpName = tblOrders.EmpName 
AND Dupe.OrderDate < tblOrders.OrderDate   
ORDER BY Dupe.OrderDate DESC, Dupe.OrderID) AS PriorVist
FROM tblOrders;

So if the DMax method doesn't work you have this to fall back on.

 

[plog]

Post #14:

How can I use this function, please

We've been moving towards that. Does your DMax work yet?

 

[gemma-the-husky]

I would not use dmax

I would extract the visits for a given employee, sorted in descending date order. (you only need the top2, but what if there is only 1, or even none?)

I would then iterate a record set

 open recordset
 lastdate = recordset!dateofvisit
 rst.movenext
 previousdate = recordset!dateofvisit
 close recordset

now you can compare lastdate and previousdate

 

[Falcon88]

Post #14:

We've been moving towards that. Does your DMax work yet?

No i can not know how to use it ?