Max of Count

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:54
Joined
Sep 12, 2006
Messages
16,118
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.
 
Try something like this SQL (substituting the appropriate table/field names):
Code:
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
 );
 
TOP - thats probably it, I was trying MAX but getting nowhere thanks
 
This worked beautifully for my max problem also. Thank you so much!!!
 
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?
 
gemma-the-husky said:
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?
 

Users who are viewing this thread

Back
Top Bottom