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.
Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.