I have a situation that is, I imagine, not uncommon.

I have three tables – essentially as follows:

TABLE: tblPROPERTIES

COLUMNS: PROP_ID, PROP_DESC
e.g.
1 “Red”
2 “Yellow”
3 “Green”
4 “Cyan”

TABLE: tblPRESET

COLUMNS: PRESET_ID, PRESET_DESC
e.g.
1 “RGC”
2 “GC”
3 “YGC”

TABLE: tblPRESET_DATA

COLUMNS: PRESET_ID, PROP_ID
e.g.
1 1
1 3
1 4
2 3
2 4
3 2
3 3
3 4

The combinations of PROP_IDs as grouped by PRESET_IDs are unique.

Although I am ultimately interested in the many-to-many relationship(?) between the tblPROPERTIES and tblPRESET tables, this question involves, I assume, a one-to-many relationship between tblPRESET and tblPRESET_DATA.

As the name implies, I use the tblPRESET + tblPRESET_DATA tables to save particular combinations of properties-essentially, to create snapshots of settings. I populate a user form with data and allow the user to save settings presets. Typically, a list with these preset names will be used to restore the presets. However, the program should also compare current settings to these presets and display the preset's name should the user happen to select a configuration that corresponds one of the saved presets.

Because all of the key IDs are numeric – one way to narrow down the possibilities is look for matching sums of PROP_ID per PRESET_ID from the tblPRESET_DATA table. This would not be fool-proof – it would, however, reduce the number of comparisons that I would have to subsequently have to perform in code. Another method would be to return compare sorted + concatenated PROP_ID values – again, requiring code.

So, my question is, is there a SQL statement that returns a key from a unique combination of values from a field – something like: ‘SELECT PRESET_ID from tblPRESET_DATA where PROP_ID = 1 and where PROP_ID = 4 and where PROP_ID = 3;’ or “SELECT PRESET_ID from tblPRESET_DATA where PROP_ID includes 1,4,3;’ (both returning 1).

Can anyone give me some advice on this? Is there a means to do this with SQL? Am I even approaching the problem in the right manner? Any and all help would be greatly appreciated.

Peter