ms access: query to compare strings in tables & count

[arvintheoc]

Table A has phrases like:
go to cinema to watch a
read a book
talk on phone
watch tv
Take a picture with new camera

Table B has words like:
a
and
my
to
be
on
of
with

What is code that compares each line of table A and spits out the # matches
with items in table B?

For example, results here would be:
"go to cinema to watch a" count = 3
(matches to, to, a)

"read a book" count = 1
"talk on phone" count = 1
"watch tv" count = 0
"take a picture with new camera" count = 2….

etc.?
Thanks,

 

[KARL DEWEY]

Try this --
SELECT [Table A].field1, Count([Table B].field2) AS CountOffield2
FROM [Table A], [Table B]
WHERE (((" " & [Table A].[field1] & " ") Like "* " & [Table B].[field2] & "
*"))
GROUP BY [Table A].field1;

 

[arvintheoc]

Thanks Karl.
However, the first row in table A has 2 "to" and 1 "a" and should show a
match count of 3 but only shows 2.

Is there a way to include repeats?

Thanks,

Try this --
SELECT [Table A].field1, Count([Table B].field2) AS CountOffield2
FROM [Table A], [Table B]
WHERE (((" " & [Table A].[field1] & " ") Like "* " & [Table B].[field2] & "
*"))
GROUP BY [Table A].field1;

Table A has phrases like:
go to cinema to watch a
read a book
talk on phone
watch tv
Take a picture with new camera

Table B has words like:
a
and
my
to
be
on
of
with

What is code that compares each line of table A and spits out the # matches
with items in table B?

For example, results here would be:
"go to cinema to watch a" count = 3
(matches to, to, a)

"read a book" count = 1
"talk on phone" count = 1
"watch tv" count = 0
"take a picture with new camera" count = 2….

etc.?
Thanks,

 

[John Spencer MVP]

Perhaps this modification to Karl's query will give you the desired result

SELECT [Table A].field1
, SUM((Len(" " & [Table A].field1 & " ")
-Len(Replace(" " & [Table A].field1 & " " ," " & [TableB].Field2 & " "),""))
/ Len(" " & Table B].Field2 & " ") as CountReplacements
FROM [Table A], [Table B]
WHERE (((" " & [Table A].[field1] & " ") Like "* " & [Table B].[field2] & "
*"))
GROUP BY [Table A].field1;

I hope I got all the parentheses and additional spaces correctly placed.

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

Thanks Karl.
However, the first row in table A has 2 "to" and 1 "a" and should show a
match count of 3 but only shows 2.

Is there a way to include repeats?

Thanks,

Try this --
SELECT [Table A].field1, Count([Table B].field2) AS CountOffield2
FROM [Table A], [Table B]
WHERE (((" " & [Table A].[field1] & " ") Like "* " & [Table B].[field2] & "
*"))
GROUP BY [Table A].field1;

Table A has phrases like:
go to cinema to watch a
read a book
talk on phone
watch tv
Take a picture with new camera

Table B has words like:
a
and
my
to
be
on
of
with

What is code that compares each line of table A and spits out the # matches
with items in table B?

For example, results here would be:
"go to cinema to watch a" count = 3
(matches to, to, a)

"read a book" count = 1
"talk on phone" count = 1
"watch tv" count = 0
"take a picture with new camera" count = 2….

etc.?
Thanks,

 

[John Spencer MVP]

I think this one is correct

SELECT [Table A].field1
, SUM((Len(" " & [Table A].field1 & " ")
-Len(Replace(" " & [Table A].field1 & " " ," " & [TableB].Field2 & " "),""))
/ Len(" " & Table B].Field2 & " ")) as CountReplacements
FROM [Table A], [Table B]
WHERE (((" " & [Table A].[field1] & " ")
Like "* " & [Table B].[field2] & " *"))
GROUP BY [Table A].field1;


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

Perhaps this modification to Karl's query will give you the desired result

SELECT [Table A].field1
, SUM((Len(" " & [Table A].field1 & " ")
-Len(Replace(" " & [Table A].field1 & " " ," " & [TableB].Field2 & "
"),""))
/ Len(" " & Table B].Field2 & " ") as CountReplacements
FROM [Table A], [Table B]
WHERE (((" " & [Table A].[field1] & " ") Like "* " & [Table B].[field2] & "
*"))
GROUP BY [Table A].field1;

I hope I got all the parentheses and additional spaces correctly placed.

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

Thanks Karl.
However, the first row in table A has 2 "to" and 1 "a" and should show
a match count of 3 but only shows 2.

Is there a way to include repeats?

Thanks,

Try this --
SELECT [Table A].field1, Count([Table B].field2) AS CountOffield2
FROM [Table A], [Table B]
WHERE (((" " & [Table A].[field1] & " ") Like "* " & [Table
B].[field2] & " *"))
GROUP BY [Table A].field1;


:

Table A has phrases like:
go to cinema to watch a
read a book
talk on phone
watch tv
Take a picture with new camera

Table B has words like:
a
and
my
to
be
on
of
with

What is code that compares each line of table A and spits out the #
matches with items in table B?

For example, results here would be:
"go to cinema to watch a" count = 3
(matches to, to, a)

"read a book" count = 1
"talk on phone" count = 1
"watch tv" count = 0
"take a picture with new camera" count = 2….

etc.?
Thanks,

 

[KARL DEWEY]

The only way I can think of would be to number each record and then append
each word with record number into another table, update to remove word
appended, and repeat until field is null. Then run my query.

Thanks Karl.
However, the first row in table A has 2 "to" and 1 "a" and should show a
match count of 3 but only shows 2.

Is there a way to include repeats?

Thanks,

Try this --
SELECT [Table A].field1, Count([Table B].field2) AS CountOffield2
FROM [Table A], [Table B]
WHERE (((" " & [Table A].[field1] & " ") Like "* " & [Table B].[field2] & "
*"))
GROUP BY [Table A].field1;

Table A has phrases like:
go to cinema to watch a
read a book
talk on phone
watch tv
Take a picture with new camera

Table B has words like:
a
and
my
to
be
on
of
with

What is code that compares each line of table A and spits out the # matches
with items in table B?

For example, results here would be:
"go to cinema to watch a" count = 3
(matches to, to, a)

"read a book" count = 1
"talk on phone" count = 1
"watch tv" count = 0
"take a picture with new camera" count = 2….

etc.?
Thanks,

 

[KARL DEWEY]

John I found typo and got other errors but it tested good for me ---

SELECT [Table A].field1, Sum((Len(" " & [Table A].[field1] & "
")-Len(Replace(" " & [Table A].[field1] & " "," " & [Table B].[Field2] & "
","")))/Len(" " & [Table B].[Field2] & " ")) AS CountReplacements
FROM [Table A], [Table B]
WHERE (((" " & [Table A].[field1] & " ") Like ("* " & [Table B].[field2] & "
*")))
GROUP BY [Table A].field1;

I think this one is correct

SELECT [Table A].field1
, SUM((Len(" " & [Table A].field1 & " ")
-Len(Replace(" " & [Table A].field1 & " " ," " & [TableB].Field2 & " "),""))
/ Len(" " & Table B].Field2 & " ")) as CountReplacements
FROM [Table A], [Table B]
WHERE (((" " & [Table A].[field1] & " ")
Like "* " & [Table B].[field2] & " *"))
GROUP BY [Table A].field1;


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

Perhaps this modification to Karl's query will give you the desired result

SELECT [Table A].field1
, SUM((Len(" " & [Table A].field1 & " ")
-Len(Replace(" " & [Table A].field1 & " " ," " & [TableB].Field2 & "
"),""))
/ Len(" " & Table B].Field2 & " ") as CountReplacements
FROM [Table A], [Table B]
WHERE (((" " & [Table A].[field1] & " ") Like "* " & [Table B].[field2] & "
*"))
GROUP BY [Table A].field1;

I hope I got all the parentheses and additional spaces correctly placed.

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

Thanks Karl.
However, the first row in table A has 2 "to" and 1 "a" and should show
a match count of 3 but only shows 2.

Is there a way to include repeats?

Thanks,

:

Try this --
SELECT [Table A].field1, Count([Table B].field2) AS CountOffield2
FROM [Table A], [Table B]
WHERE (((" " & [Table A].[field1] & " ") Like "* " & [Table
B].[field2] & " *"))
GROUP BY [Table A].field1;


:

Table A has phrases like:
go to cinema to watch a
read a book
talk on phone
watch tv
Take a picture with new camera

Table B has words like:
a
and
my
to
be
on
of
with

What is code that compares each line of table A and spits out the #
matches with items in table B?

For example, results here would be:
"go to cinema to watch a" count = 3
(matches to, to, a)

"read a book" count = 1
"talk on phone" count = 1
"watch tv" count = 0
"take a picture with new camera" count = 2….

etc.?
Thanks,