Search for information from Table A to see if it is in Table B

[Dave]

I want to look for specific infor mation in one table and see if it exists in
another table

Table A
Model
1
2
3

Table B
Skills - Memo Field
1 2 4 5 >> Model 1 2 from above would match
3 4 5 >> Model 3 from above would match
1 5 >> Model 1 from above would match
5 >> No matches

I tried

SELECT Model.Model, Skills.Skills INTO Audit
FROM Model, Skills
WHERE (((Model.Model) Like "*" & ([Skills].[Skills]) & "*"));

AND

WHERE (((Model.[Model]) In ([Skills].[Skills])));

can you get me started

 

[John W. Vinson]

I want to look for specific infor mation in one table and see if it exists in
another table

Table A
Model
1
2
3

Table B
Skills - Memo Field
1 2 4 5 >> Model 1 2 from above would match
3 4 5 >> Model 3 from above would match
1 5 >> Model 1 from above would match
5 >> No matches

I tried

SELECT Model.Model, Skills.Skills INTO Audit
FROM Model, Skills
WHERE (((Model.Model) Like "*" & ([Skills].[Skills]) & "*"));

AND

WHERE (((Model.[Model]) In ([Skills].[Skills])));

can you get me started

Well, you're going to have a HECK of a hard time doing this, because your
Skills memo field violates the basic principle that fields should be atomic,
having only one value.

If the memo field contains "11 24 31" then it ipso facto contains "1", "2",
"3" and "4". Sure, you SEE them as just 11, 24 and 31 - but as far as Access
is concerned, it's just a string of eight ASCII characters, and one of those
eight charcters is "3".

You'll need to check for preceding and trailing blanks, characters at the
beginning and the end of the memo field, etc.

Any chance that you can clean up your data structure!?

Your first example should work (with the examples given and the above
caveats); in what way did it not work?

 

[Ken Sheridan]

If you add the following function to a standard module:

Public Function FindWord(varFindIn As Variant, varWord As Variant) As Boolean

Const PUNCLIST = " .,?!:;(){}[]"
Dim intPos As Integer

FindWord = False

If Not IsNull(varFindIn) And Not IsNull(varWord) Then
intPos = InStr(varFindIn, varWord)

If intPos > 0 Then
' is it at start of string
If intPos = 1 Then
' is it whole string?
If Len(varFindIn) = Len(varWord) Then
FindWord = True
Exit Function
' is it followed by a space or punctuation mark?
ElseIf InStr(PUNCLIST, Mid(varFindIn, intPos + Len(varWord),
1)) > 0 Then
FindWord = True
Exit Function
End If
Else
' is it precedeed by a space or punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos - 1, 1)) > 0 Then
' is it at end of string or followed by a space or
punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos + Len(varWord),
1)) > 0 Then
FindWord = True
Exit Function
End If
End If
End If
End If
End If

End Function

(Watch out for single lines in the above which might have been split over
two lines by your newsreader)

Then you should be able to call it in a query like so:

WHERE FindWord(Skills.Skills, Model.Model);

The function handles the points John made about preceding and trailing
blanks, characters at the beginning and the end of the memo field, etc, but
the more important problem is that you are using the memo field as data
structure, which means the Skills table is not normalized to First Normal
Form.

Ken Sheridan
Stafford, England

 

[Dave]

I appreciate the replies.

I should have been offered a little more here.
Table B looks similar to this:
Table B
Skills - Memo Field
1, 2, 4, 5 >> Model 1 2 from above would match
3, 4, 5 >> Model 3 from above would match
1, 5 >> Model 1 from above would match
5 >> No matches

I am interested in your comments about using a memo field.

Here is the exercise

We look up an employee who is using Skill 1 to service a customer, we want
to validate his\her training, so I look into the memo field and if I get a
match, we call this person skilled.

The exercise I inquired about is an audit. The everyday exercise uses an
"IN" statement.

These skills has been a challenge since it's inception so I am open to
another design\idea.

I look at this like a Thesaurus, are the like works in a Thesaurus each
listed as a separate record, or are they grouped in a Text or memo field?

I am always open to a "better mouse trap"

Thanks

Dave

If you add the following function to a standard module:

Public Function FindWord(varFindIn As Variant, varWord As Variant) As Boolean

Const PUNCLIST = " .,?!:;(){}[]"
Dim intPos As Integer

FindWord = False

If Not IsNull(varFindIn) And Not IsNull(varWord) Then
intPos = InStr(varFindIn, varWord)

If intPos > 0 Then
' is it at start of string
If intPos = 1 Then
' is it whole string?
If Len(varFindIn) = Len(varWord) Then
FindWord = True
Exit Function
' is it followed by a space or punctuation mark?
ElseIf InStr(PUNCLIST, Mid(varFindIn, intPos + Len(varWord),
1)) > 0 Then
FindWord = True
Exit Function
End If
Else
' is it precedeed by a space or punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos - 1, 1)) > 0 Then
' is it at end of string or followed by a space or
punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos + Len(varWord),
1)) > 0 Then
FindWord = True
Exit Function
End If
End If
End If
End If
End If

End Function

(Watch out for single lines in the above which might have been split over
two lines by your newsreader)

Then you should be able to call it in a query like so:

WHERE FindWord(Skills.Skills, Model.Model);

The function handles the points John made about preceding and trailing
blanks, characters at the beginning and the end of the memo field, etc, but
the more important problem is that you are using the memo field as data
structure, which means the Skills table is not normalized to First Normal
Form.

Ken Sheridan
Stafford, England

I want to look for specific infor mation in one table and see if it exists in
another table

Table A
Model
1
2
3

Table B
Skills - Memo Field
1 2 4 5 >> Model 1 2 from above would match
3 4 5 >> Model 3 from above would match
1 5 >> Model 1 from above would match
5 >> No matches

I tried

SELECT Model.Model, Skills.Skills INTO Audit
FROM Model, Skills
WHERE (((Model.Model) Like "*" & ([Skills].[Skills]) & "*"));

AND

WHERE (((Model.[Model]) In ([Skills].[Skills])));

can you get me started

 

[John W. Vinson]

I look at this like a Thesaurus, are the like works in a Thesaurus each
listed as a separate record, or are they grouped in a Text or memo field?

A thesaurus is designed to be read by a human. A relational table is designed
to be read by a computer program. These entities have different skill sets!!!

Storing multiple skills in a single memo field is simply incorrect design. A
field should have one and only one value.

What you have is a perfectly normal many to many relationship: each employee
has zero, one or more Skills, and each Skill may be exhibited by zero, one or
more Employees. A relational model would have three tables:

Employees
EmployeeID
LastName
FirstName
<etc, you already have this I'm sure>

Skills
SkillID <Number or Autonumber, primary key>
Description <text, e.g. "Public Speaking">

EmployeeSkills
EmployeeID <link to Employees>
SkillID <link to Skills>

Rather than one field with multiple entries, you would put multiple *rows*
into EmployeeSkills. It then becomes an almost trivial matter to identify
employees with a particular skill, just by creating a query joining the three
tables.

 

[Ken Sheridan]

The function I posted should work with your present set-up. If you examine
the code you'll see that it identifies a 'word' as a character or contiguous
set of characters delimited by the start or end of the field's contents, a
space or a punctuation character. In your case each value is followed by a
comma, so it should work fine. This is only a way of working around a
fundamentally flawed design, however, and John has given you a description of
the correct approach.

Relational databases use a process known as normalization to ensure that the
design is sound, of which there are five normal forms, plus an extra one
which was added when it was found that the original five didn't cope with one
particular set of circumstances. The very first of these, First Normal Form
(1NF) is defined formally as:

First Normal Form: A relvar is in 1NF if and only if, in every legal value
of that relvar, every tuple contains exactly one value for each attribute.

Roughly speaking a relvar (relation variable) equates to a table, a tuple to
a row (record) and an attribute to a column (field), so what this is saying
is that at any column position in any row in a table there must be only one
value. By including a comma delimited list as a string in a memo field you
have multiple values. The string might not look like it but it is in fact a
data structure. In the correct model as described by John the data structure
is a table and each value is stored at one column position in one row of the
table, which is the correct way, and conforms with another fundamental
principle of the database relational model, the Information Principle:

'The entire information content of the database is represented in one and
only one way, namely as explicit values in column positions in rows in
tables'.
C J Date - Introduction to Database Systems; 7th Edition; 2000.

Ken Sheridan
Stafford, England

I appreciate the replies.

I should have been offered a little more here.
Table B looks similar to this:
Table B
Skills - Memo Field
1, 2, 4, 5 >> Model 1 2 from above would match
3, 4, 5 >> Model 3 from above would match
1, 5 >> Model 1 from above would match
5 >> No matches

I am interested in your comments about using a memo field.

Here is the exercise

We look up an employee who is using Skill 1 to service a customer, we want
to validate his\her training, so I look into the memo field and if I get a
match, we call this person skilled.

The exercise I inquired about is an audit. The everyday exercise uses an
"IN" statement.

These skills has been a challenge since it's inception so I am open to
another design\idea.

I look at this like a Thesaurus, are the like works in a Thesaurus each
listed as a separate record, or are they grouped in a Text or memo field?

I am always open to a "better mouse trap"

Thanks

Dave

If you add the following function to a standard module:

Public Function FindWord(varFindIn As Variant, varWord As Variant) As Boolean

Const PUNCLIST = " .,?!:;(){}[]"
Dim intPos As Integer

FindWord = False

If Not IsNull(varFindIn) And Not IsNull(varWord) Then
intPos = InStr(varFindIn, varWord)

If intPos > 0 Then
' is it at start of string
If intPos = 1 Then
' is it whole string?
If Len(varFindIn) = Len(varWord) Then
FindWord = True
Exit Function
' is it followed by a space or punctuation mark?
ElseIf InStr(PUNCLIST, Mid(varFindIn, intPos + Len(varWord),
1)) > 0 Then
FindWord = True
Exit Function
End If
Else
' is it precedeed by a space or punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos - 1, 1)) > 0 Then
' is it at end of string or followed by a space or
punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos + Len(varWord),
1)) > 0 Then
FindWord = True
Exit Function
End If
End If
End If
End If
End If

End Function

(Watch out for single lines in the above which might have been split over
two lines by your newsreader)

Then you should be able to call it in a query like so:

WHERE FindWord(Skills.Skills, Model.Model);

The function handles the points John made about preceding and trailing
blanks, characters at the beginning and the end of the memo field, etc, but
the more important problem is that you are using the memo field as data
structure, which means the Skills table is not normalized to First Normal
Form.

Ken Sheridan
Stafford, England

I want to look for specific infor mation in one table and see if it exists in
another table

Table A
Model
1
2
3

Table B
Skills - Memo Field
1 2 4 5 >> Model 1 2 from above would match
3 4 5 >> Model 3 from above would match
1 5 >> Model 1 from above would match
5 >> No matches

I tried

SELECT Model.Model, Skills.Skills INTO Audit
FROM Model, Skills
WHERE (((Model.Model) Like "*" & ([Skills].[Skills]) & "*"));

AND

WHERE (((Model.[Model]) In ([Skills].[Skills])));

can you get me started