Scheduler?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone please explain to me if the following are possible or not.

1) A query can be run to insert records into a table on a certain date

2) Insert records for all employees whose anniversity falls within that month.

3) Prevent from doing these things more than once within a month.
 
Rather than insert records for an employee who has an anniversary in a
particular month, why not just build a query to pull out all the employees
with anniversaries?

Anytime we see people asking how to move or copy records from one table to
another, it throws up a flag that they are trying to do something they
shouldn't.

A query that pulls all the employees with anniversaries this month can be
used as the data source for forms, reports, and even other queries. It is
almost never necessary to make a new table out of them.
 
Thanks Rick,

But i don't understand why this is so hard to understand. My question is
about timing. I want to time my events. I guess most users are novices and
you guys try to really understand what others are trying to do to help them
avoid pit falls.

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!
 
Jacob:

You are correct in your assumption that we often try to advise novices
against hurting themselves.

I have a database similar to what you mention but it is simply to track my
personal time off and that of a few team members. I just do it manually
each year because our's is on a calendar year.

I would think that your scenario would make sense. Unfortunately, I am not
sure exactly how you'd do it. Hopefully one of the folks more experienced
will jump in. With your detailed information below, I'm sure someone will
come up with something.

Good luck,
 
IF (BIG IF) I were going to do something like this, I would use a table to
record the next time the event was to be executed and then check the table.
If an event is to be executed, I would update the table with the next date
and execute the query.

I would do this all with VBA and use a transaction to do it so that I could
roll it all back if any part of it failed. Then I would have a procedure in
place that checked the Event table when the database was launched. If an
event had passed its rundate, then I would execute the procedure.

I'm not sure how the database is actually set up. For instance, do these
times get decremented when someone takes vacation or sick time or is that
recorded elsewhere.

Your proposed method is (in my opinion) an accident waiting to happen.
Given an employee's hire date you should be able to always calculate the
exact amount of time they have accrued over time for any specified date and
subtract any time they have taken up to that date.

Anyway, good luck on this.
 
Back
Top