Max of Count

[gemma-the-husky]

I'm struggling with this one.

first, I do a total query to retreive the frequency of a certain attribute

eg This produces

Customer Depot Order

CustA North 12 orders
CustA South 8 orders
CustA East 10 orders etc

CustB North 9
CustB West 11
CustB East 10

So now I want to retrieve for each customer, the Depot with the highest order count

ie

CustA North (12)
CustB West (11)

I can't find the right structure for the second query to generate the answer, because as soon as I do a group by, I get all Depots again! Or can I do the whole thing with one query.

If there are two similar max counts, ideally, I want to return either one. I suppose I could do a dlookup on the max count to retrieve the associated depot, but this seems sloppy.

 

[ByteMyzer]

Try something like this SQL (substituting the appropriate table/field names):

SELECT T1.[i][u]Customer[/u][/i], T1.[i][u]Depot[/u][/i], Count(T1.[i][u]Order[/u][/i]) AS Orders
FROM [i][u]MyTable[/u][/i] AS T1
GROUP BY T1.[i][u]Customer[/u][/i], T1.[i][u]Depot[/u][/i]
HAVING Count(T1.[i][u]Order[/u][/i])=
 (SELECT TOP 1 Count(T2.[i][u]Order[/u][/i])
  FROM [i][u]MyTable[/u][/i] AS T2
  WHERE T2.[i][u]Customer[/u][/i]=T1.[i][u]Customer[/u][/i]
  GROUP BY T2.[i][u]Customer[/u][/i], T2.[i][u]Depot[/u][/i]
  ORDER BY Count(T2.[i][u]Order[/u][/i]) DESC
 );

 

[gemma-the-husky]

TOP - thats probably it, I was trying MAX but getting nowhere thanks

 

[ccox]

This worked beautifully for my max problem also. Thank you so much!!!

 

[gemma-the-husky]

Hey ByteMyzer

I'm doing something wrong

I assign your code to a string, changing all the references and then

try docmd.runsql sqlstrg, but i get an invalid sqlstrg message - is this the way to do it, treating each element as text?

 

[neileg]

Hey ByteMyzer

I'm doing something wrong

I assign your code to a string, changing all the references and then

try docmd.runsql sqlstrg, but i get an invalid sqlstrg message - is this the way to do it, treating each element as text?

I know it's the easy way, but just build this in the query builder. Do you need to string it?