Hi,
i have a table that holds a set of criterias, named tlblCriteria. This table consits of a number of colums that equals each criteria. The rows in the table are unique ControlType.
By a user Interface this allow the user to choose one ore moore of the criterias to define a set of criterieas that represent a ControlType.
I have then a Query that combines each criteria from TblCriteria by the "&" so a "key" is calclulated. In a separate Query based on TblReference i generate a simular key based on the corresponding criterias in that table.
The keys is then compared to eachother (key from tbl Criteria and key from tblReference, and if a match the column "ControlType" in Reference table are updated to the ControlType defined in TblCriteria.
This work as a charme in those cases the user has used ALL the colums. But not in the cases the user only choses one or moore (not all) of the possible criterias.
The problem is that when generating the key from TblReference i dont know exactly which Fields the user has chosen in TblCriteria (or if the user later changes the choises).
Is there any way to build an Expression in the Query that checks what Fields (criterias) the user has chosen in tblCriteria and only generate the key from TblReferance from those exact Fields? This would make the Whole thing a lot moore dynamic.
TblCritaria
ControlType: S1
Criteria_A - YES
Criteria_B - NO
Criteria_C - left blank by user
Criteria_D - left blank by user
Query: Criteria_A & Criteria_B & Criteria_C & Criteria_D =YESNO (Key)
TblReference
Data1
Data2
etc.
Criteria_A - YES (included in data export from "mother system"
Criteria_B - NO (included in data export from "mother system"
Criteria_C - NO (included in data export from "mother system"
Criteria_D - NO (included in data export from "mother system"
Query2; Criteria_A & Criteria_B & Criteria_C & Criteria_D =YESNONONO (Key)
Combined Query to update Controltype (that now only Works if all columns are chosesn in tblCrieria):
If Key1 = Key2 -->Update ControlType to Controltype = TblCritria
If Query2 could be modifided to first check what Fields user has chosen in tblCriteria, and then generate key only based on those columns i would very happy.
I have tried some Version of SQL to loop through each textbox in the user input form, but With no Luck..
Any ideas of what i should try ?
i have a table that holds a set of criterias, named tlblCriteria. This table consits of a number of colums that equals each criteria. The rows in the table are unique ControlType.
By a user Interface this allow the user to choose one ore moore of the criterias to define a set of criterieas that represent a ControlType.
I have then a Query that combines each criteria from TblCriteria by the "&" so a "key" is calclulated. In a separate Query based on TblReference i generate a simular key based on the corresponding criterias in that table.
The keys is then compared to eachother (key from tbl Criteria and key from tblReference, and if a match the column "ControlType" in Reference table are updated to the ControlType defined in TblCriteria.
This work as a charme in those cases the user has used ALL the colums. But not in the cases the user only choses one or moore (not all) of the possible criterias.
The problem is that when generating the key from TblReference i dont know exactly which Fields the user has chosen in TblCriteria (or if the user later changes the choises).
Is there any way to build an Expression in the Query that checks what Fields (criterias) the user has chosen in tblCriteria and only generate the key from TblReferance from those exact Fields? This would make the Whole thing a lot moore dynamic.
TblCritaria
ControlType: S1
Criteria_A - YES
Criteria_B - NO
Criteria_C - left blank by user
Criteria_D - left blank by user
Query: Criteria_A & Criteria_B & Criteria_C & Criteria_D =YESNO (Key)
TblReference
Data1
Data2
etc.
Criteria_A - YES (included in data export from "mother system"
Criteria_B - NO (included in data export from "mother system"
Criteria_C - NO (included in data export from "mother system"
Criteria_D - NO (included in data export from "mother system"
Query2; Criteria_A & Criteria_B & Criteria_C & Criteria_D =YESNONONO (Key)
Combined Query to update Controltype (that now only Works if all columns are chosesn in tblCrieria):
If Key1 = Key2 -->Update ControlType to Controltype = TblCritria
If Query2 could be modifided to first check what Fields user has chosen in tblCriteria, and then generate key only based on those columns i would very happy.
I have tried some Version of SQL to loop through each textbox in the user input form, but With no Luck..
Any ideas of what i should try ?