X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> How Do We Show The N Highest Hits By Category?, Access 2010    
post Sep 4 2017, 04:50 AM

Posts: 2
Joined: 3-September 17

I have a db of plant remedies showing what trauma each has alleviated and where (on the body) that trauma was active.

The fields of interest in this are:
Id (primary key), BodyPart, Remedy.

The db presently has 162,000 entries, each designating one of 80 body parts/conditions affected or remedied.

Now we'd like a report showing the remedies having the highest number of hits for each BodyPart, arragned by BodyPart.

The Table is in MS Access.
I queried the data as follows to get a frequency of hits, where:

'BodyParts = An-Tsandry
'Aoly = Remedy
'CntId = CountOfId (the primary key in the Table HK-Tdy)

SELECT [HK-Tdy].[An-tsandry], [HK-Tdy].Aoly, Count([HK-Tdy].ID_Tdy) AS CntId
GROUP BY [HK-Tdy].[An-tsandry], [HK-Tdy].Aoly
ORDER BY [HK-Tdy].[An-tsandry], Count([HK-Tdy].ID_Tdy) DESC;

Where I'm at a loss, is how to limit the output of this query to the 5 top remedies for each body part, where "top" refers to the number of positive hits by a remedy on the BodyPart.

Thanks in advance for any help on this.
Go to the top of the page
Doug Steele
post Sep 4 2017, 09:23 AM

UtterAccess VIP
Posts: 21,852
Joined: 8-January 07
From: St. Catharines, ON (Canada)

Is Allen Browne's Top n records per group what you need?

Doug Steele, Microsoft Access MVP (2000-2018)
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
post Sep 4 2017, 12:12 PM

Posts: 2
Joined: 3-September 17

Thank you for the lead, Mr. Steele,
Actually I'd been studying that and tried it as a query based on the query I showed, but it responded that only one record at most would be output, but then it didn't even output that. I'm not sure where to place that script, if in a report, well I could use help on that avenue also.
The difference in Allen Browne's sample, his primary key is an ID but I'm taking data from a summary where is there is no longer a primary key. I can't apply that script to my table but to a query. I then made a table from my query but it made no difference in the system retort when trying to run a query modeled on Mr. Browne's.
I found a similar SQL at https://www.pcreview.co.UK/threads/create-t...-group.1168779/ where Brian Camire and Tom Elingson helped out Kirk P. Their help was apropos to that case, and I will take their advice to make sure I have indexed. Now I tried it again following as closely as I could on the new table made by the query according to Allen Browne and voila it took! but though I have a speedy little laptop this took a half hour to compile and another half hour after showing some output before completing. The db is rather heavy I suppose but that's slow!
I've copied a piece of the output below, having headings An-Tsandry (BodyPart), Aoly (remedy), CntID (CountOfId); The Araña (Lungs) category provides 5 remedies in descending order, just what we called for, but Areke below has 2 for the high value and 8 other remedies below of only 1 each. Probably the compiler had a problem with Desc being there was no gradient. Any way I can force it to output only five without having a unique key?
Thank you for your encouraging direction, it paid off to go back to it.

An-tsandry Aoly CntID

Ankababae Angoloke 1

Araña Tataoa 36
Araña Ampem 31
Araña Roipiteke 28
Araña Relefoñe 24
Araña Belañe 24

Areke Kapoke 2
Areke Paraky 1
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    15th August 2018 - 07:30 AM