How do you manage historical changes to data in Access?

[Xixor]

Some context for the question -

I have a database setup that captures information related to grant submissions at a university. I have one table for university faculty, which captures info like the person's name, their title, what department they are assigned to, etc. I have another table that captures information related to grant applications - which faculty member applied, to what sponsor, how much money was requested, etc.

Because these two tables are linked, I can run reports based on criteria from either table, e.g. "how many grant submissions did the department of chemistry submit in total?", or "how many grant submissions did the Assistant Professors submit?" - because the grant submission table captures which faculty members submit and the faculty table captures what department they belong to, their job title, etc. All-in-all it seems pretty much exactly how Access is designed to work, and it works really well so far.

The question -

What happens when a faculty member switches departments, or gets a promotion? I could easily go in and update that info on the faculty table, but if I do that, than all the historical data in the database related to those changes becomes inaccurate, because Access will update it to whatever is currently in the table.

For example - John Doe is an Assistant Professor. John submits 5 grant applications in 2018. John is promoted to Associate Professor in 2019. The dean wants a report of what all Assistant Professors submitted in 2018. I go to run the report, but John Doe's submissions as an Assistant Professor no longer show up if I change his title when he is promoted.

The only solution I can think of at the moment is to simply add John Doe into the database a second time, with his new title. This would theoretically work, but it seems like it would get cumbersome and complicated quickly as more and more people get promoted, change departments, etc. because then you would have multiple duplicates of the same person in the database.

Any ideas or guidance is appreciated!

[Gicu]

You need a third table for the Job (position) history with JobID (autonumber, primary key), FacultyID (foreign key), StartDate, EndDate, Position, Position Notes. This would allow you to get the right reports every time. To identify the "current" positions you simply look for the entries with null End Dates

Cheers,
Vlad

[orange]

Hi xixor,

What you are describing is a typical real life situation. This sort of thing is often overlooked or "put on the back burner" during analysis and design. We often see "new to database people" make comments like --I'm doing this first, then I will do X--which is often associated with design by trial and error. Much better (more efficient) to review the "business" and create a data model that can be tested and vetted with various scenarios. The goal is to have a blueprint for the database that addresses all scenarios.

You might want to review and make a list of these issues/"anomalies". Then, in non-database terms, identify the logic of what you should do to correct/alleviate/remove each. Based on priorities, address the "fixes" from a holistic view.

Remember, people can
-change their names
-change jobs
-change titles
-have multiple phones/devices
- etc, etc.

What are the things relevant to your project? How should such things be designed/changed to not have serious effects?
These are often considered part of analysis and design where they can be redesigned/tested on paper. Often such things are discovered in production/operations where the costs to correct same are much higher.

Bottom line is to identify the current shortcomings; determine possible corrective actions and their impact; proceed based on priority.

Good luck with your project.

[Xixor]

Thanks, Vlad and Orange! Yes, I assumed it was a design shortcoming on my part. Costs are higher now to implement a fix than if I had anticipated from the beginning, but Vlad's suggestion should work for my situation. Sometimes external pressures to "get something, anything" started and implemented to at least some limited degree shrinks the analysis and design phase more than it deserves.

[CJ_London]

Remember, people can
-change their names
-change jobs
-change titles
-have multiple phones/devices
- etc, etc.

they can also have more than one job at the same time (not relevant here perhaps but a sales rep might provide temporary maternity/holiday/LTS cover for another rep). They can also leave the organisation.

Always better to manage with start/end dates rather than a flag

[orange]

I agree with costs now higher than in design phase. Also as Ajax says -multiple jobs, multiple titles, multiple bank accounts....If you can dream up a scenario, identify it and keep it or reject it from a point of knowledge. Good idea to identify and record things contemplated, kept and rejected. It might just benefit anyone charged with maintaining, adjusting this application.

[Xixor]

..........

[Xixor]

I'm having some trouble visualizing the normalizing process that Gicu mentions above. Can anyone point me to some links describing similar examples? Or alternatively, is there a key search term that applies to the situation I am describing? When I try to google or search on these forums something like "how to track historical changes to data in access", the search results all come up with descriptions of audit trails of when data changes, which while interesting is not what I am looking for... thanks!

[orange]

xixor,
The Database Planning and Design link in my signature has a few tutorials from RogersAccessLibrary. It is well worth working through 1 or 2 of those tutorials to get some experience with Normalization and design process.
There is another link with those articles re Normalization at RogersAccessLibrary.
Good luck with your project.

[Xixor]

So I have run through a few of the normalization exercises on that website and others and do generally have a better understanding of the higher-level design. I think I understand enough to know I need separate tables for department, and department history as a "linking" table with dates associated with it. What I am really running into difficulty with now is understanding how to get the data into those tables. Right now, I have a form that users use to input data into one table. Its static and unhelpful when something changes.

I still want to be able to use that form, but I can't visualize how to implement a way for the users to go into that form and update the persons department, and have that action result in the data being distributed to the "department history" linking table with dates. Do I need to add date fields to the form? If I do that, it seems I would need multiple redundant department fields with dates next to each one for the users to update. In that case, how do I get that into the department history table without duplicating data? For something like job title or salary history that might change every year, that would just not be possible to scale up in a form as you would quickly run out of space to house all the duplicate data...

If anyone has any links to actual databases that implement what I am describing, that would be extremely helpful. It's one thing (and helpful) to work through some theoretical examples to get a "high-level" understanding of where I went wrong with the design, but I really need to get down in the weeds with some examples to fully understand how all the pieces work together.

Thanks again!

[orange]

Do you have a data model or your relationships window with tables expanded so all fields are visible that you can post?
If you worked through some of the exercises at RogersAccessLibrary, did you apply what you learned to your own situation?
Do you have a list of outputs that you need(and don't yet have) from the proposed database?
Some sample scenarios to show your requirements would be helpful to readers.

I did find this free, generic data model on Tracking University Grants from Barry Williams' site. It might provide some insight into tables and relationships for consideration, but it does not deal with some of the history you mention. It has some history for Grants and ProjectStaff (note the dates).

Did you see this link that was in the Database Planning and design articles.

Good luck with your project.

[Xixor]

Okay, here is my best shot at describing the situation:

1) I have a table for faculty. This table has department as one attribute. Faculty belong to only one department at a time.
2) I have a form that users can add faculty members. The form has fields that, when updated by the user, populate the faculty table:



3) I have a table for grant submissions. It contains a lot of info on individual submissions. One of the attributes is the faculty member who submits the grant. It is linked to the faculty table primary key:


4) I have reports. One report shows submissions by department. For example department "A" had x-number of grant submissions in 2018. The report uses a query that pulls data from the submissions and faculty table.

Problem - A faculty member in department "A" submits 5 grants between January and June. Then that faculty member changes to department "B". The user goes into the form and updates the faculty member's department. Then the faculty member submit 5 grants between July and December. Right now, if I run a report from January to December, the report will show department "B" as having 10 submissions for that faculty member, but it really needs to show 5 submissions from department "A" and 5 from department "B".

My form to allow users to add a faculty member is so rudimentary that I have no idea how to implement a form and table solution that will allow the user to go in and change the faculty member's department, and have the end result in the reporting I need.

The only solution I can think of is to simply add that faculty member to the database a second time with the different department and different primary key. This as you can tell will not work in the long run because having multiples of the same person in the database for users to select will become a nightmare in short order. Also, as you have probably already noticed, changing departments is the least of my problems at this point considering there are other reporting attributes such as Position (i.e. job title/promotions) and tenure stream year that will change far more frequently than the department will.

I think part of my biggest problem at the moment, is that I am novice enough to not really have the nomenclature/vocabulary necessary to run an effective google search to find examples of what I am describing. I get that "normalizing" the database is my first priority, and I think I understand that I need separate tables for department, department history, position, position history, etc. but what I am really struggling with is understanding how to build a form that users can interact with to make changes and have those changes populate the normalized tables. I keep coming up with search results for "audit trail" or "audit history", but I don't think that is exactly what I need, and involves a level of VBA proficiency that I do not currently possess...

[ArviLaanemets]

What I am really running into difficulty with now is understanding how to get the data into those tables.

Let's have an example logic exercise:

You have tables tblDepartments (DepartmentID, DepartmentName, ...) and tblEmployees (EmployeeID, ForeName, LastName, ...).

To track to which departmant at certaim time moment every employee is assigned to, yo have a table tblDepartmentEmployees (DepartmentID, EmployeeID, ValidFrom) (You can also have StartDate and EndDate instead of ValidFrom, I myself prefer a single date. I have designed an User Defined Function, which I inclyde into any project where I need it, and it returns e.g. department certain employee is assigned at certain date.). And those 3 fields in tblDepartmentEmployees together must be defined as unique primary key.

Now you have to decide, is your app generally about departments, or about employees. This decision determines, what will be your main form. Let's assume you your main interest are employees. So you design you main form based on tblEmployees as a single form. Then you design a continuous form based on tblDepartmentEmployees, and add it as subform into main form. Main form and subform must be linked by EmployeeID (in subform's properties). Of course you need a form for Departments too, but in such design it is usually used only to register new departments, or to edit general department information (i.e. it is used on need).

When all is done properly, whenever you activate an employee record in main form. the whole department history for active employee is displayed in subform. And whenever you add a new record into subform, it will be linked to active employee in main form.

[orange]

Further to Arvi's comments which reflect the need to identify the "things" involved (eventually tables with attributes), here are a few points related to potential tables. These may or may not be accurate nor complete, but should provide sufficient info to build a first cut data model.

We are talking about a university environment where 
Faculty members occupy Positions in Departments and can change Positions and/or Departments
Some Faculty members submit applications for 1 or more Grants
There is a requirement to track Faculty positions and departments 
There is a requirement to Track the status of submissions for Grants by Faculty Member, 
Time period and Department.

The above are facts and are used to identify possible tables (and attributes) and relationships between tables.
Always better to develop a model on paper that can be tested with "scenarios" before jumping into physical database, forms and reports etc.

Good luck with your project.