Which ever you choose, the starting point is one or a
number of update queries which check the dates and
append to or update the VacationTime and SickTime Tables.
Then you need to decide how and when you want to trigger
the script, and where you want to store it.
Access/Jet/DAO/ADO has a thing called a 'stored procedure',
but it can only have one SQL command in it. If you work on
your SQL, you should be able to put all of this into one
update query, and you would be able to run this from a
scheduler or script.
But you wouldn't really need to put everything into one update
query, because your scheduler would be set to run a script,
and your script could contain a number of actions, just like
a SQL Server stored procedure would. It just means that your
logic is split between the script and the SQL, instead of
all in one place, in a TSQL script.
Alternatively, Access (the user interface program, not
jet, not ado, not dao, not the database engine) has
macro's, so you can script a number of actions, and
VBA, which works like VB script and allows you to
script a number of actions.
So you could store your script in Access, just like
you would store your TSQL script in SQL Server.
The disadvantage of storing the script in Access is that
you would have to start Access - the massive office user
interface program - just to run your stored procedure.
Of course, you might decide to run the script when
you start Access anyway, instead of using a scheduler.
If your payroll people use the payroll program every
day, you can put a start-up or close-down script into
the payroll program, so that it does this update.
(david)
"jacob" <(E-Mail Removed)> wrote in message
news:90BDF031-A894-44E0-8AAD-(E-Mail Removed)...
>I work with SQL Server. I would typically do this with a stored procedure,
> but since i don't know much about Access, i don't know how to do this.
> I'm
> just trying to get some clues before i get started on this thing and
> figure
> out i can't do what i need.
>
> In this small database, i have about 4 tables. Department, Employee,
> VacationTime, SickTime
>
> Currently payroll is using a manual system to calculate all this stuff and
> it is riddled with errors.
>
> 1) When our fiscal year begins, each employee is given 2 personal days of
> 8
> hours each. I want the database to add these hours for all employees
> automatically.
>
> 2) On the anniversary of his hire date, each employee accrues vacation
> time
> based on years served.
>
> - 1 to 5 years, 80 hours
> - 6 to 10 years, 120 hours
> - 11 years or more, 160 hours
>
> I would like Access to do this automatically so payroll doesn't have to
> remember each month.
>
> 3) Sick leave accrual has to be added also. Every 2 weeks our hourly
> staff
> accrues 4 hours sick leave. Salaried employees accrue 8 hours per month
> of
> sick leave.
>
> I don't think payroll should have to run a query to do this every single
> month. What if staff tries to pull up their time before payroll runs the
> query?
>
> Don't worry about simultaneous users. I'm aware of that.
>
> I'm assuming I will need some if statements and all that kind of stuff.
> I'm
> not asking anyone to do the programming. I'm simply asking what i need to
> know to get this done so i can read up on it.
>
> Thanks guys!
>
|
Similar Threads
