• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

MS Access crosstab query -- driving me nuts

OK -- let's say I have two unjoined tables in MS Access. I'm looking for a crosstab query that will find and count instances of each word in Table_A in the strings within records in Table_B. I've made a couple of passes using a crosstab query but get no output. I know I need to pivot the data and I know I need to use something like a Like operator but I'm having trouble assembling it into a workable query. I've actually considered doing this programmatically but thought it might be worth posting first since this is likely a cleaner solution if I can get it to work. BTW, there is no constraint on whether tables A and B can be linked or not -- just hadn't gone there yet.

Table_A
red
yellow
blue


Table_B
I have a red pomegranate.
I have a yellow banana.
I have a red apple.


Results
                        Word count
red                        2
yellow                  1
blue                        0

I'm in a design phase right now, so the table contents are actually pretty close to what my test data and tables look like. Here's a variation on one of the crosstabs I've tried so far:

TRANSFORM Count(Table_B.ID) AS CountOfID
SELECT Table_A.Word
FROM Table_A, Table_B
WHERE (((Table_B.ShortText) Like '* [Table_A].[Word] *'))
GROUP BY Table_A.Word
PIVOT Table_B.ShortText;

Any help that can be supplied here would be GREAT.
0
alfamikefoxtrot
Asked:
alfamikefoxtrot
  • 3
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
try this

SELECT TableA.FieldA, Sum(IIf(InStr([TableB].[FieldB],[TableA].[FieldA])>0,1,0)) AS WordCount
FROM TableA, TableB
GROUP BY TableA.FieldA;
0
 
Rey Obrero (Capricorn1)Commented:
SELECT Table_A.Word, Sum(IIf(InStr([Table_B].[ShortText],[Table_A].[Word])>0,1,0)) AS WordCount
 FROM Table_A, Table_B
 GROUP BY Table_A.Word
0
 
Dale FyeCommented:
This is not a CrossTab query, this is a Cartesian Join, where you include two unjoined tables in a query and then use a where clause limit the recordset, although could use a non-equi join to get the same feature.

I think Rey's post should give you the answer, but a non-equi join would look like:

SELECT Table_A.Word, Count(B.Sentence) as WordCount
 FROM Table_A
LEFT JOIN Table_B ON TableB.Sentence Like "*" & TableA.Word & "*"
 GROUP BY Table_A.Word
0
 
Rey Obrero (Capricorn1)Commented:
better use this query

SELECT Table_A.Word, Sum(IIf(InStr(" " & [Table_B].[ShortText] & " "," " & [Table_A].[Word] & " ")>0,1,0)) AS WordCount
 FROM Table_A, Table_B
 GROUP BY Table_A.Word

to eliminate word embedded in another word to be counted. i.e.,   red will be counted for word like prepared
0
 
alfamikefoxtrotAuthor Commented:
Excellent -- thanks to both of you. Really, nice, elegant solution. And good point on the cartesian join.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now