E
Ed Ardzinski
Imagine a table with a person ID, a Start Date and an End Date. The person
can be in multiple times, such as a date range that the person was "happy"...
My challenge is to be able to calculate the number of days between "happy"
episodes...that is the [Start Date] from the second record less the [End
Date] from the first record. I came up with a sub-query idea that does the
trick...
Except that it gives me an error that the sub-query can only return 1
record. I'm thinking that the issue might have been the cases where there
were no more records to return, but I'm not that sure. I tried to count the
potential records that might be valid to compare with but no dice. Tried to
make a table, but got the same issue.
And a trial database seems not to have the same issue, but that could be
becuase of the size of the recordsets...
Here's the details...The table has an ID (autonumber), Name (Text), Start
(Date), End (Date). The query that *seems* to work in the trial database is:
SELECT Y.Name, Y.Start, Y.End, (Select Top 1 [X].[Start] from tblData as X
Where [X].[Name] = [Y].[Name] and [X].[Start] >= [Y].[End] ORDER BY
[X].[Start]) AS NextStart, [NextStart]-[End] AS [Effect Days]
FROM tblData AS Y
ORDER BY Y.Name, Y.Start;
Any suggestions would be appreciated.
can be in multiple times, such as a date range that the person was "happy"...
My challenge is to be able to calculate the number of days between "happy"
episodes...that is the [Start Date] from the second record less the [End
Date] from the first record. I came up with a sub-query idea that does the
trick...
Except that it gives me an error that the sub-query can only return 1
record. I'm thinking that the issue might have been the cases where there
were no more records to return, but I'm not that sure. I tried to count the
potential records that might be valid to compare with but no dice. Tried to
make a table, but got the same issue.
And a trial database seems not to have the same issue, but that could be
becuase of the size of the recordsets...
Here's the details...The table has an ID (autonumber), Name (Text), Start
(Date), End (Date). The query that *seems* to work in the trial database is:
SELECT Y.Name, Y.Start, Y.End, (Select Top 1 [X].[Start] from tblData as X
Where [X].[Name] = [Y].[Name] and [X].[Start] >= [Y].[End] ORDER BY
[X].[Start]) AS NextStart, [NextStart]-[End] AS [Effect Days]
FROM tblData AS Y
ORDER BY Y.Name, Y.Start;
Any suggestions would be appreciated.