Prev: pass through query
Next: Query: Calculating
From: Ojoj on 25 Feb 2010 11:38
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.
From: KARL DEWEY on 25 Feb 2010 12:55
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.
From: John Spencer on 25 Feb 2010 14:43
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.
 | 
Pages: 1
Prev: pass through query
Next: Query: Calculating