Date Difference Calculation

[brahmabull525]

I have a table in Access that has two fields, one called "ID" and the other called "SaleDate". The table (small sample) looks like this:

ID SaleDate
1 12/12/2004
1 12/15/2004
1 12/22/2004
1 12/31/2004
2 12/1/2004
2 12/9/2004
2 12/31/2004

Using this, I'd like to create a make table query that calculates a difference between rows, by ID. Based on the above, the resulting table would be:

ID Diff
1 3
1 7
1 9
2 8
2 22

I have no idea how this can be accomplished as described! Any and all help would be most appreciated.

Thanks!

 

[r937]

select t2.ID
     , datediff("d",t1.SaleDate,t2.SaleDate) 
                    as Diff
  from yourtable as t1
inner
  join yourtable as t2
    on t1.ID = t2.ID
   and t1.SaleDate =
       ( select max(SaleDate)
           from yourtable
          where ID = t2.ID
            and SaleDate < t2.SaleDate )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[PHV]

Another way (typed, untested):
SELECT A.ID, A.SaleDate-Max(B.SaleDate) AS Diff
FROM tblSaleDate A INNER JOIN tblSaleDate B
ON A.ID=B.ID AND B.SaleDate<A.SaleDate
GROUP BY A.ID, A.SaleDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[brahmabull525]

PHV,

Thanks for the post! I used what you provided and I can see the results as a select query. How can I turn this into a table? Based on the way it's written Access won't allow me to go into Design View, and it won't allow me to select "Make Table Query" from the toolbar "Query" dropdown. Is there something that can be added to the end of it in SQL View so that a table can be generated?

By the way...you've answered a lot of my questions on this forum over the past month or so. I have really appreciated your help. I hope someone is paying you big $$$ for your talents, because you know what you're doing!!!!


Thanks!!!

 

[brahmabull525]

r937, thanks to you also for the response. I tried yours but it wasn't working so I tried PHV's.

Appreciate your help!