AnsweredAssumed Answered

Counting Unique Values from Related Table

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

Title

Counting Unique Values from Related Table

Post

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.

ARRGGHH

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

Outcomes