AnsweredAssumed Answered

Counting Unique Values from Related Table

Question asked by sksmith2517 on Mar 3, 2011
Latest reply on Jun 15, 2011 by sksmith2517


Counting Unique Values from Related Table


I have a database that relates Contacts & Agencies. I want to be able to count the number of unique agencies that 70 contacts come from.  I have already successfully created summary fields for those filedsof data that relate to the contacts and are in the contacts table (# who serve specific populations, # from Home Office, etc).  But I have created a related table that gives all the Agency Info - Name, Address, Website, Department heads, etc) This table is related with Primary & Foreign Keys being an agency serial number.

On one of my Reports of Contacts, I want to be able to summarize how many unique agencies are represented.  I have used the  KnowledgeBase Answer ID#3423 from Nov, 2009. I have not been able to get an accurate count.  My results are either the same as the number of records in the found set, or 1. (Depending on how I tweak the calculation & Summary fields)

Based on that Answer #3423, I did the 3 following steps:

1) made a self-join with the Agency using the Auto Ser# as the match field. I did that in the Agency file.

2) created a calc field If (Min(_K_ID_Agency)=Agency SelfJoin::_K_ID_Agency; 1; 0)

 3) created a summary field that totals the Calc field.

Then on the Contacts' Report List - I placed the summary field from the Agency file - it always results with a number of 1.

So I created new fields within the Contacts file - accessing the same fields in agency table:

If ( Min(_KF_ID_Agency)=Agencies::_K_ID_Agency; 1;0). When I place this field (that is from within Contacts table) onto the layout, it always results in the same number as the found set.


Obviously I am not referencing the correct fields and/or creating the wrong calcs/summaries, etc. Thanks for any help.