Tom,
I think it comes down to what exactly do you want/need.
If you take the queries and adjust to sort descending on number of matches, and append to a table that has Target_Id as a indexed, no dups, I think you'd end up with 7 records in the table. But I don't know if that would meet your requirement.
Do you need 9 or 12 OR is 7 the bottom line?
The queries Vlad has provided are easier to maintain and can handle up to 6 keywords. I did not deal with 6 keywords and it would be somewhat cumbersome/tedious to get the sql for all combinations.
Here is what I foresee as the records that would result in the suggested table. The greyed out rows would be eliminated based on the No Duplicate index.
Based on query:
Code:
SELECT [qry01_Approach#1_Preliminary].tbl_Target.ID, [qry01_Approach#1_Preliminary].[tbl_Source.ID] AS Expr1, [qry01_Approach#1_Preliminary].F1, [qry01_Approach#1_Preliminary].F2, [qry01_Approach#1_Preliminary].F3, [qry01_Approach#1_Preliminary].SCHEMA, [qry01_Approach#1_Preliminary].TARGET_FIELDNAME, Abs([MatchF1]+[MatchF2]+[MatchF3]) AS Matches
FROM [qry01_Approach#1_Preliminary]
WHERE (((Abs([MatchF1]+[MatchF2]+[MatchF3]))<>0))
ORDER BY Abs([MatchF1]+[MatchF2]+[MatchF3]) DESC;
ID |
Expr1 |
F1 |
F2 |
F3 |
SCHEMA |
TARGET_FIELDNAME |
Matches |
6 |
1 |
ESTIMATED |
COMPLETION |
DATE |
COMMONDATA |
AFSAS_ESTIMATED_COMPLETION_DATE |
3 |
5 |
1 |
ESTIMATED |
COMPLETION |
DATE |
COMMONDATA |
AFSAS_ESTIMATED_COMPLETION |
2 |
4 |
2 |
DATE |
DEFICIENCY |
|
COMMONDATA |
AFSAS_DEFICIENCY_POC_DATE |
2 |
3 |
2 |
DATE |
DEFICIENCY |
|
COMMONDATA |
AFSAS_DEFICIENCY_DATE |
2 |
1 |
2 |
DATE |
DEFICIENCY |
|
COMMONDATA |
AFSAS_DATE_DEFICIENCY |
2 |
7 |
2 |
DATE |
DEFICIENCY |
|
COMMONDATA |
AFSAS_INJURY_DATE |
1 |
7
|
1 |
ESTIMATED |
COMPLETION |
DATE |
COMMONDATA |
AFSAS_INJURY_DATE |
1 |
6
|
2 |
DATE |
DEFICIENCY |
|
COMMONDATA |
AFSAS_ESTIMATED_COMPLETION_DATE |
1 |
4
|
1 |
ESTIMATED |
COMPLETION |
DATE |
COMMONDATA |
AFSAS_DEFICIENCY_POC_DATE |
1 |
3
|
1 |
ESTIMATED |
COMPLETION |
DATE |
COMMONDATA |
AFSAS_DEFICIENCY_DATE |
1
|
2 |
2 |
DATE |
DEFICIENCY |
|
COMMONDATA |
AFSAS_DEFICIENCY |
1 |
1
|
1 |
ESTIMATED |
COMPLETION |
DATE |
COMMONDATA |
AFSAS_DATE_DEFICIENCY |
1 |
Based on query:
Code:
SELECT *
FROM [qry11_Approach#2]
ORDER BY len(match_on) DESC;
ID |
F1 |
F2 |
F3 |
SCHEMA |
TARGET_FIELDNAME |
MATCH_ON |
6 |
ESTIMATED |
COMPLETION |
DATE |
COMMONDATA |
AFSAS_ESTIMATED_COMPLETION_DATE |
ESTIMATED, COMPLETION, DATE |
6
|
ESTIMATED |
COMPLETION |
DATE |
COMMONDATA |
AFSAS_ESTIMATED_COMPLETION_DATE |
ESTIMATED, COMPLETION |
5 |
ESTIMATED |
COMPLETION |
DATE |
COMMONDATA |
AFSAS_ESTIMATED_COMPLETION |
ESTIMATED, COMPLETION |
4 |
DATE |
DEFICIENCY |
|
COMMONDATA |
AFSAS_DEFICIENCY_POC_DATE |
DATE, DEFICIENCY |
3 |
DATE |
DEFICIENCY |
|
COMMONDATA |
AFSAS_DEFICIENCY_DATE |
DATE, DEFICIENCY |
1 |
DATE |
DEFICIENCY |
|
COMMONDATA |
AFSAS_DATE_DEFICIENCY |
DATE, DEFICIENCY |
2 |
DATE |
DEFICIENCY |
|
COMMONDATA |
AFSAS_DEFICIENCY |
DEFICIENCY |
7 |
DATE |
DEFICIENCY |
|
COMMONDATA |
AFSAS_INJURY_DATE |
DATE |
6
|
DATE |
DEFICIENCY |
|
COMMONDATA |
AFSAS_ESTIMATED_COMPLETION_DATE |
DATE |
Just some thoughts for consideration.