Using populated and unpopulated records (1 Viewer)

NSAMSA

Registered User.
Local time
Yesterday, 19:13
Joined
Mar 23, 2014
Messages
66
All:

I am trying to create a training tracking database. I have a table that has the workgroups and the courses assigned to each group, a table with employees and the workgroups that they belong to, and a query that deletes all records and repopulates in a record entry master table based on any changes made. When I query in my form I bring up the needed records and can populate the dates. The records are then saved to a history table. My issue is that, while I can get the history table information, I cannot transfer that information to a master sheet to see overall compliance and/or to only see the latest records. Essentially, I want to see all assignments, completed or not, and on the ones that have been done, I only want to see the most recent completion. Does this make sense? I can't create a relationship between dates because that removes all records not existing in the history table. I'm guessing the solution is rather simple, but its just not coming to me. Thank you for any help you can lend.
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 22:13
Joined
Jan 23, 2006
Messages
15,364
Why delete and repopulate? What is the rationale for this?
You seem to have a separate History table, but isn't that info in the table you empty and refill?

I suggest you provide a description of your process with some mock data.
For instance:
Porky Pig is a member of WorkGroup Animal......

Lead us through the process so all is clear.
 

NSAMSA

Registered User.
Local time
Yesterday, 19:13
Joined
Mar 23, 2014
Messages
66
Why delete and repopulate? What is the rationale for this?
You seem to have a separate History table, but isn't that info in the table you empty and refill?

I suggest you provide a description of your process with some mock data.
For instance:
Porky Pig is a member of WorkGroup Animal......

Lead us through the process so all is clear.
I suppose the reason I delete and repopulate, is to avoid having too many records. e.g. if I assign basket weaving to the workgroup A, which Suzy and Jake are a part of. I populate the Record Entry Table and it has two lines for Suzy and Jake doing basket weaving. Suzy completes basket weaving and I populate the history table. I then assign snorkeling to the same workgroup. If I append, I get duplicate records for basket weaving because I'm adding snorkeling, and its appending basket weaving again. Also, with the history tab, if Suzy completes the course again in two years, I still want the record of her completing it 2 years prior in history.

I can see fixing the append issue by creating multiple primary keys to keep from duplication. But a couple of issues may occur. First, if someone leaves the company, then how will their requirement be removed. Second how will I only see the most recent completion date in the record entry table but keep all completions in the history tab?

Its helping to type through this because I am starting to get a good idea of how I'll remove these obstacles, but still would like input from the experts.
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 22:13
Joined
Jan 23, 2006
Messages
15,364
Code:
Its helping to type through this because I am starting to get a good idea of how I'll remove these obstacles, but still would like input from the experts.

This is what we call analysis and possibly prototype/feasibility. Understanding the business process, the things involved and how they relate to one another.

Code:
I then assign snorkeling to the same workgroup
Do you assign "tasks/activities" to a person or a workgroup?
if only 1 member of a workgroup completes the "Task" successfully--- how is this recorded (by person or workgroup)?

Code:
Too many records is extremely unlikely in your case.
is extremely unlikely i your set up.

You need to review your setup.

Person belongs to a workgroup
"Task" is assigned to XXX? on DateAssigned?
Task is completed by YYY? on DateCompleted
Some "tasks" are not completed successfully??
Person can be assigned same task on different DateAssigned?
 

GPGeorge

Grover Park George
Local time
Yesterday, 19:13
Joined
Nov 25, 2004
Messages
1,776
I suppose the reason I delete and repopulate, is to avoid having too many records. e.g. if I assign basket weaving to the workgroup A, which Suzy and Jake are a part of. I populate the Record Entry Table and it has two lines for Suzy and Jake doing basket weaving. Suzy completes basket weaving and I populate the history table. I then assign snorkeling to the same workgroup. If I append, I get duplicate records for basket weaving because I'm adding snorkeling, and its appending basket weaving again. Also, with the history tab, if Suzy completes the course again in two years, I still want the record of her completing it 2 years prior in history.

I can see fixing the append issue by creating multiple primary keys to keep from duplication. But a couple of issues may occur. First, if someone leaves the company, then how will their requirement be removed. Second how will I only see the most recent completion date in the record entry table but keep all completions in the history tab?

Its helping to type through this because I am starting to get a good idea of how I'll remove these obstacles, but still would like input from the experts.
One of the reasons for storing data in relational database applications, rather than in spreadsheets, is that we prefer to have a history of what happened today, yesterday, last week, last year, five years ago, ten years ago.....

One would not expect a relational database application, therefore, to involve deleting records for former employees. That's part of your history.

The duplicate problem you mention is the result of an improper table and/or query design, unfortunately. While many good relational database applications do involve the use of archive tables to help maintain performance, if you have to archive data to get valid queries, that's an inappropriate table design.

The solution is start again with a properly normalized database. Training is a fairly common kind of application, but there are a surprising number of variations to suit individual organizations. Look for models or templates on training as a guide to starting over.

Essentially, you'll need three base tables (along with others to support various aspects of the process):

1) Students, or employees to be trained.--The Primary Key for Students becomes a Foreign Key in the Student/Training History table.
2) Training classes or courses.--The Primary Key for Students becomes a Foreign Key in the Student/Training History table.
3) Student/Training History.

The third table is often referred to as a Junction Table. It has three fields at a minimum.
  • A field for the Primary Key of the student
  • A field for the Primary Key of the Training Class
  • A field for the date that student took that class.
Other fields might include some sort of evaluation score for the student for the class, and so on. Your business rules determine that.

If a person leaves your employment, you do NOT delete all their records, you simply change their "InactiveDate" from Null, meaning they are still active, to the date on which they separated from employment. Exclude records for employees with non-null inactive dates from queries.
 

Users who are viewing this thread

Top Bottom