I think I know what I need but i don't know enough SQL to write it.
I have a many to many relationship between assets and vulnerabilities (and a bunch more but one is sufficient) and a junction table assets_vulnerabilities.
A vulnerability may apply to multiple assets and an asset can have multiple vulnerabilities.
My main form creates vulnerability records and part of that is specifying what assets the vulnerability applies to.
I am using a subform with a continuous form based on a combobox to populate the junction table.
My first attempt used the list of assets to populate the checkbox but that allowed multiple entries with the same (asset, vulnerability) pair; a violation of referential integrity. Which brings me to the SQL I don't know how to write.
In English what I want is, Given the current vulnerability (v) find all assets (a) for which (a,v) is not in the junction table.
Many thanks,
Hank Cohen
I have a many to many relationship between assets and vulnerabilities (and a bunch more but one is sufficient) and a junction table assets_vulnerabilities.
A vulnerability may apply to multiple assets and an asset can have multiple vulnerabilities.
My main form creates vulnerability records and part of that is specifying what assets the vulnerability applies to.
I am using a subform with a continuous form based on a combobox to populate the junction table.
My first attempt used the list of assets to populate the checkbox but that allowed multiple entries with the same (asset, vulnerability) pair; a violation of referential integrity. Which brings me to the SQL I don't know how to write.
In English what I want is, Given the current vulnerability (v) find all assets (a) for which (a,v) is not in the junction table.
Many thanks,
Hank Cohen