Query value from two records ??

[Mr.Access]

Hi,

I have a query that contanis the (Employees Work Rotations) of 4 columns ;
------------------------------------------------------|
In_Work | Out_Work | Work_Days | Off_Days
------------------------------------------------------|
01/01/2008 | 15/01/2008 | 15 | 4
20/01/2008 ............................................
....................................................................

------------------------------------------------------|

You can see that the Off_Days = 4 since the employee came back to work after 4 days off ....

I've got the Work_Days values by the DateDiff() function but ....
the question is , How can I generate the "Off_Days" values ?

Many thanks in advance .

 

[namliam]

Your problem is to get the InWork to the previous line...

Something like this:
SELECT Table15.StartDate, Table15.EndDate, Min(Table15_1.StartDate) AS NextStartDate
FROM Table15, Table15 AS Table15_1
WHERE (((Table15_1.StartDate)>[table15].[StartDate]))
GROUP BY Table15.StartDate, Table15.EndDate;

Should do that trick for you, you can then use datediff to calculate the days...

Good Luck

 

[Mr.Access]

It works well .

What a nice trick !

Mailman .. you are the man .

Many thanks.

 

[namliam]

One warning tho, it is kindoff a dirty round about trick... If your dataset is going to get big, this query is going to get slower and slower.