Calcuate date difference when two dates are same

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

Satyam wrote:
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?

--
Marsh
MVP [MS Access]

"Marshall Barton" wrote:

Satyam wrote:
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?

--
Marsh
MVP [MS Access]

Marsh,
I do have a field (Column AA) that distinguishes one Xpto 18-Mar from
another Xpto 18 March.
I am putting up the modified table below.

Col- AA Col-A Col-B Col-C
X1 Xpto 15-Mar 1
X2 Xpto 18-Mar 3
X3 Xpto 18-Mar 0
X4 Xpto 14-Mar
A1 Abc 19-Mar
A2 Abc 21-Mar 2
A3 Abc 23- Mar 2
D1 Dfd 23-Mar
T1 Ttr 24-Abr
T2 Ttr 27-Abr 3

I hope this helps you in suggesting a solution to my problem

Thanks