how to make a crosstab with a where clause?

[dochsm]

The simplified core of a problem I am working on has 3 tables. A table
of people (PID, Name), a table of things (TID,Decription) and a table
of interactions (PID, TID, score, date). Not all person/thing
combinations will be present.

I need to do a crosstab on the interaction table showing the number of
times each person scored each thing.
That bit's OK using a Xtab with PID as the row, TID as the col and
count(score) as the value.

However, as there are quite a lot of people and things I only want to
show rows/columns where the value of count(scores) is bigger than some
value, so that I can do exception reporting showing only people
scoring the same thing lots of times.

How can this be done?
Howard

 

[John Spencer]

Two query solution. First query identifies cases where a person has 10 or
more interactions. Second query uses that in a join to filter the records you
want returned in the crosstab. For convenience I have assigned aliases to the
table names. All you should need to do is replace the table names with your
actual tables names.

Below is the SQL of the queries you would need to build. (View: SQL to see the
actual SQL of your queries.

Query One (Saved as qMoreThan10):
SELECT PID, TID
FROM Interactions as I INNER JOIN Things as T
ON I.TID = T.Tid
GROUP BY PID, TID
HAVING COUNT(Scores) > 10

Query Two
TRANSFORM Count(I.Scores) as TheCount
SELECT I.PID
FROM Interactions as I INNER JOIN qMoreThan10 as Q
ON I.Pid = Q.Pid
AND I.Tid = P.Tid
GROUP BY I.PID
PIVOT I.TID

Depending on your Actual field and table names you could do that in one query
using a sub-query to identify the records you need to return.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[dochsm]

Ah!, Thank you, I hadn't thought of doing it in reverse. I was trying
to filter the xtab instead if xtabbing the filter. It's funny how one
you get your mind set on a solution you keep persuing it, ignoring the
more easy side roads. (unless you're an MVP I suppose!). Thanks again.
I'll work on doing in one query.
Howard

 

[John Spencer]

IF it will work as a single query, the single query would look like the following

TRANSFORM Count(I.Scores) as TheCount
SELECT I.PID
FROM Interactions as I INNER JOIN
(SELECT PID, TID
FROM Interactions as I1 INNER JOIN Things as T
ON I1.TID = T.Tid
GROUP BY PID, TID
HAVING COUNT(Scores) > 10 ) as Q
ON I.Pid = Q.Pid
AND I.Tid = Q.Tid
GROUP BY I.PID
PIVOT I.TID

I did have an error in my first response. In the second query, I referred to
P.TID instead of Q.Pid in the line "AND I.Tid = P.Tid".

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County