calculate days between records and between two columns in the gro

  • Thread starter Thread starter IP
  • Start date Start date
I

IP

I need to calculate days between Discharge Date and next Admit Date in the
group of records. For example,

FirstName LastName ADate DDate Cycle

John Lee 5/10/2009 5/20/2009
John Lee 5/30/2009 5/31/2009 10
John Lee 6/10/2009 6/15/2009 10
Ann Sim 8/11/2009 8/15/2009
Ann Sim 9/01/2009 9/02/2009 16

There must be a way of doing this. I would greatly appreciate any help.
 
Usually, you use Datediff to calculate. Like this:
Datediff("d",[firstdate],[seconddate])
The "d" is for days.
 
It would work if I need date diff in the same record. But I need ADate in one
record, but DDate in the previous, and in the group.

Thank you

golfinray said:
Usually, you use Datediff to calculate. Like this:
Datediff("d",[firstdate],[seconddate])
The "d" is for days.
--
Milton Purdy
ACCESS
State of Arkansas


IP said:
I need to calculate days between Discharge Date and next Admit Date in the
group of records. For example,

FirstName LastName ADate DDate Cycle

John Lee 5/10/2009 5/20/2009
John Lee 5/30/2009 5/31/2009 10
John Lee 6/10/2009 6/15/2009 10
Ann Sim 8/11/2009 8/15/2009
Ann Sim 9/01/2009 9/02/2009 16

There must be a way of doing this. I would greatly appreciate any help.
Click to expand...
Click to expand...
 
I will refraise this . I want to calculate days between two columns , but
between current and previous records. Is it possible to do?
Thank you very much in advance.
 
No. Not without a lot of steps. I think it would probably be easier then to
maybe just have 2 tables, one that includes each date, then assign a record
number as a primary key then join the two on the key and query. Trying to
query from a different record will be difficult.
 
Do you have a primary key that identifies the person? If so life will be
easier. If not, then

SELECT FirstName, LastName, ADate, DDate
, (Select Max(DDate)
FROM SomeTable as Temp
WHERE Temp.FirstName = SomeTable.FirstName
AND Temp.LastName = SomeTable.LastName
AND Temp.DDate < SomeTable.ADate) as PriorDischarge
, ADate -
(Select Max(DDate)
FROM SomeTable as Temp
WHERE Temp.FirstName = SomeTable.FirstName
AND Temp.LastName = SomeTable.LastName
AND Temp.DDate < SomeTable.ADate) as Cycle
FROM SomeTable


If you don't want to see the prior Discharge date then remove the subquery for
PriorDischarge from the SELECT clause of the query.



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you so much for looking into it. I will try this way. I think it must
work. But if you know the way to do it in the query or in VBA, I would love
to know it.

Thank you again
 
Thank you so much ! It is exactly what I need.
What a greate thing these discussions are!
 
Back
Top