Please Help with Append Query

[InfoNow]

Please look at this query. It works as below, but if I add a new record to the table ISORequirements and run this query, it doesn't append the new record to the Table TrainingRecords. Any idea why??
For Example..
If TrainingRecords & ISORequirements already have a record as follow.
EmpNum = 100 IsoNum = 1.1.1
Running the query would do nothing. Which is what I want, but if I go and add another record for EmpNum 100 in ISORequirements as follow.
EmpNum = 100 ISONum = 1.1.2
then run the query below, it doesn't add the new record to TrainingRecord table.
What I want is TrainingRecords to have
EmpNum = 100 ISONum = 1.1.1
EmpNum = 100 ISONum = 1.1.2

INSERT INTO TrainingRecords ( EmpNum, ISONum, TrainDate )
SELECT EmployeeInfo.EmpNum, ISORequirements.ISONum, Null AS Expr1
FROM EmployeeInfo INNER JOIN ISORequirements ON EmployeeInfo.JobNum = ISORequirements.JobNum
WHERE (((EmployeeInfo.EmpNum) Not In (SELECT Distinct EmployeeInfo.EmpNum
FROM (EmployeeInfo INNER JOIN TrainingRecords ON EmployeeInfo.EmpNum = TrainingRecords.EmpNum) INNER JOIN ISORequirements ON (ISORequirements.ISONum = TrainingRecords.ISONum) AND (EmployeeInfo.JobNum = ISORequirements.JobNum))));


TIA

 

[tlbroadbent]

The query is much more complex than needed. The problem is that the sub query for the NOT IN clause will always return a record if the any record exists in the TrainingRecords table for the employee. You have not included any criteria to select the current ISONum.

Try the following query. It is much simpler and should do just what you want.

INSERT INTO TrainingRecords
(EmpNum, ISONum, TrainDate)
SELECT EmployeeInfo.EmpNum,
ISORequirements.ISONum, Null AS Expr1
FROM EmployeeInfo
INNER JOIN ISORequirements
ON EmployeeInfo.JobNum = ISORequirements.JobNum
WHERE Not Exists
(SELECT * From TrainingRecords
Where EmpNum=EmployeeInfo.EmpNum
And ISONum=ISORequirements.ISONum); Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[InfoNow]

Terry,
That works beautifully... Thank you very much. The bruises on my head from hitting the wall is now feeling better.

 

[InfoNow]

Terry,
The query you provided works great, but I still have another issue. If I delete a record from the table IOSRequirements, the corresponding record in the table TrainingRecords does not get deleted. How do I go about doing this?

Thanks again for your help Terry.

 

[tlbroadbent]

You'll have to define the relationship between the tables and turn on referential intregrity and cascade deleted records. Look on the Access Tools menu for Relationships. See Help for details about setting up relationships, referential integrity and cascading updates and deletes. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[InfoNow]

Terry,
Thanks again, it works great now.