Carry Data to a new record relating to specific ID (1 Viewer)

Hi everyone,

I'm quite new to access so please be patient with me. I'm currently making an annual leave database for the company I work for. The fields I am currently concerned with are..

- ID
- Employee ID
- NumbOfDaysCarried
- NumbOfDaysRequested
- NumbOfDaysRemaining

The NumbOfDaysRemaining shows the value of [NumbOfDaysCarried]-[NumbOfDaysRequested]

What I'm trying to do is carry the NumOfDaysRemaining to NumbOfDaysCarried in a new record BUT only linked to a specific Employee ID. Therefore, the NumberofDaysCarried will gradually reduce according to the more NumberofDaysRequested.

In other words imagine the Employee ID is 10. After inputting holiday requests for numerous other clients, I wish to add a holiday request for Employee 10. What I want is the NumbofRemaining from the last holiday request from client 10 to carry over to the NumbOfDaysCarried to next request by Employee 10.

I really hope this makes sense. If anybody could help, I'd be really grateful.

Hi. Welcome to the forum! Can't say if this is the proper design structure, but you should be able to use DLookup() to look up the last days remaining for the same ID and then put it in the days carried field.

Classic stock control model except in this case rather than items in a warehouse, it is days of leave.


You don't store the days remaining in each record. Store the number of days that each employee starts with, add entries to a tblLeaveDays with a minus entry when leave is taken, add a positive entry when more leave accrues. The days remaining is always the running sum which can be calculated and displayed on any new record.


Instead of storing positive/negative day amounts, you could have a field indicating the type of transaction eg LeaveTaken or LeaveAccrued.

Thank you both for your replies. I shall look into it.

The reason @Cronk's idea is the one to use is the issue you have if someone cancels a holiday.


If an employee has booked 4 separate breaks, and then cancels or amends the first one, you need to amend the carry forward days on all 4 records, which is a pain, and easy to get wrong.


If you just work it out each time
then the total days booked is always accurate.

In passing, note that the days booked count, could be obtained from the start/end day of the holiday, but in practice, it might be useful to store all 3 - start date, end date and days booked. You might need to consider half days as well.