Finding parent with most child records

[yeatmanj]

I'm familiar with the DCount function and how to use it in most cases. I have a bit of a special situation though.

The parent table contains a relationship to a child table and is a one to many relationship. I need to determine which parent record has the most children before I can perform a routine on the records.

Any ideas?

 

[gemma-the-husky]

can't you write a summary query for your subform source. Then dmax on the sum column will identify the key with the highest count.

 

[yeatmanj]

Excellent. That is exactly what I needed. I was thinking in terms that were too complex and completely forgot the obvious. Thank you very much.

 

[pbaldy]

By the way, it wouldn't be tricky with SQL either, if you needed to do it outside the form. "SELECT TOP 1..." on a totals query that grouped by the parent key and counted detail records would find you the parent with the most child records.