Query Help

[Zanzibarjones]

I have this query:

INSERT INTO tblPartColors ( PartID, ColorID )
SELECT DISTINCT tblParts.PartID, tblColors.ColorID
FROM (tblParts INNER JOIN tbl_PartsList_Import ON tblParts.PartNum = tbl_PartsList_Import.PartNum) INNER JOIN tblColors ON tbl_PartsList_Import.ColorName = tblColors.ColorName;

Which allows me to import/append tblPartColors with unique combinations of PartID and ColorID.

However, if I run the query a second time against the same data, I get duplicates in my tblPartColors. Is there anyway to do a comparative append query, which won't allow duplicate PartID, ColorID combinations?

[Allan Murphy]

In your table tblPartColors set your Primary Key to PartID+ColorID.

[Zanzibarjones]

I didn't know you could set 2 fields as a single primary key

[Allan Murphy]

Yes, you can set multiple fields as a primary key. The number of fields in a primary key depend on your table structure etc.

[HiTechCoach]

While it is possible to do I have never had to do it. It has many issues. There has been many debates on whether you should ever do it. I have never found a good reason to do it so far in 30+ years and 000's of databases.

I will make a compound index using multiple fields set to no dups.