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

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.

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

--
Build a little, test a little.


"Ojoj" wrote:

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.

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

Ojoj wrote:
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.