Query Field to change Value

[bluke829]

I have a table that lists employee name, date, Absent Type(AllDay,Late or LeaveEarly and Value (1 for absent and .5 for Late or LeaveEarly). If The employee is listed as absent in this table the point value is 1.

Here is my question: if the employee is absent for two consecutive dates (e.g. 1/30/17 and 1/31/17) instead of 1 point for each day, these 2 records need to be changed to .5 for each day.

How do I go about writing an expression in the query to do this? I'm at a loss and struggling to figure this out.

Thanks in advance for the help

 

[Ranman256]

now youre making your own rules. (which dont quite make sense)
absent 1 day = 1 pt.
absent 2 days = .5 pts per day.

this can only be done by hand,
or by making specific vb code.

 

[sneuberg]

You want to change a value in one record based on a value in another. This might be able to be done by used of a subquery or DLookup within an Update query but it would probaby be easier just to loop through a recordset in VBA and update the records.

If a VBA program or update query just did this blindly it would update the first two of a sequence of three days of being absent. Does this make any difference to you?