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.