Calculations

  • Thread starter Thread starter BethS
  • Start date Start date
B

BethS

I have a form with employee info. I have been asked to
track vacation days used. Granted vacation days has been
added to the employee table.I have added to the form: EX:
Granted Vacation Days: 20
Used:
Balance: ??
I have also added a subform for the data entry to key the
date used and number used. Ex: DATE: January 10, 2004
USED: 1
Need for the "used" in the form to pick up the amt "used"
in the subform and then substract for balance.
This will be first time using calculations. Not familiar
with VB. Best way to calculate? Thanks everyone for
listening and your time.
 
Beth,

Given your present structure, you can do this...
1. In the Footer section of the subform, put an unbound textbox,
possibly invisible, with this as its controlsource:
=Sum([Used])
(assumes 'Used' is the name of your field, change accordingly)
2. Name this textbox 'TotalUsed'
3. In the controlsource of the Used textbox (which I am assuming is
named DaysUsed) on the main form, put the equivalent of:
=[NameOfYourSubformControl]![TotalUsed]
4. In the controlsource of the Balance texbox on the main form, put
the equivalent of:
=[GrantedVacationDays]-[DaysUsed]

Having said this, there is probably a significant design change which
you could make, which would make management of this data simpler.
Remove the Granted Vacation Days field from the Employee table. This
should be in the other table, with the days used, as it a transaction
that relates to the employee's vacation days "account". Just add
another field, VacationType, which has the options of Granted or Used.
So, when additional vacation days are granted (?annually) or whatever,
you enter it with date and number of days. And the Granted days get
added and the Used days get subtracted when you do your queries.

- Steve Schapel, Microsoft Access MVP
 
Back
Top