finding unmatched records in three tables, with multiple criteria 1

[portjobs]

I am working with three tables:

1) TableResources, with fields: ResourceID_pk, ResourceName
2) TableCategories, with fields: CategoryID_pk, CategoryName
3) TableResCats, with fields: ResCatID_pk, ResourceID_fk, CategoryID_fk, ResCatNote

TableResources is already populated with a list of resources. TableCategories is already populated with a list of categories. And TableResCats is partially populated with most (but not every) resource, and some (but not all) categories.

I want to populate TableResCats with one complete set of categories for each resource, without appending a category that is already in TableResCats for a particular resource.

I've been round and round with inner and outer joins, unmatched queries, etc. and can't seem to crack this nut. Any help would be greatly appreciated - thanks!

 

[Golom]

Drop [blue]ResCatID[/blue] as the primary key and define ResourceID and CategoryID as a multi-field primary key. Then
A statement like

INSERT INTO TableResCats ( ResourceID, CategoryID, ResCatNote )

SELECT ResourceID, CategoryID, '' As [ResCatNote ]

From TableResources, TableCategories

Should do it. If you duplicate values already in the table then they won't be inserted.

 

[portjobs]

Thanks much for the quick reply. I can see how that would work in principle.

Unfortunately, Access won't allow me to define ResourceID_fk and CategoryID_fk as a multi-field primary key, because it will "create duplicate values in the index, primary key, or relationship."

I've dropped ResCatID as the primary key. The message appears regardless of whether indexing is off, on-no dups, or on-dups okay (in ResourceID_fk and CategoryID_fk), and regardless of whether there is a relationship between the tables. (TableResources and TableResCats has a one-to-many relationship, as does TableCategories and TableResCats.)

The query runs regardless, but inserts a complete set of Categories for every Resource. Thanks again for your ideas.

 

[Golom]

Sorry ... from your description I was assuming that TableResCats was a many-to-many join table between the TableResources and TableCategories tables where each occurrence of a "ResourceID / CategoryID" combination would be unique. If that's not the case then

INSERT INTO TableResCats ( ResourceID, CategoryID, ResCatNote )

SELECT ResourceID, CategoryID, '' As [ResCatNote]

From TableResources R, TableCategories C

Where NOT EXISTS
      (Select 1 From TableResCats RC
       Where RC.ResourceID = R.ResourceID
         AND RC.CategoryID = C.CategoryID)

 

[portjobs]

Wow. I've never seen a query like that before.

I had just finished a (much less elegant) solution: One query displaying all the current records in TableResCats (along with a new "fake" pk field combining R.ResourceID and C.CategoryID); another query listing all possible records for TableResCats (also with a "fake" pk field), and a third query to find unmatched records between the two, using the new pk field to compare records.

Needless to say, doing it all in one query is much more efficient! At least I was able to verify I'm adding the right records, since both yielded the same answer.

Thank you, thank you, thank you.