What is wrong with this query?

[DDTiff]

Hello,

Can someone help me with this query? I don't know what I did wrong. The purpose is to insert the records from one table to the other by matching the same fields.


INSERT INTO Junction ( Unique_ID, ID, WBS, Name, Duration_In_Days, Start_Date, Finish_Date, Recurring, Milestone, Resource_Unique_ID, Resource_ID, Resource_Name, Resource_Initials )
SELECT Task.Unique_ID, Task.ID, Task.WBS, Task.Name, Task.Duration, Task.Start_Date, Task.Finish_Date, Task.Recurring, Task.Milestone, Assignment.Resource_Unique_ID, Assignment.Resource_ID, Assignment.Resource_Name, Assignment.Resource_Initials
FROM Task, Assignment;


Thank you for your help.

 

[IFELSETHEN]

Looks OK, what error do you get?

 

[DDTiff]

Hello IFELSETHEN,

Thank you for your reply.

The problem I get is that the Task table(Source) has 100 records while Junction table(Reference) has 48 records. The Source table has 9 fields while the Reference table has 14 fields. My goal is to transfer the extra 52 records from the Task table over to Junction table by matching the 9 similar fields of the two tables; however, when I run the above query, the Reference table came out to have 4800 records instead of 100. Somehow, it multiplies the records of the Source table by the Reference table (100X48).

Thank you very much for your time and help.

DTiff

 

[IFELSETHEN]

Hmmmm, how about adding 'WHERE Task.ID <> Assignment.ID' this should fill in only the missing ID's.
John

 

[DDTiff]

Hello John,

I tried putting the WHERE statement in the query; however, this time the Reference table (Junction table) has 4752 records.

This is the statment I tried in the query since in the Assignment table the Task_ID field is used to reference to the ID in the Task table:

WHERE Task.ID <> Assignment.Task_ID;

DTiff

 

[IFELSETHEN]

Contact me through my eMail vb_doc@hotmail.com