Query will only insert a record only if record doesn't exist alredy

[vanleurth]

Query will only insert a record only if record doesn't exist alredy

Hi everyone,

I have a table with employees categorized by groups; for example ; maintenance, operations, etc. I have another table with trainings that have different groups assigned to it.

Now let's say that I want to register everyone in group A into a training. But, an employee might belong to another group that is also defined under that training.
The final list will have a duplicated record with the same employee's name.

I would like to know how to create a query will only insert a record only if record doesn't already exist.

Thanks you in advance,

V.

 

[dhookom]

Can you set a unique index on the combination of Employee and Training? Otherwise, use a left or right join in the append query to the existing registration table. Only append records where a field from the registration table is null.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[vanleurth]

Thank you so much for the advice,

Well, I really don't see how can I use the null value here. Perhaps I should give more details.

Ok let's say I have this table called trainings with two fields.

[TrainingID] [TrainingMonth]
Example data entries
[Asbestos] [October]
[Lead Paint] [November]

Now, I have another table called training_in_groups with these fields.

[TrainingGroupsID] [Groups] [TrainingID]
Example of data entries
[1] [Communications] [Asbestos]
[2] [Marketing] [Asbestos]

Also, I have an employees tables that goes like this;
[EmployeeID] [Groups]
[Chad] [Communications]
[Jose] [Communications]
[Chad] [Marketing]

Ok, Now lets say that I want to create an SQL instruction that will insert all employees in a table based on the groups, What about if the employee is already in the table.

Any other advice will be appretiated,

V.

 

[dhookom]

Create your query that selects the Unique individuals from the groups that are to be assigned the training. Your result from this query would have only the trainingID and EmployeeID (with no duplicates).

Add in your table (that seems to be missing) that has the TrainingID and EmployeeID. Join the like fields but create the join such that all the records are shown from the original tables. Add the EmployeeID from the just added table to the grid and set its criteria to Is Null.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.