How to calculate a balance in form with subform

[yanie]

Hi...can someone help me..currently im doing database for my office,its call leave database..i having a problems with the calculation..i dont know how to write a script..

Lets me explain more about the problems..

[FONT=&quot]Every staff have their own entitlement leave for this year and carry forward balance from last year..i need to calculate a balance when staff took their leave.. but what i realy need is...the balance will reduce only when i select a certain leave type such as CR01,CR07..other than that it [/FONT][FONT=&quot]doesn’t effect the balance.[/FONT][FONT=&quot].how to i do the calculation in form with subform.

first i need to sum entitlement leave for this year and carry forward balance from last year then minus leave totals days.(please refer to my attached database and review my frm)

how do i write the script..please help me[/FONT]

 

[Galaxiom]

You have some normalisation issues.
Table [Personal Leave Data] should not have a [Current Balance] field at all as this would be derived from the [Carry Forward] field and the information in table [LeaveForm].

Likewise the [leave day taken] field. This can be calculated from the start date and end date. And once again no balance field here.
All balances should be calculated as required on the forms.

Designation, Department and Leave Type tables should have a Primary key and this key should be the stored value everywhere else in the database.

Sort out the normalisation and we can then look at the calculations.

Also shorten your table and field names and leave out the spaces. The labels in the forms can be edited to the longer name but long names with spaces make queries very messy.

 

[yanie]

Hi,GalaxiomAtHome..

Here i attach a database after i do some ammendment as your suggestion..Could you help to calculate a balance,,pls

 

[Galaxiom]

I have done some normalisation and adjusted the relationships. This will at least mean you are working on a properly normalised database.

However I would probably combine the LeaveData into the StaffDetails as they are a 1:1 relationship. Of course you would have to adjust the queries accordingly. Not essential.

Note how the LeaveData and StaffDetails tables now store numbers instead of text in several fields. This cuts storage size and speeds up the processing.

Check out the changes in the combo boxes Row Source, Column Count and Column Widths that deal with this change. They record numbers but display text.

I changed Active to a checkbox.

I have reduced the primary key to two fields in the LeaveForm table to Staff_no and startdate. The way you had it with four fields would have allowed the same staff to have leave of different types starting on the same day providing potential for duplication.

The relationships were dangerous. The one to many with cascaded deletes would have caused leave records to be removed if a type was deleted from the LeaveType table. Better it stays there with an invalid ID than being deleted.

I have only demonstrated the calculation for one type of leave but you should get the idea.

Notice how balances are not stored anywhere but calculated by summing the leave in the LeaveForm table and subtracting from the CarryForward and Entitlement. See how the Balance field is calculated in its Control Source.

Study the LeaveDecrement query. Note how it only uses data in the current year and only the types designated by the Decrement field in the LeaveType table. At the end of the year you would recalculate the carryforward figure.

This is sloppy work but it will give you an idea of the kinds of ways to do it. If it was my project I would be using a table to record the rollover dates rather than relying on the year. The technique I used could break if the rollover is not done on December 31.

I would strongly advise you to stop using the caption property in the tables as this will lead to confusion. Much better you see the true name of tables. IMO, they are a much bigger problem than table level value lookups.

I have roughly normalised the LeaveForm. Only the start date and number of days are recorded. The end date is calculated not stored. However I did not deal with the weekends and other holidays so it isn't right for use.

You can either fix this properly or change it back to how it was. I did it mainly to demonstrate the normalisation and show the kinds of things that are done to the control source to make it calculated.

I expect there are a number of threads on the site to show how to deal with weekends and holidays but post for advice in a new thread after having a look at them if you need more help and want to go this way. Or just change it back and ignore the normalisation issue..

The spin button on the staff name did not work on my computer. I would avoid these ActiveX controls as they don't work everywhere. Better use a next record button or include a selection combo or search button.

This should give you a lot to take in.