Calcuate date difference when two dates are same

[Guest]

Hi,
I want to calculate (date) difference of column B based on grouping in
column A. Problem is similar to one of the threads on the discussion board
except the fact that for a value in colum A there can be same value in
column B. That is, the desired output should be as shown in column C.

Col-A Col-B Col-C
Xpto 15-Mar 1
Xpto 18-Mar 3
Xpto 18-Mar 0
Xpto 14-Mar
Abc 19-Mar
Abc 21-Mar 2
Abc 23- Mar 2
Dfd 23-Mar
Ttr 24-Abr
Ttr 27-Abr 3

I tried using

SELECT ColA, ColB, ColC
, DMAX("ColB","YourTableOrQuery","ColB<#" & ColB & "# AND ColA = """ & ColA
& """") as PriorDate
, DateDiff("d",ColB,DMAX("ColB","YourTableOrQuery","ColB<#" & ColB & "# AND
ColA = """ & ColA & """") )
FROM YourTableOrQuery

but the output I get is as shown in Column D instead of as desired in Col-C

Col-A Col-B Col-C Col-D
Xpto 15-Mar 1 1
Xpto 18-Mar 3 3
Xpto 18-Mar 0 3
Xpto 14-Mar
Abc 19-Mar
Abc 21-Mar 2 2
Abc 23- Mar 2 2
Dfd 23-Mar
Ttr 24-Abr
Ttr 27-Abr 3 2

I would really appreciate any help as I have been stuck this for long.

Thanks,
Satyam

 

[Marshall Barton]

I want to calculate (date) difference of column B based on grouping in
column A. Problem is similar to one of the threads on the discussion board
except the fact that for a value in colum A there can be same value in
column B. That is, the desired output should be as shown in column C.

Col-A Col-B Col-C
Xpto 15-Mar 1
Xpto 18-Mar 3
Xpto 18-Mar 0
Xpto 14-Mar
Abc 19-Mar
Abc 21-Mar 2
Abc 23- Mar 2
Dfd 23-Mar
Ttr 24-Abr
Ttr 27-Abr 3

I tried using

SELECT ColA, ColB, ColC
, DMAX("ColB","YourTableOrQuery","ColB<#" & ColB & "# AND ColA = """ & ColA
& """") as PriorDate
, DateDiff("d",ColB,DMAX("ColB","YourTableOrQuery","ColB<#" & ColB & "# AND
ColA = """ & ColA & """") )
FROM YourTableOrQuery

but the output I get is as shown in Column D instead of as desired in Col-C

Col-A Col-B Col-C Col-D
Xpto 15-Mar 1 1
Xpto 18-Mar 3 3
Xpto 18-Mar 0 3
Xpto 14-Mar
Abc 19-Mar
Abc 21-Mar 2 2
Abc 23- Mar 2 2
Dfd 23-Mar
Ttr 24-Abr
Ttr 27-Abr 3 2

Insufficient data!

To do that you **must** have a unique sort order for the
records. Do you have some other field in the table that can
be used to distinguish one Xpto 18-Mar record from another
Xpto 18-Mar record?