Table Comparison

[Jason Lopez]

I have a table that as a list of keywords to search through project names in
order to provide common keyresponse. However, I now have an issue that if
more than one keyword can be used, I get multiple results. The
"keyresponse" table is already organized based on priority. So if there is
a possibility of a project name having multiple keywords, then only the
first key response should be used. So, what would I change in my SQL below
to get the comparison to stop as soon as a good response is found?

SELECT dbo_Workflow.WorkflowID, dbo_Workflow.ContentName, IIf(([ContentName]
Like "*" & [keyresponse].[keyword] & "*"),[keyresponse].[response],Null) AS
KeyTerm
FROM dbo_Workflow, keyresponse
WHERE (((IIf(([ContentName] Like "*" & [keyresponse].[keyword] &
"*"),[keyresponse].[response],Null)) Is Not Null) AND
((dbo_Workflow.Group)="PL") AND ((dbo_Workflow.WorkflowPhase)="Complete"));


Jason

 

[Michel Walsh]

Either insert a DISTINCT:

SELECT DISTINCT ...


either (more friendly, but requires more work), make the query a GROUP BY
query and COUNT the number of apparition of each initial record. Clearly, if
a record get a count of, say, 5, that would mean that it fits 5 of the
keywords, so, probably, should move higher (near the first one to get out),
so, ORDER BY COUNT(*) DESC could also be added, in that case.


Vanderghast,. Access MVP

 

[John Spencer]

SELECT W.WorkflowID
, W.ContentName
, First(K.Keyword) as OneMatch
, Count(K.Keyword) as NumberOfMatches
FROM dbo_Workflow as W INNERJOIN keyresponse as K
ON W.ContentName like "*" & K.Keyword & "*"
WHERE W.Group="PL" AND W.WorkflowPhase="Complete"
GROUP BY W.WorkflowID, W.ContentName

The OneMatch field will give you one of the keywords that matched. That is
not necessarily the first word that matched. There is no way to do that based
on the available information you have given us. If you want the first word
alphabetically, use MIN in place of FIRST.

The W and K aliases are just a way to temporarily "rename" the tables to make
creating the SQL simpler.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Jason Lopez]

So, far two sets of good ideas. However, my keyreponse table is already set
up with the keywords grouped according to heirarchy. What ever key term is
at the top of the list is more important than those towards the bottom.

What I am confused at is how can I filter out the less important key terms
only retaining the keyterm of highest importance? I don't quite see how
getting the OneMatch or NumberOfMatches would be beneficial when all I need
is the first successful match. If I remove the condition of [KeyTerm] Is
Not Null (as shown in my original SQL), and replace it with the INNERJOIN ON
as shown, should that only give me one result (hopefully the first result
that works)? Otherwise, I know that there are going to be results that will
be null as previos tests show a result for each comparison. So if I have 30
key terms to compare with, I am going to have 30 occurrences for each
W.WorkflowID.

SELECT W.WorkflowID
, W.ContentName
, First(K.Keyword) as OneMatch
, Count(K.Keyword) as NumberOfMatches
FROM dbo_Workflow as W INNERJOIN keyresponse as K
ON W.ContentName like "*" & K.Keyword & "*"
WHERE W.Group="PL" AND W.WorkflowPhase="Complete"
GROUP BY W.WorkflowID, W.ContentName

The OneMatch field will give you one of the keywords that matched. That
is not necessarily the first word that matched. There is no way to do
that based on the available information you have given us. If you want
the first word alphabetically, use MIN in place of FIRST.

The W and K aliases are just a way to temporarily "rename" the tables to
make creating the SQL simpler.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a table that as a list of keywords to search through project names
in order to provide common keyresponse. However, I now have an issue
that if more than one keyword can be used, I get multiple results. The
"keyresponse" table is already organized based on priority. So if there
is a possibility of a project name having multiple keywords, then only
the first key response should be used. So, what would I change in my SQL
below to get the comparison to stop as soon as a good response is found?

SELECT dbo_Workflow.WorkflowID, dbo_Workflow.ContentName,
IIf(([ContentName] Like "*" & [keyresponse].[keyword] &
"*"),[keyresponse].[response],Null) AS KeyTerm
FROM dbo_Workflow, keyresponse
WHERE (((IIf(([ContentName] Like "*" & [keyresponse].[keyword] &
"*"),[keyresponse].[response],Null)) Is Not Null) AND
((dbo_Workflow.Group)="PL") AND
((dbo_Workflow.WorkflowPhase)="Complete"));


Jason

 

[John Spencer]

How does the computer know which keyword ranks highest? Have you
assigned some value to order the keywords? If not there is no reliable
method to determine which keyword you want to return.

Records in tables are UNORDERED - you must use the content of the record
to establish an order.

Post back with the structure of your Keyresponse table and a couple
sample records in the keyResponse fields.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

So, far two sets of good ideas. However, my keyreponse table is already set
up with the keywords grouped according to heirarchy. What ever key term is
at the top of the list is more important than those towards the bottom.

What I am confused at is how can I filter out the less important key terms
only retaining the keyterm of highest importance? I don't quite see how
getting the OneMatch or NumberOfMatches would be beneficial when all I need
is the first successful match. If I remove the condition of [KeyTerm] Is
Not Null (as shown in my original SQL), and replace it with the INNERJOIN ON
as shown, should that only give me one result (hopefully the first result
that works)? Otherwise, I know that there are going to be results that will
be null as previos tests show a result for each comparison. So if I have 30
key terms to compare with, I am going to have 30 occurrences for each
W.WorkflowID.

SELECT W.WorkflowID
, W.ContentName
, First(K.Keyword) as OneMatch
, Count(K.Keyword) as NumberOfMatches
FROM dbo_Workflow as W INNERJOIN keyresponse as K
ON W.ContentName like "*" & K.Keyword & "*"
WHERE W.Group="PL" AND W.WorkflowPhase="Complete"
GROUP BY W.WorkflowID, W.ContentName

The OneMatch field will give you one of the keywords that matched. That
is not necessarily the first word that matched. There is no way to do
that based on the available information you have given us. If you want
the first word alphabetically, use MIN in place of FIRST.

The W and K aliases are just a way to temporarily "rename" the tables to
make creating the SQL simpler.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a table that as a list of keywords to search through project names
in order to provide common keyresponse. However, I now have an issue
that if more than one keyword can be used, I get multiple results. The
"keyresponse" table is already organized based on priority. So if there
is a possibility of a project name having multiple keywords, then only
the first key response should be used. So, what would I change in my SQL
below to get the comparison to stop as soon as a good response is found?

SELECT dbo_Workflow.WorkflowID, dbo_Workflow.ContentName,
IIf(([ContentName] Like "*" & [keyresponse].[keyword] &
"*"),[keyresponse].[response],Null) AS KeyTerm
FROM dbo_Workflow, keyresponse
WHERE (((IIf(([ContentName] Like "*" & [keyresponse].[keyword] &
"*"),[keyresponse].[response],Null)) Is Not Null) AND
((dbo_Workflow.Group)="PL") AND
((dbo_Workflow.WorkflowPhase)="Complete"));


Jason

 

[Jason Lopez]

Keyword table looks like this:

Keyword; Response; Priority
ProjKey1; Project Main Name; 1
ProjKey2; Project Main Name; 2
ProjKey3; Project2 Main Name; 3

And it keeps going from there. The table is ordered according to the
priority number. The index will not allow duplicates as it is set on the
priority field. Some projects are older and have become obsolete. In order
to keep the older ones aligned with the newer ones for referencing purposes,
we are trying to set it up this way (or something similar). Essentially, if
the Project Name in the system comes up with "ProjKey1", then we want the
expression to return "Project Main Name" and end. If "ProjKey1" is not in
the project name, then it goes to the next one (and keeps going till it
finds a match. Once a match is found, the loop ends and it goes to the next
Project Name. In this manner, all the projects that are for the same master
project can be reviewed as one collective group and the progress evaluated
using some other queries that have already been created to calculate times
and date differences. That is what we are looking to do with this
comparison. Loop until a valid response is found. Once a response is
found, then the loop stops and the new project name is examined.

Jason

How does the computer know which keyword ranks highest? Have you assigned
some value to order the keywords? If not there is no reliable method to
determine which keyword you want to return.

Records in tables are UNORDERED - you must use the content of the record
to establish an order.

Post back with the structure of your Keyresponse table and a couple sample
records in the keyResponse fields.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

So, far two sets of good ideas. However, my keyreponse table is already
set up with the keywords grouped according to heirarchy. What ever key
term is at the top of the list is more important than those towards the
bottom.

What I am confused at is how can I filter out the less important key
terms only retaining the keyterm of highest importance? I don't quite
see how getting the OneMatch or NumberOfMatches would be beneficial when
all I need is the first successful match. If I remove the condition of
[KeyTerm] Is Not Null (as shown in my original SQL), and replace it with
the INNERJOIN ON as shown, should that only give me one result (hopefully
the first result that works)? Otherwise, I know that there are going to
be results that will be null as previos tests show a result for each
comparison. So if I have 30 key terms to compare with, I am going to
have 30 occurrences for each W.WorkflowID.

SELECT W.WorkflowID
, W.ContentName
, First(K.Keyword) as OneMatch
, Count(K.Keyword) as NumberOfMatches
FROM dbo_Workflow as W INNERJOIN keyresponse as K
ON W.ContentName like "*" & K.Keyword & "*"
WHERE W.Group="PL" AND W.WorkflowPhase="Complete"
GROUP BY W.WorkflowID, W.ContentName

The OneMatch field will give you one of the keywords that matched. That
is not necessarily the first word that matched. There is no way to do
that based on the available information you have given us. If you want
the first word alphabetically, use MIN in place of FIRST.

The W and K aliases are just a way to temporarily "rename" the tables to
make creating the SQL simpler.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Jason Lopez wrote:
I have a table that as a list of keywords to search through project
names in order to provide common keyresponse. However, I now have an
issue that if more than one keyword can be used, I get multiple
results. The "keyresponse" table is already organized based on
priority. So if there is a possibility of a project name having
multiple keywords, then only the first key response should be used.
So, what would I change in my SQL below to get the comparison to stop
as soon as a good response is found?

SELECT dbo_Workflow.WorkflowID, dbo_Workflow.ContentName,
IIf(([ContentName] Like "*" & [keyresponse].[keyword] &
"*"),[keyresponse].[response],Null) AS KeyTerm
FROM dbo_Workflow, keyresponse
WHERE (((IIf(([ContentName] Like "*" & [keyresponse].[keyword] &
"*"),[keyresponse].[response],Null)) Is Not Null) AND
((dbo_Workflow.Group)="PL") AND
((dbo_Workflow.WorkflowPhase)="Complete"));


Jason

 

[John Spencer]

OK then following should give you the WorkFlowID and the minimum priority

SELECT W.WorkflowID
, Min(K.Keyword) as PriorityMatch
FROM dbo_Workflow as W INNERJOIN keyresponse as K
ON W.ContentName like "*" & K.Keyword & "*"
WHERE W.Group="PL" AND W.WorkflowPhase="Complete"
GROUP BY W.WorkflowID

Then you can use that query to get all the information you want by
joining it to the workflow table and the KeyResponse table.

All in one query that should work. I can't test it, but it should get
you started on a solution.

SELECT WA.*
, K2.Keyword, K2.Response
FROM dbo_WorkFlow as WA
INNER JOIN (
SELECT W.WorkflowID
, Min(K.Keyword) as PriorityMatch
FROM dbo_Workflow as W INNERJOIN keyresponse as K
ON W.ContentName like "*" & K.Keyword & "*"
WHERE W.Group="PL" AND W.WorkflowPhase="Complete"
GROUP BY W.WorkflowID) as Temp
ON WA.WorkFlowID = Temp.WorkFlowID
INNER JOIN KeyResponse as K2
ON Temp.PriorityMatch = K2.Priority




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Keyword table looks like this:

Keyword; Response; Priority
ProjKey1; Project Main Name; 1
ProjKey2; Project Main Name; 2
ProjKey3; Project2 Main Name; 3

And it keeps going from there. The table is ordered according to the
priority number. The index will not allow duplicates as it is set on the
priority field. Some projects are older and have become obsolete. In order
to keep the older ones aligned with the newer ones for referencing purposes,
we are trying to set it up this way (or something similar). Essentially, if
the Project Name in the system comes up with "ProjKey1", then we want the
expression to return "Project Main Name" and end. If "ProjKey1" is not in
the project name, then it goes to the next one (and keeps going till it
finds a match. Once a match is found, the loop ends and it goes to the next
Project Name. In this manner, all the projects that are for the same master
project can be reviewed as one collective group and the progress evaluated
using some other queries that have already been created to calculate times
and date differences. That is what we are looking to do with this
comparison. Loop until a valid response is found. Once a response is
found, then the loop stops and the new project name is examined.

Jason

How does the computer know which keyword ranks highest? Have you assigned
some value to order the keywords? If not there is no reliable method to
determine which keyword you want to return.

Records in tables are UNORDERED - you must use the content of the record
to establish an order.

Post back with the structure of your Keyresponse table and a couple sample
records in the keyResponse fields.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

So, far two sets of good ideas. However, my keyreponse table is already
set up with the keywords grouped according to heirarchy. What ever key
term is at the top of the list is more important than those towards the
bottom.

What I am confused at is how can I filter out the less important key
terms only retaining the keyterm of highest importance? I don't quite
see how getting the OneMatch or NumberOfMatches would be beneficial when
all I need is the first successful match. If I remove the condition of
[KeyTerm] Is Not Null (as shown in my original SQL), and replace it with
the INNERJOIN ON as shown, should that only give me one result (hopefully
the first result that works)? Otherwise, I know that there are going to
be results that will be null as previos tests show a result for each
comparison. So if I have 30 key terms to compare with, I am going to
have 30 occurrences for each W.WorkflowID.

SELECT W.WorkflowID
, W.ContentName
, First(K.Keyword) as OneMatch
, Count(K.Keyword) as NumberOfMatches
FROM dbo_Workflow as W INNERJOIN keyresponse as K
ON W.ContentName like "*" & K.Keyword & "*"
WHERE W.Group="PL" AND W.WorkflowPhase="Complete"
GROUP BY W.WorkflowID, W.ContentName

The OneMatch field will give you one of the keywords that matched. That
is not necessarily the first word that matched. There is no way to do
that based on the available information you have given us. If you want
the first word alphabetically, use MIN in place of FIRST.

The W and K aliases are just a way to temporarily "rename" the tables to
make creating the SQL simpler.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Jason Lopez wrote:
I have a table that as a list of keywords to search through project
names in order to provide common keyresponse. However, I now have an
issue that if more than one keyword can be used, I get multiple
results. The "keyresponse" table is already organized based on
priority. So if there is a possibility of a project name having
multiple keywords, then only the first key response should be used.
So, what would I change in my SQL below to get the comparison to stop
as soon as a good response is found?

SELECT dbo_Workflow.WorkflowID, dbo_Workflow.ContentName,
IIf(([ContentName] Like "*" & [keyresponse].[keyword] &
"*"),[keyresponse].[response],Null) AS KeyTerm
FROM dbo_Workflow, keyresponse
WHERE (((IIf(([ContentName] Like "*" & [keyresponse].[keyword] &
"*"),[keyresponse].[response],Null)) Is Not Null) AND
((dbo_Workflow.Group)="PL") AND
((dbo_Workflow.WorkflowPhase)="Complete"));


Jason