Automated adding of column (1 Viewer)

[Tkandy]

I have inherited a database at work for the accrual of annual leave at the moment they input it manually, but would like this to auto incrament every month on our financial month.

these are the fields
[First Name] [Surname] [Department] [Position] [Start date Company] [Start Date] [Salary] [LB start Date] [Leave Balance] [Toil Balance] [Sick Leave] [LS Leave]

Your help would be much appreciated

 

[vbaInet]

Auto increment in what context?

 

[Galaxiom]

I believe you are asking a question which could turn this into a bad database if not done right. I expect the system is already being used incorrectly.

Currently it appears that they are storing [LB start date] which I expect would stand for [Leave Balance Start Date]. The current leave entitlement displayed on the Employee form should be calculated in the Record Source by subtracting [LB StartDate] from the current date then deriving the total leave entiltment for this period, then subtracting the sum of leave taken from a table of Leave records.

This does not require updating monthly. Maybe yearly at most. I expect there will already be a query or VBA procedure in the database to update both the Balance and [LB start date].

Look for that query or create it if necessary. If necessary use V-Tools to search all references in the database to [LB start date].

Similar with the Sick Leave and LS Leave.

 

[Tkandy]

[Start date Company] [Start Date] [Salary] [LB start Date] [Leave Balance] [Toil Balance] [Sick Leave] [LS Leave]

You may start at this company [Start Date Company] but you may not be salary staff till a later date, hence having the [Start Date] because that is when you start accruing leave. But your long servie leave starts from when you started [LB Start date] is a number of how much leave they started with when they started this DB. sick leave 12 (months) / 6 Days of accrual

Long service leave [LS Leave] is accrued on 10 - 15 and 20 years. which is a 5 days holiday added (this is not every year just a 1 off bonus)

Annual leave is accrued on a monthly basis and entitlement is 4 weeks a year

I am sure there is a better way of doing this. (probably starting from scratch) I am only a beginner at this so if there is any suggestions

 

[Tkandy]

From your start date at salary

increment on a monthly basis
Annual Leave and Sick leave

Then from your actual start date with the company of 10 / 15 and 20 years you get a bonus one off entitlement of a week holiday

 

[Galaxiom]

You need to forget about ever incrementing the figure at all. The leave entitlements can be calculated from the information you have about the employee.

Although it is a little more elaborate than I initially realised the same principle applies. A well designed system does not rely on any incrementing procedure being run to keep the displayed data accurate.

For example, every time it needs to be displayed, the total sick leave entilelment would be calculated by subtracting the employment start date from today's date, obtaining the integer number of full months, multiplying that by the days given per month. Then sum the days taken from the records of sick leave taken and subtract this from that total to provide the figure of the net entitlement. No increment is ever neeeded.

Long service is similar. Subtract the employment start date from today and calculate the gross entiltlement based on years of service.

This is a normalization issue. Incrementing the stored figure for entitlement can result in a conflict between the what the entitlement should be when calcualted versus the figure stored value. Imagine what could happen if the increment routine was inadvertently run twice.

I hope this makes sense. It is a central principle of database design and extremely important to understand.

It is not dissimlar to the principle of calculating inventory quantities on hand by recording an opening quantity, adding the purchases and subtracting the sales rather than adjusting a Stock Quantity field at every transaction.