How to get previous and current and next values on one line

[DawnTreader]

Hello All

I have need to know in one "line" the previous value, current value and the
"future" value.

here is my scenario. i have a "service report", these can be entered at any
time and so sometimes people can be entering a service report for a past date
that "fits" between 2 other reports. the problem is that each time the same
product is serviced and a report is entered there are running hours.

this running hour value can only increase but if someone is putting in a
report that falls between 2 that are already entered then the running hours
must not be less than the previous report or greater than the "future" report
that the entered report is trying to fit into. this makes validating entries
something of a pain but it needs to be done.

i have a query that gives me the previous hours. is there a way to modify
this to also give me the next hours on the same line? if so i can get the
code to look up the 3 values in this query. here is the sql:

SELECT DISTINCTROW subtblRunningHours.RunningHoursID,
subtblRunningHours.RHDate, subtblRunningHours.HoursAtDate,
subtblRunningHours.ServiceReportID, subtblRunningHours.BlockID,
subtblRunningHours.IssueID, (SELECT TOP 1 DsubtblRunningHours.HoursAtDate
FROM subtblRunningHours AS DsubtblRunningHours WHERE
DsubtblRunningHours.BlockID = subtblRunningHours.BlockID AND
DsubtblRunningHours.RHDate < subtblRunningHours.RHDate ORDER BY
DsubtblRunningHours.RHDate DESC, DsubtblRunningHours.RunningHoursID) AS
PriorRH, (SELECT TOP 1 DsubtblRunningHours.RHDate FROM subtblRunningHours AS
DsubtblRunningHours WHERE DsubtblRunningHours.BlockID =
subtblRunningHours.BlockID AND DsubtblRunningHours.RHDate <
subtblRunningHours.RHDate ORDER BY DsubtblRunningHours.RHDate DESC,
DsubtblRunningHours.RunningHoursID) AS PriorRHDate
FROM subtblRunningHours
GROUP BY subtblRunningHours.RunningHoursID, subtblRunningHours.RHDate,
subtblRunningHours.HoursAtDate, subtblRunningHours.ServiceReportID,
subtblRunningHours.BlockID, subtblRunningHours.IssueID,
subtblRunningHours.HoursAtDate
ORDER BY subtblRunningHours.BlockID DESC , subtblRunningHours.HoursAtDate
DESC;

any and all help is appreciated.

 

[vanderghast]

The easiest way, but maybe slow at execution time, would be to add

(SELECT TOP 1 DsubtblRunningHours.RHDate FROM subtblRunningHours AS
DsubtblRunningHours WHERE DsubtblRunningHours.BlockID =
subtblRunningHours.BlockID AND DsubtblRunningHours.RHDate >
subtblRunningHours.RHDate ORDER BY DsubtblRunningHours.RHDate ASC,
DsubtblRunningHours.RunningHoursID) AS NextRHDate


where I copied the formulation for PriorRHDate, changing the alias to
NextRHDate, and the < to a > and DESC to ASC.

Simple question, are you sure that you should not use <= instead of <, and

= instead of > ?

Seems strange to use strict inequalities, in these cases.



Vanderghast, Access MVP

 

[DawnTreader]

Hello Van

i am not sure what you mean by strict inequalities...

this is what i realized after posting for the third time on this topic. it
hit me as i was looking through my sql in the post.

thanks for the help though, i didnt think of the ASC, i was trying to figure
out how i was going to get the closest record to the one that is current.
this alone is worth the post. Thanks!