Another Q about date intervals

[glorber]

I have a table similar to the following:

ID Date Biopsy result
111 x ddd
111 y hhh
111 z fff
222 g ggg
222 h llll
333 d jjj
333 l eee
333 s aaa

I would like to calculate, for each ID, the date interval between biopsy dates
Is there a way of doing this?
Thanks.

 

[raskew]

Hi -

"Date", a reserved word which shouldn't be used as a field name. (Try "MyDate" or something similar). Shown as x, y, z etc.. How does that work?

Bob

 

[gemma-the-husky]

i think to do this you would have to use a recordset. you need a query sorted by caseid, and date, then you would have to iterate the recordset to find each instance of the record, to evealuate the dates.

although it introduces redundancy, you could store the gap in the current record when you save it, although you need to be aware of the issues if you insert an extra consultance in the middle of a table, or if you delete a record.

you oculd also dynamically read the previous date when you need it -something like

previous date
= dmax("consultancydate","consultancytable","[caseid] = " & whatever & " and [consultancydate] < #" & latestconsultancydate & "#")

i hope these ideas are useful

 

[raskew]

Hi -

Personally think that specifying dates as x, y, z is a tad too abstract. Check here to see how Access stores dates/times http://support.microsoft.com/kb/q130514/.

Bob

 

[pdx_man]

Raskew, uhh ... never mind.

Gemma is on top of it.

SELECT 
   Glorber.MyId, 
   Glorber.MyDate, 
   Glorber.Biopsy, 
   DateDiff('d',DMax("mydate","Glorber","MyID = " & [MyID] & " 
     AND MyDate < #" & [MyDate] & "#"),[mydate]) AS DaysSince
FROM Glorber;

 

[glorber]

First off, thanks for your assistance (pdx_man and Gemma).
pdx man, I pasted your code into a new query, renamed the relevant fields, and yet I get an "#Error" result in the 'days since' field ?