Append Query for Duplicates in 1 Field (1 Viewer)

[SCase]

Hi there!

I am creating a database for tracking training at my workplace and have run into some trouble with archiving old training records.

Basically i would like to create an append query that i can use to move previous training records to a separate table when i add a new training record. For example:

Employee Number: 12345 Name: Jane Smith Training: Anti-Bullying Completed: 10/12/2020 Expires: 10/12/2021

so if the above record is in the Anti-bullying table, and Jane completes her annual Anti-Bullying training and i want to add the updated record to the table and move the other record into an archive table. Sorry, i am very much a beginner at MS Access.

I guess i am just wondering what criteria would i put in the append query if i wanted to move a record with duplicate employee number but remove the one with the earliest completion date.

thanks for your help!

 

[plog]

Data shouldn't be moved around in a database. Instead you use other data to designate it. Perhaps an Archive field which you mark as true to designate a record as archived. Or you simply use the existing date fields and logic in a query to ascertain which records are 'archived'.

However, for your specific issue I don't understand what an 'Archived' designation will achieve. Why can't 2 records for the same training but for different periods exist in your table? What do you think moving them will accomplish or allow you to do?

Lastly, I don't think your table(s) are structured properly. You need an Employee table where you store the name of an employee and their Number. Then in this training table, you don't store both pieces of data (or perhaps either) you store the primary key value of teh Employee table for that employee in the training table. Then when you need all your data together you JOIN it in a query and get it all together that way.

My recommendation at this point is to explain why you need to 'archive' data and also post a screenshot of your Relationship Tool so that we can see your table structures and help address any issues you have with that.

 

[SCase]

Data shouldn't be moved around in a database. Instead you use other data to designate it. Perhaps an Archive field which you mark as true to designate a record as archived. Or you simply use the existing date fields and logic in a query to ascertain which records are 'archived'.

However, for your specific issue I don't understand what an 'Archived' designation will achieve. Why can't 2 records for the same training but for different periods exist in your table? What do you think moving them will accomplish or allow you to do?

Lastly, I don't think your table(s) are structured properly. You need an Employee table where you store the name of an employee and their Number. Then in this training table, you don't store both pieces of data (or perhaps either) you store the primary key value of teh Employee table for that employee in the training table. Then when you need all your data together you JOIN it in a query and get it all together that way.

My recommendation at this point is to explain why you need to 'archive' data and also post a screenshot of your Relationship Tool so that we can see your table structures and help address any issues you have with that.

Hi, yes i didnt do a very good job at explaining the issue.

The reason for needing it to go to a separate table is that when i generate a report to see the upcoming and overdue training dates, it doesn't reflect that employee XYZ has an overdue training when i have already added a new record with their updated training record.

As for having an archive field, that doesn't help me as i would need to search for the specific record every time to archive it rather than having the database do it for me when i add the new training record for employee XYZ.

As for my table structures, i do have a table with employee information including name, ID number, department and role. i have created multiple other search forms and "add training" forms using VBA/SQL and they work fine. It is only the archiving that i am having trouble with.

I'm basically just asking what criteria i would need to put in my append query to get a record with a duplicate ID number but earlier date to move to a different table. I have seen criteria such as "Dmin" (Date minimum i assume?) but i am not sure how to properly write the expression.

In my limited knowledge of criteria i would write the Query Design like this:

Employee_Number

Criteria: =Count("*"<1)

Which, to me, would mean "if there are more than 1 of the same employee number"

and then

Train_Complete

Criteria: Dmin

Which, to me, would mean "if there are more than 1 of the same employee number and it is the smallest date".

I know this is obviously wrong, im just hoping this gives you more of an understanding about what im after.

Sorry!
Thanks for your help, i really appreciate it.

Edit:
i have also seen the DLast expression. Would this be better than Dmin?

 

[plog]

Again, an APPEND query is the wrong way to achieve this. Further your explanation as to the why didn't address how this helps you.

Now the goal is that you need to find out what trainings are overdue. This can and should be achieved without moving data around. Please explain how you currently deduce that a training is overdue.

 

[SCase]

Again, an APPEND query is the wrong way to achieve this. Further your explanation as to the why didn't address how this helps you.

Now the goal is that you need to find out what trainings are overdue. This can and should be achieved without moving data around. Please explain how you currently deduce that a training is overdue.

When a training is added using my 'Add Training' form, i have a button that has an event procedure that add's the record to the appropriate table (including employee data) and assigns an expiry date, using a Docmd.RunQuery. From here, i have a report that is populated with the criteria:

Train_Expire

Criteria: <Date()

and another report that shows the upcoming expiry dates with the following criteria

Train_Expire

Criteria:
<Date()+30 And >Date()
or
<Date()+60 And >Date()
or
<Date()+90 And >Date()

This is how i identify upcoming/overdue expiry.

If there are more than 1 record in these tables with the same employee information but different expiry dates, it will always reflect in the "overdue" report that they have not completed training, even though they have.

 

[oleronesoftwares]

Good day,
I think you will need another table(e.g attended training) to enter the employees who have gone for training and which training they went for.

So you need to get feedback from each employee or their supervisor when they have attended training.

This need table will have a relationship key with the initial table that lists expected training to be attended.

You can then have one or two update queries at most that will do the following.

A. Update the initial table to change status of training to attended(if there is a record in the attended training table for each employee)

B. Update the initial table to expired if there are no related records in attended training table and date of training has expired)

So a new field has to be added to the initial table, this will show the status of a training, e.g yes or no. you can call it attended.

 

[plog]

Can you post a screenshot of your Relationship Tool with every table expanded so we can see the tables and their fields?

 

[oleronesoftwares]

Can you post a screenshot of your Relationship Tool with every table expanded so we can see the tables and their fields?

Good suggestion, this will clarify things.

 

[Pat Hartman]

The training record needs a completed date if that can be different from the scheduled date. Otherwise, you can tell by comparing the expired date with the training date. Using some +/- x days calculation, compare the scheduled date with the expiration date to see which training needs to be updated.

 

[SCase]

 

[SCase]

we have 200+ employees and atleast 11 regular mandatory training qualifications. So it would be difficult to make a separate table for when they have completed it because then i would have to be making a new table every year. i also have a search form set up to search each employees current training qualifications and i would need to link that to the new tables every time.

i am comfortable with how the rest of my database works so basically all i am asking is is it possible to do an append query that will move a record with the criteria that if there are multiple of the employee number then it will move the one with the earliest date?

 

[plog]

...because then i would have to be making a new table every year...

Similarly to moving data, that is not how databases are to work. Your tables and fields should be able to accomodate all foreseen data needs upon its creation. Now, if a business rule changes or needs change you might add a table or a field, but the year 2022 occuring is not an unforseen circumstance.

Looking at your screenshot, you really need to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization). You're tables are veryl poorly structured. The overriding mistake you made is that you are treating each type of training as its own entity and they are not. Each type of training is just that, a type. You don't have a table for every employee? No you have a table for all employees.

Same thing goes for training--you don't need 12 tables named after each type of training, you simply need 1 table and you add a [Type] field to designate what type it is for. I see the same issue with tbl_All_Roles, instead of tables named after types you have fields named after types. That is wrong as well.


Again, I think you should read up on normalization and give it a shot with your database. The issue you posted about is a symptom of this bigger problem.

 

[Pat Hartman]

Don't worry about existing data. This structure must be changed. It is a nightmare to maintain. We can help with the conversion of this schema to what would be a properly normalized one.

Here's a start and a new way to think about things. The change is huge but you really can't leave the db the way it is. It will fail as soon as you get a new type of training or a new job position.


For example, tbl_Training:
TrainingTypeID (autonumber PK)
TrainName(AML, ABH, RSA, etc. needs a unique index)
Dec (description of the course)
TermTypeID 'days,weeks, months, years
ValidTerm 'units of TermType before training must be taken again
This would have one row for each type of training, AML, ABH, RSA, etc rather than a column for each type. The advantage of proper normalization for this table is that no strutrural change is required should you add a new type of training. Think about what would happen to this database if next week you needed to add training - BBB. You'd crawl into a corner and cry.
Then to Associate a type of training with an individual, you would use
tbl_EmpTraining:
EmpTrainingID (autonumber PK)
TrainingTypeID (FK to tbl_Training)
Employee_Number (FK to tblEmployeeInformation)
Train_CompleteDT
Train_ExpireDT (calculated based on TermTypeID and ValidTerm and stored so that those values can change without affecting existing training.
The advantage of proper normalization is that now you have ONE and only ONE table instead of ELEVEN tables. Again, think about what happens if you add a new training requirment.
tbl_Roles:
RoleID (autonumber PK)
RoleName (AML, ABH,RSA, etc , needs a unique index)

Then you need a table to connect roles to the training required for each role
tbl_TrainingRoles
TrainingRolesID (autonumber PK)
RoleID (FK to tbl_Roles)
TrainingTypeID (FK to tbl_Training)
RequiredYN

After you add a new employee, in the form's AfterInsert event, you would run an append query that uses the rows in tbl_TrainingRoles to append rows in tbl_EmpTraining.

Since the training will expire at different times, you would use the expiration of training to add a new row for that employee for that training.

If you add a new type of training, to TrainingRoles you would use a query that finds all the employees of its Role to add the appropriate new training to tblEmpTraining

It is amazing how little code will be required. Most processing will be done by queries that append rows to the tables that need them.

There would be no need to have an employee form with a subform of training which has to be maintained manually UNLESS there are optional training courses. ALL required courses will be managed automatically.

This is so important that if you can't understand how to proceed, you should hire someone to normalize the schema for you. I started but there is more work. Post the suggestion here and we'll look at it for you and review it. Then you'll need a conversion plan. Don't worry, it will all be done with queries.