MS Access crosstab query -- driving me nuts
Posted on 2016-11-13
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.
I have a red pomegranate.
I have a yellow banana.
I have a red apple.
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
FROM Table_A, Table_B
WHERE (((Table_B.ShortText) Like '* [Table_A].[Word] *'))
GROUP BY Table_A.Word
Any help that can be supplied here would be GREAT.