If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
In crosstab query, how do I count not including duplicates?
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. |
#3
|
|||
|
|||
In crosstab query, how do I count not including duplicates?
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. |
Thread Tools | |
Display Modes | |
|
|