Thanks @Pat Hartman
The query appends with no duplicates now!
The Indexes interface is weird though. I would like to share screen shots to make sure I did it correctly.
Based on your example I changed my table back to one primary key, instead of a compound key. I opened the Indexes panel and these were it's defaults:
So based on your example I modified it to look like this:
I'm not really sure why my primary key field is listed twice...
Anyway, I created an index called UniqueIDX for the two fields I want treated as a unique record. I set the "Unique" property of UniqueIDX to "Yes".
According to your explanation SizeStandards_IDFK will fall under the UniqueIDX index, since there is no index name in it's row.
...In addition to this, there are also General Field Properties, one of which is the "Indexed" property. These are currently set to "No" for the fields BaseSku_IDFK and SizeStandards_IDFK.
I guess I'm still a bit hazy on all these overlapping indexing rules. Should this property be set to "Yes, Duplicates OK"? Or is "No" more appropriate here?
I will have "Pants-101" recur in the BaseSku column. And I will have size 28 recur in the SizeStandards column. But not that compound unique indexes have entered the picture, I'm not sure which to choose.