Trying to join two tables. The "common field" is not an exact match. I used the following and thought it was working, but discovered some that did not get picked up. Generally the pattern appears to be that the fields match from beginning up to a certain point, but there are some that the order of some parts are in different positions or omitted.
This matches:
SourceA.DocumentTitle:
Terms of Contract Form
SourceB.Title
2018.10.17-019-XYZ-AANN-XYZ-000402-Terms of Contract Form-Rev0
This didn't match
SourceA.DocumentTitle:
2018.10.15-019-XYZ-000100-AANN-ABC-This is the Title
SourceB.Title:
2018.10.15-019-XYZ-000100-This is the Title-Rev1
I can't rely on the fields having dots and/or dashes in order to use split delimiter as I noticed sometimes they simply used a space or not such as 2018 10 15 or maybe 20181015 or the year portion may not be in the title for one of the tables. Seems that the phrase part is consistent "This is the Title", not sure how to match on it since there is stuff before and after in some cases.
I did another query where I hard coded a left(field,25) and that produced a match, obviously I don't want to hard code as there is no guarantee that those positions apply universally.
Code:
SELECT DISTINCT qrySOURCE_A.DocNo, qrySOURCE_A.DocumentNo, qrySOURCE_B.Author, qrySOURCE_A.DocumentTitle,
qrySOURCE_B.Title, qrySOURCE_A.DocumentStatus, qrySOURCE_A.DateModified, qrySOURCE_B.letterno
FROM qrySOURCE_A, qrySOURCE_B
WHERE (InStr([qrySOURCE_A].[DocumentTitle],[qrySOURCE_B].[Title])>0
AND qrySOURCE_B.SentTo="ABC")
OR (qrySOURCE_A.DocumentTitle Like [qrySOURCE_B].[Title] & "*"
AND qrySOURCE_B.SentTo="ABC")
ORDER BY qrySOURCE_A.DocNo;
This matches:
SourceA.DocumentTitle:
Terms of Contract Form
SourceB.Title
2018.10.17-019-XYZ-AANN-XYZ-000402-Terms of Contract Form-Rev0
This didn't match
SourceA.DocumentTitle:
2018.10.15-019-XYZ-000100-AANN-ABC-This is the Title
SourceB.Title:
2018.10.15-019-XYZ-000100-This is the Title-Rev1
I can't rely on the fields having dots and/or dashes in order to use split delimiter as I noticed sometimes they simply used a space or not such as 2018 10 15 or maybe 20181015 or the year portion may not be in the title for one of the tables. Seems that the phrase part is consistent "This is the Title", not sure how to match on it since there is stuff before and after in some cases.
I did another query where I hard coded a left(field,25) and that produced a match, obviously I don't want to hard code as there is no guarantee that those positions apply universally.