Display max of count

[ppataki]

Dear All,

I have a query that results in 3 fields:
Name, Payer Nr, CountOfName
so it shows how many Payer Nrs are assigned to each Name

It happens that two or more names are assigned to a Payer
In that case I would like to see only the one that has the maximum CountOfName
I tried with max instead of Group by but that did not work

Could you please advise?
I am quite desperate now

Many thanks!

 

[boblarson]

Start a new query with that other query as the source. Then you can do an aggregate on that other query.

 

[ppataki]

I tried that already but it does not work
I created an aggregate query and put the CountOfPayer to Max but nothing happens I got the same results as with Group by

 

[boblarson]

I tried that already but it does not work
I created an aggregate query and put the CountOfPayer to Max but nothing happens I got the same results as with Group by

can you post the db?

 

[ppataki]

Query1 shows how many times a Payer is assigned to each individual
I would like to create a query that only shows those records where the count is max (so in this example I would like to only see Adam and Andrew and exclude Kathy as she is assigned fewer times to the same Payer as Adam)

Query2 is my try but it does not work

Your help is very much appreciated!

 

[boblarson]

You probably should mention that it is a 2007 file type as not everyone has 2007. I don't have it at work yet so I can't look at it until I get home. Perhaps someone else, who does have 2007 at their disposal, can look before then.

 

[ppataki]

I apologize

Please find a 2000 version attached below

Many thanks!

 

[boblarson]

Okay, I was wrong - I needed TWO other queries to do the job. See attached revised.

 

[ppataki]

I see
But Query3 shows Adam only without Andrew
The aim would be to only see each payer once where the number of assignments (so the CountOfName) is max

 

[boblarson]

I see
But Query3 shows Adam only without Andrew
The aim would be to only see each payer once where the number of assignments (so the CountOfName) is max

Umm, that is exactly what you have in query 1.

 

[boblarson]

Sorry, now I see.

 

[boblarson]

Here you go.

 

[ppataki]

Thank you sooo much!!!!!
Now I see the logic behind