In crosstab query, how do I count not including duplicates?

[Ojoj]

I have a table which lists contacts with people including the field
'FamilyID'. I want to count the number of different families the
organisation has had contact during a quarter with a crosstab query. If I
use the Count function it double counts families we have seen twice, so
showing us the overall number of contacts, but not the number of families we
had contact with.

 

[KARL DEWEY]

Post your crosstab query SQL and sample data with example of what the output
should look like.

 

[John Spencer]

You need to do a two-step process.

First build a query that returns unique records. Something like

SELECT Distinct ContactID
, Year(MeetingDate) as TheYear
, DatePart("q",MeetingDate) as TheQuarter
FROM ContactsTable

NOW use that to build your crosstab query.
TRANSFORM Count(ContactID)
SELECT TheYear
FROM TheSaveQuery
GROUP BY TheYear
PIVOT TheQuarter



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