Table structure to track attributes changing over time (1 Viewer)

[okanagan]

I have a list of employees. For each employee, I want to track attributes that change over time. Let's say there are 2 attributes, "level" and "stage". Level and stage vary independently. I could maintain a table for each employee, like this:

Date Level Stage
2018-06-01 A 1
2019-01-15 A 2
2020-05-05 B 1
etc.

But I'm sure that's not the way to do it, since I wouldn't want to have to add a new table for every new employee.
Can someone please point me in the right direction?

 

[mike60smart]

Hi

You need a table that contains just the Employee information with a Primary Key of EmployeeID

You then need a table that contains the details of all the attributes that change over time.

You would have a Primary Key in this table together with a field called EmployeeID which would be the Foreign Key linked to EmployeeID in the Employees Table.

Set the Forms up as Main Form based on Employees with a Subform based on Employees Attributes

 

[okanagan]

Ok thanks but I see I wasn't clear. I need to keep track what dates each attribute value was in effect for each employee, i.e. retain the date history of the value changes.

I'm hoping to be able to specify a date in a query and see what an employee's values were on that date.

 

[okanagan]

Sorry, I think I understand your answer better now and see how it will work!

 

[mike60smart]

Can you upload a zipped copy of the database?

 

[The_Doc_Man]

The best way to do this always depends on how you want to use it. Where things change over time and you want to pick a time to see what value was in force, I would use two dates - a start date and a stop date. Then I would search via something similar to

SELECT TheValue FROM ValueHistory WHERE DesiredDate BETWEEN ValueStart AND ValueEnd ;

Note that my answer would be totally different if you were asking different questions. My point is that if you have the choice, always structure your data to be consistent with how you want to use it.

 

[okanagan]

Can you upload a zipped copy of the database?

Ok, thanks for asking, here's what I'm working on. It's only a small piece of what this project will become but I am building it in pieces, as I need to learn how to do just about everything. This part is to track how various attributes of employees, change over time (dates). Some questions:

• I've made a table for each of the attributes, containing a list of valid values. I'm not sure whether this is overkill but I want to have the users be able to add new valid values.
• Most of these tables consist of just the PK and one field containing the valid values, which will also be unique. This field I set Indexed to Yes (No Duplicates). Is that the right thing to do?
• I've been planning on creating forms for users to update the attribute files as needed (not done yet).
• There is also a employees table that contains employee data that isn't tracked over time. I've done the same thing -Yes (No Duplicates)- for the employee field in this table.
• Then there is the EmployeeData table which will record all the employee attributes in force on a certain date. A new record will be added any time any one of the attributes changes.
• I'm working on a form to add or update employee records, without much success. I've been looking at subforms and combo boxes but it's not working yet. I just want some feedback on the table structure before I spend more time on trying to get a form to work.
• Many developers use an object naming system, for instance prefixing all tables with tbl, which I've copied. But Access displays a unique icon for each object type so what's the advantage of including the prefix in the object names?

Any input is appreciated.

 

[okanagan]

The best way to do this always depends on how you want to use it....

Yes, I have some more thinking to do. It feels like I'm not making much progress but I hope things will start to gel soon.

 

[The_Doc_Man]

Many developers use an object naming system, for instance prefixing all tables with tbl, which I've copied. But Access displays a unique icon for each object type so what's the advantage of including the prefix in the object names?

That depends on whether you will always be the only developer. If anyone else takes over the DB, then SOME kind of naming system will be helpful to them. And to you if you have to step away from it for a long while and then come back to it to fix things later.

You don't have to use ANY system. But once the project reaches a certain "critical mass" it will be your head that explodes with the object names that suddenly have entered the scene. Having a naming system helps to dampen that critical mass to keep it manageable.

 

[The_Doc_Man]

I'm going to step away from the previous post to talk "overview" because right now you are still gathering facts. There will be tons of things to consider and your best bet is to try to stay methodical in what you do. Divide and conquer. Do your fact-finding in a way that allows you to separate what you learned according to relevant topics.

There are a few guidelines I can offer and they won't entirely make sense right away perhaps. But I'll explain and hope that they will be "food for thought" when you are designing.

Rule #1 - If you can't do it on paper, you can't do it in Access. That is, if you don't understand the processes well enough to draw a diagram of where data originates, where/how it is processed, and where it goes, then you aren't ready to actually build anything other than as a "trial balloon." If you can put a diagram on paper, you have a roadmap. This is important because if you don't have a map to where you are going, how do you think you will ever find it, or know that you HAVE found it? Without this roadmap, you have no target and if you shoot from the hip, you will find out how lousy a shot you can be. It can be an eye-opening experience.

Rule #2 - Access can't tell you anything you didn't provide to it first. That is, Access knows NOTHING except how to make tables, queries, forms, reports, macros, and modules - and relationships. But it knows nothing about YOUR business at all. So YOU will be providing the inputs that become your working data sets. If you want to see XYZ coming out of your database, you have to provide it with XYZ somewhere, or at least you have to give it X, Y, and Z and the formula that combines them. This rule sometimes means that you have to work backwards from the desired outputs to verify that you have the inputs you need to make this DB work.

Rule #3 - When your Access Database says X and yet the real-world process says Y, the database is wrong. That is, reality always wins. When you plan your DB to do things, be sure that you are planning something that corresponds to the real-world process. Otherwise you will reach the point where either (a) your database forces a business process change (the tail is wagging the dog) or (b) your database becomes useless because it diverges too much from reality. Neither of those is good.

 

[mike60smart]

Hi
I would recommend that you populate your lookup tables with actual data which you intend to use in your process.

I have attached a revised database showing how your Main Form / SubForm should be structured.

Notice that the Subform is based on the actual Table