how to calculated elapse time between 2 orders

Question

I have the following table :

     tCustomerOrder
     -IdOrder
     -IdCustomer
     -OrderDate

I want a query to get a table with :

     -Idcustomer
     -LastOrder_date
     -LastOrder_Id
     -LastOrder_period   ''ie elasped time between the two last orders
     -NextOrder_Date     ''estimated date egal to LastOrder_date + LastOrder_period

I success to have LastOrder_Date (using MaxDom function to calculate a new fied) but when I use DiffDate it return en error.

     LastOrder_Date: MaxDom("OrderDate";""tCustomerOrder"";"[IdCustomer]=" [IdCustomer] & " AND [IdOrder]<>" & [IdOrder] & " AND  [OrderDate] < " & [OrderDate])

     DiffDate("\d";[tCustomerOrder];[LastOrderDate])

Must I have to use aggregate query? How do it work? Thanks a lot for your support.

Is your LastOrder_Date returning something ? Sometimes `Null`, perhaps ? — iDevlop, Nov 22 '18 at 11:29

score 0 · Answer 1 · answered Nov 22 '18 at 11:14

0

Use DateDiff function instead of DiffDate

answered Nov 22 '18 at 11:14

Sergey S.

6,296
1
14
29

OP seems to use a French MS-Access – iDevlop Nov 22 '18 at 11:27
Of course I use a French MS-Access. I correct the expression as follow DiffDate("j";[LastOrder_Date];[OrderDate]) and it works. – Elen Glimois Nov 22 '18 at 14:09

score 0 · Answer 2 · answered Nov 22 '18 at 11:34

0

You calculate LastOrder_Date, but then you try to use [LastOrderDate]
(missing _ in name)

answered Nov 22 '18 at 11:34

iDevlop

24,841
11
90
149

score 0 · Accepted Answer · answered Nov 22 '18 at 18:35

I found the answer thanks to MS Access SQL: Aggregating on Min Value but retrieving other fields

Here is the solution :

SELECT a.IdClient, a.LivraisonDateMax, m.IdCommande, 
m.NextLivraisonDelay, m.NextLivraisonDate
FROM 
(
   (
    SELECT IdClient, 
    Max(LivraisonDate) AS LivraisonDateMax 
    FROM qryBILANCommandeClient_All GROUP BY IdClient)  
    AS a INNER JOIN qryBILANCommandeClient_All AS m 
    ON (a.LivraisonDateMax = m.LivraisonDate) AND (a.IdClient = m.IdClient
    )
) 
INNER JOIN tClient_General ON a.IdClient = tClient_General.IdClient
ORDER BY tClient_General.DesignationCourte;

how to calculated elapse time between 2 orders

3 Answers3