TopValues

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to query/report the Top 5 Values per State from a large table that has
1000s of records and all states are well represented.

I know how to use TopValues option in tool bar. But that supplies top
values from the entire table....so I get only a total of 5 records responded.

Haven't discovered a way to get the top 5 responses per State.
 
um...well thanks, I think..... glanced at the v2002 of that doc and they
have a join of two separate tables plus an expression to sum total
quantities....So the example seems to be far more complicated than my single
table situation (without any sum needed)........am trying to muddle thru it
to figure out which applies and which doesn't......
 
SELECT TableName.StateName, TableName.DesiredValueField
FROM TableName
WHERE TableName.DesiredValueField In
(SELECT TOP 5 T.DesiredValueField
FROM TableName AS T
WHERE T.StateName = TableName.StateName
ORDER BY T.DesiredValueField DESC);
 
Thanks Ken, you really delivered a very nice, efficient chunk of code.
Impressive. I copy/pasted it into a new query in SQL View, changed out my
field names - and it worked.

The first data sheet view had all values mingled but I went over to Design
view and put in sort Ascending in the State name field - - - and it really
looked good.

I noticed however that the Sales values were still co-mingled and not
descending - so I went back once again to Design View and added that sort - -
- and this time it looked great.

I then went back to Design View and added in the other fields I wanted
showing. I am really impressed - - thank you.
 
You're welcome.

--

Ken Snell
<MS ACCESS MVP>

NetworkTrade said:
Thanks Ken, you really delivered a very nice, efficient chunk of code.
Impressive. I copy/pasted it into a new query in SQL View, changed out my
field names - and it worked.

The first data sheet view had all values mingled but I went over to Design
view and put in sort Ascending in the State name field - - - and it really
looked good.

I noticed however that the Sales values were still co-mingled and not
descending - so I went back once again to Design View and added that
sort - -
- and this time it looked great.

I then went back to Design View and added in the other fields I wanted
showing. I am really impressed - - thank you.
Click to expand...
 
Back
Top