Auto Duplicate of Records

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

Hello All,

I have a training database that was created by someone that no longer works
for my company. It pulls employees from an employee database. I have a
table SpecTraining that holds the employees training records. I am trying
to find a way to create/duplicate records (there are about 8000) in the
SpecTraining table only for employees from the employee database that have
the Status of Active. Also, when the new record is created, I need for the
Year in the SpecTraining to be the current year. We are having to input
these thousands of records individually now, and would appreciate any help
that you can give. If there is something else that is needed to help with
my problem, please respond and I will give any info I can.

I can do a little VB but am a novice. If examples of code are given, I can
usually figure them out.

Thanks,

Daniel
 
Daniel,

I assume there is a table called Employees (or something similar) with
employee data such as an ID field, name, surnmane etc. in the employee
database, which you have imported or linked into your training database.
This being the case, then all you need is a simple append query, no code or
anything...
Make a new query in design view, and add your Employees table.
Drag the Employee ID field and the Status field down to the grid.
Under field Status type in "Active" in the first criterion line.
Add a calculated field by typing the following expression in the Field line
of the next available field in the grid:
Year: Year(Date())
Now go to menu Query > Append, to make your query an append one, and when
prompted select table SpecTraining.
Go back to the grid; notice the new line Append To; in this line, under
Employee ID select the pertinent field of the SpecTraining table, and under
the calculated field for Year select again the pertinent field of the
SpecTraining table. No selection for the Status field, this is just a
filter.
Now go Query > Run; you will be prompted to confirm addition of X number of
records to your SpecTraining table. Just confirm and your new records are
created!

HTH,
Nikos
 
This Append query works fine, but if I try to run it again, it doubles the
amount of records to append. Why? I have in my query Status with "Active"
in criteria, SSN updating SSN from SpecTraining and Year: Year(Date())
updateing Year of Training from the SpecTraining.

First time prompts for 1000 records. Second time I run it, it prompts for
2000 records. This is the problem I am having. If you can tell me how to
avoid the duplications, I would appreciate it.

Thanks,

Daniel
 
Back
Top