alfamikefoxtrot
asked on
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.
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.
SELECT Table_A.Word, Sum(IIf(InStr([Table_B].[S
FROM Table_A, Table_B
GROUP BY Table_A.Word
FROM Table_A, Table_B
GROUP BY Table_A.Word
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent -- thanks to both of you. Really, nice, elegant solution. And good point on the cartesian join.
SELECT TableA.FieldA, Sum(IIf(InStr([TableB].[Fi
FROM TableA, TableB
GROUP BY TableA.FieldA;