How to create a SELECT query for each record in a table?

Question

I have a table, SingleCrossReference containing a list of words in Japanese. I want to query this table, and for each record, count how many times that word string appears in a separate table, Keyword. So essentially the query table I want to create would look like:

| SingleCrossReference.Word | Count(Keyword.Keyword) |
| Word 1                    |   1                    |
| Word 2                    |   2                    |
| Word 3                    |   1                    |

Also, the mechanism used to count words needs to be case-sensitive, as otherwise Access will consider two of the Japanese writing systems as the same and count them together.

I have a query that can evaluate a single record in the Keyword table:

SELECT COUNT(Keyword.Keyword)
FROM Keyword
WHERE StrComp(Keyword.Keyword, "[Japanese word]", 0)=0

But I don't know how to link the two tables so that it counts the number of times a word from the SenseCrossReference table appears in the Keyword table.

score 2 · Answer 1 · answered Dec 16 '19 at 13:35

A subquery essentially does this:

SELECT Word, 
(
      SELECT COUNT(Keyword)
      FROM Keyword
      WHERE StrComp(Keyword.Keyword, Word, 0)=0 
) As CountKeyword
FROM SingleCrossReference

However, performance is often suboptimal, and you're often off better rewriting it to a join, e.g.:

SELECT Word, Count(Keyword)
FROM SingleCrossReference, Keyword
WHERE StrComp(Keyword.Keyword, Word, 0) = 0 
GROUP BY Word

This assumes no locale issues when grouping on Word, else you'd need to add a unique identifier for each word.

How to create a SELECT query for each record in a table?

1 Answers1