Please help with design for Faculty/Commencement event

[Sleaf]

Hi there-

I'm obviously a newbie at Access, and no matter how hard I try I cant seem to wrap my head around how to design this database.

I have about 147 faculty members, of which 50% are required to come one year (ie even years like 2014, 2016) and then we alternate for the odd years. While we have a relatively stable set of employees we do get small amounts of those who retire and new members. Faculty can switch their "track" but must find another faculty member to switch with.

They also submit (each year) robe rentals (of which we are trying to keep track of).

Anywho I'm not sure of how many tables to set up... One for Faculty (name, and track); one for robes; but how do I handle the alternating tracks? Do I do a separate table for "Tracks" with a column for even years and another for odd?

I need an easy way to be able to import new faculty members (hopefully which new will automatically be assigned to a track), be able to modify which track they are on, and be able to quickly and easily pull up a report or list (or query?) of which facility members are required to attend for that upcoming year, and then an overall viewpoint of their attendance.

Thank you for advance for your help!
":

[Micron]

For sure a Faculty table with fields pertinent to the member. For track, I presume it is odd or even years only, so there's no need to worry about storing what year they'd attend. It would require unnecessary upkeep. That could be a yes/no field like EvenYear. I'd consider comparing the result of using the Mod function against the year in question and if it evaluates to 0 it is an even year so your query returns Yes members, otherwise it's an odd year so you return No members. If a member swaps, you update each to Yes/No accordingly.

tblRobeRental at minimum would need Member_FK (containing auto number or other type of unique member ID from tblFaculty). DateOut and DateIn could serve to track robes not returned (DateIn field would be Null).