Annual Roll-Over

[Robbin]

Hi all,

I'm hoping that my tables are normalized properly, but I'm stumped on a
particular layout and design structure.

I hav a table with faculty, which is always on the (1) side of several other
tables, which are the (many) side. Faculty are activities tracked annually
and sometimes by semester, most of them remain the same from year to year,
though some come and go, like other employees. Each year they have a
different salary structure, each semester they have a different number of
courses to teach, and/or sit on different committees, etc.

In the past I've had to make a new teaching table record, committee, table
record, salary table record, ect. for each new year. That means that there
is a FacultyID and a YearID in each subtable. I have a year table, but it's
used to populate a combo box.

What is the best way to handle the annual updates considering that more than
half of the faculty will not change their information? My form has the
faculty table on the header, with tabs in the detail, each tab having one of
these sub tables.

This is really bogging me down.

Thank you,

 

[John W. Vinson]

Hi all,

I'm hoping that my tables are normalized properly, but I'm stumped on a
particular layout and design structure.

I hav a table with faculty, which is always on the (1) side of several other
tables, which are the (many) side. Faculty are activities tracked annually
and sometimes by semester, most of them remain the same from year to year,
though some come and go, like other employees. Each year they have a
different salary structure, each semester they have a different number of
courses to teach, and/or sit on different committees, etc.

In the past I've had to make a new teaching table record, committee, table
record, salary table record, ect. for each new year. That means that there
is a FacultyID and a YearID in each subtable. I have a year table, but it's
used to populate a combo box.

What is the best way to handle the annual updates considering that more than
half of the faculty will not change their information? My form has the
faculty table on the header, with tabs in the detail, each tab having one of
these sub tables.

Why have a YearID instead of just a year - or perhaps a pair of date fields,
EffectiveDate and EndDate? A salary table might have fields like FacultyID,
EffectiveDate, EndDate, Salary; whenever the person's salary changes you would
enter a new record (and not until then). You could have a query selecting
records with NULL EndDate to find the person's current salary.

The same could apply to the other tables - Dr. Schmidt was on the Grants
Review Committee from 2/17/2003 through 5/12/2007 when she got totally fed up
with the wretched proposals and quit <g>

John W. Vinson [MVP]

 

[Robbin]

Thank you, John. I see that I'm not that far off. I started with just a
year field, but changed it later, fearing I was guilty of not setting up my
tables right. The problem is that this database performs some complex
calculations for the annual salary pool, and then uses that to compute
individual salary annual increases. The year field actually factors into the
calculation. So I guess I'm stuck creating a new record each year. I'm
mulling over a possible macro that will do an "annual roll over" event
somehow. I just thought maybe I had missed something along the way with this
process being so laborious twice a year.

Thanks again.

 

[John W. Vinson]

Thank you, John. I see that I'm not that far off. I started with just a
year field, but changed it later, fearing I was guilty of not setting up my
tables right. The problem is that this database performs some complex
calculations for the annual salary pool, and then uses that to compute
individual salary annual increases. The year field actually factors into the
calculation. So I guess I'm stuck creating a new record each year. I'm
mulling over a possible macro that will do an "annual roll over" event
somehow. I just thought maybe I had missed something along the way with this
process being so laborious twice a year.

Thanks again.

It can certainly be made less laborious with some Append Queries (to append a
new record to each affected table changing only the year); the queries could
be launched en masse from a macro or (better) from VBA code.

Can't the year (in the calculation) be derived from the date range, or from
the current year from the computer's clock/calendar?

John W. Vinson [MVP]

 

[Robbin]

Oh, John, you're a dear. I will certainly be back for your help with that
VBA code when I get to that point!

Yes, the calculations do use the clock calendar when there is no end date
(for calculating years in rank, or years of experience, (which then factor
into the salary calcs), but they must be anchored to the salary year so that
they don't all roll forward when the clock changes. I need to do multi-year
reporting on salary trends with this. If all the dates were keyed to the
clock, they'd all roll over at once! This is certainly the most calcluation
intensive database I've ever done. It's based on a very complex set of
spreadsheets that the committee on faculty compensation has been using for
lo, these many years. But as you know, spreadsheets can't aggretate data
very well, nor can they track from year to year very easily. So we're
rolling this baby into a database that has multiple nested IIF clauses and
Switches. The most fun I've had with Access yet!

Stand by for more questions!

Oh, and you pegged Dr. Schmidt on the nose! ;-)

 

[John W. Vinson]

Oh, John, you're a dear. I will certainly be back for your help with that
VBA code when I get to that point!

Yes, the calculations do use the clock calendar when there is no end date
(for calculating years in rank, or years of experience, (which then factor
into the salary calcs), but they must be anchored to the salary year so that
they don't all roll forward when the clock changes. I need to do multi-year
reporting on salary trends with this. If all the dates were keyed to the
clock, they'd all roll over at once! This is certainly the most calcluation
intensive database I've ever done. It's based on a very complex set of
spreadsheets that the committee on faculty compensation has been using for
lo, these many years. But as you know, spreadsheets can't aggretate data
very well, nor can they track from year to year very easily. So we're
rolling this baby into a database that has multiple nested IIF clauses and
Switches. The most fun I've had with Access yet!

For certain values of "fun"... <g>

Note that DateDiff and DateAdd can calculate the time between dates, and a
future date from a past date respectively. It may be necessary to store the
year in the table but there may be alternatives.

Stand by for more questions!

I don't know if I should be intrigued or worried...!

Oh, and you pegged Dr. Schmidt on the nose! ;-)

Just so I don't get charged with assault and battery!

John W. Vinson [MVP]

 

[Robbin]

Back again! Ready for the roll-over query/macro/vb code

Here's what I'd love to be able to do. Create a query with all of the
current year's professors, the year field, and the semester field. I'd like
it to have check boxes for each professor which default to "Yes", because
most professors will roll to the next year/semester. Then I'd like to
uncheck the ones who are not coming back so they don't get a new record.

Then, I'd like the query to prompt the user for the new year and/or the new
semester, or I guess, just add one to the previous year (but that won't work
with semester).

If I just have the code for this in a very "vanilla" form, I can probably
tweak it for the three different tables that have to be rolled over. Two are
only done once per year, one will be done each semester.

Thank you so much in advance. I learn so much each and every time I post to
this forum!

Best, Robbin

 

[John W. Vinson]

Back again! Ready for the roll-over query/macro/vb code

May not be able to respond until tomorrow or Sunday but will keep the thread
alive.