INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

MS Access SQL to return most frequently appearing values.

MS Access SQL to return most frequently appearing values.

(OP)
In a summary query, I need to return the value that shows up the greatest number of times in a certain column of a table "TotalAssetsCalculated", for each route that is listed in a separate column "Route"

I have read that I need to calculate the "mode", and the approach I am using is cribbed from another forum

SELECT TOP 1 myTable.TotalAssetsCalculated, myTable.Route
FROM myTable
GROUP BY myTable.TotalAssetsCalculated, myTable.Route
HAVING (((myTable.TotalAssetsCalculated) Is Not Null))
ORDER BY Count(*) DESC;

I am getting the result for the highest appearing value across the whole dataset.

TotalAssetsCalculated
Route
5,000.00
Wales

When I would like to see the values for all the routes, e.g. 2500 Anglia, 1300 Western

Any pointers gratefully received, thank you !


RE: MS Access SQL to return most frequently appearing values.

Could you show a sample of your data?

Just a guess here:
SELECT MAX(TotalAssetsCalculated) As ABCD, Route
FROM myTable
GROUP BY Route

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: MS Access SQL to return most frequently appearing values.

(OP)
Hi Andy
Thanks for this - unfortunately MAX seems to return the highest value within Route rather than the most frequently occurring within Route

Anglia 6,004.00
East Midlands 4,829.00
Kent 4,139.00


The results I am after would look like this:

Anglia 6,003.89
East Midlands 4,828.87
Kent 4,138.42

I have posted a data sample below.
At present the Top 1 (mode) function just returns
Anglia 6,003.89
- Because it appears 11 times
(East Midlands 4,828.87 10 times, Kent 4,138.42, 9 times)

I am a pretty infrequent user of Access SQL; I tried adapting the "As ABCD" function and got nowhere... any help very welcome.

Route TotalAssets_Calculated
Anglia 5,000.00
Anglia 5,000.00
Anglia 6,000.00
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,003.89
Anglia 6,004.00
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,828.87
East Midlands 4,829.00
Kent 4,000.00
Kent 4,138.42
Kent 4,138.42
Kent 4,138.42
Kent 4,138.42
Kent 4,138.42
Kent 4,138.42
Kent 4,138.42
Kent 4,138.42
Kent 4,138.42
Kent 4,139.00

Thanks again

RE: MS Access SQL to return most frequently appearing values.

A good place to start would be here:

Select Route, TotalAssets_Calculated, Count(*) As MyCount
FROM myTable
Group By Route, TotalAssets_Calculated

Route      TotalAssets_Calculated MyCount
Anglia        5,000.00                 2
Anglia        6,000.00                 1
Anglia        6,003.89                11
East Midlands 4,828.87                10
East Midlands 4,829.00                 1
Kent          4,000.00                 1
Kent          4,138.42                 9
Kent          4,139.00                 1
 
Now we just need to get the BLUE records out of it...

Select Route, TotalAssets_Calculated, Max(MyCount)
From (
Select Route, TotalAssets_Calculated, Count(*) As MyCount
FROM myTable
Group By Route, TotalAssets_Calculated )
Group By Route, TotalAssets_Calculated


SQL's not tested, just an idea smile

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: MS Access SQL to return most frequently appearing values.

(OP)
Thanks Andy, have tried with this but seem to be getting identical results for both..

"Mode1"

SELECT Route, TotalAssets_Calculated, Count(*) AS MyCount
FROM myTable
GROUP BY Route, TotalAssets_Calculated;


Then running "Mode2"

SELECT Mode1.Route, Mode1.TotalAssets_Calculated, Max(Mode1.MyCount) AS MaxMyCount
FROM Mode1
GROUP BY Mode1.Route, Mode1.TotalAssets_Calculated;


Showing all values, not the "Max" counts I was hoping to see in Mode2.

RE: MS Access SQL to return most frequently appearing values.

Based on the sample provided, what do you get as the outcome from
"Mode1":

Select Route, TotalAssets_Calculated, Count(*) As MyCount
FROM myTable
Group By Route, TotalAssets_Calculated

???

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: MS Access SQL to return most frequently appearing values.

(OP)
Hi Andy
Seems to be this:

Route TotalAssets_Calculated MaxMyCount
Anglia 6,003.89 11
Anglia 5,000.00 2
Anglia 6,004.00 1
Anglia 6,000.00 1
East Midlands 4,828.87 10
East Midlands 4,829.00 1
Kent 4,138.42 9
Kent 4,139.00 1
Kent 4,000.00 1

RE: MS Access SQL to return most frequently appearing values.

WildbloodS,
In addition to what I had so fat, I've got to this point:

SELECT Route, Max(MyCount) As MyMax
FROM (SELECT Route, TotalAssets_Calculated, Count(*) as MyCount
FROM MyTable
Group By Route, TotalAssets_Calculated)
GROUP BY Route;

and have this information:
Route           MyMax
Anglia             11
East Midlands      10
Kent                9
 
But I cannot get it going any further sad

We have to wait for somebody smarter than me to resolve it...
(Unless I will have a revelation over the weekend... but don't count on it)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: MS Access SQL to return most frequently appearing values.

(OP)
Hey, thanks for your assistance Andy, much appreciated !

RE: MS Access SQL to return most frequently appearing values.

I'm not sure if you need 1 or two queries but if it works all in one, below. I suspect you might have to use TOP in a second query instead of that one. But in short, group by, count and sort the count descending and get the top x values.


SELECT TOP 3 Route, TotalAssets_Calculated, Count(*) AS MyCount
FROM myTable
GROUP BY Route, TotalAssets_Calculated
Order By MyCount DESC;

RE: MS Access SQL to return most frequently appearing values.

Yes, but if he has in his data:
Yorkshire
Scotland
Wales
...

The "TOP 3" approach will not work. sad

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: MS Access SQL to return most frequently appearing values.

But if the data is ordered by MyCount, it should work fine, unless it doesn't work with it and you need a second query.... I so rarely use Top I don't remember without trial by fire.

RE: MS Access SQL to return most frequently appearing values.

(OP)
Thanks both for your comments, unfortunately I am on other work this week, but hoping to get back to this next week - much appreciated.

RE: MS Access SQL to return most frequently appearing values.

(OP)
Still working on this... thanks for your input

RE: MS Access SQL to return most frequently appearing values.

(OP)
Both
Thanks very much for sticking with this one. I have finally got a solution, cribbed from a stack overflow post


SELECT n.Route, TotalAssets_Calculated
FROM (SELECT Route, TotalAssets_Calculated, Count(TotalAssets_Calculated) as cnt
FROM myTable3 as t
GROUP BY Route, TotalAssets_Calculated
) AS t INNER JOIN (SELECT Route, max(cnt) as maxcnt
FROM (SELECT Route, TotalAssets_Calculated, Count(TotalAssets_Calculated) as cnt
FROM myTable3
GROUP BY Route, TotalAssets_Calculated
) as t
GROUP BY Route
) AS n ON (t.cnt = n.maxcnt) AND (t.Route = n.Route);



(Where there is More than one result per Route - I use a simple avg query for the 2 or more results)

Thanks again
Regards
Wildblood


RE: MS Access SQL to return most frequently appearing values.

Sorry I somehow missed the requirement in the OP... But that query looks good. I reformatted below so it is easier for us mere mortals to read.

CODE

SELECT n.Route, TotalAssets_Calculated
FROM (
      SELECT Route, TotalAssets_Calculated, Count(TotalAssets_Calculated) as cnt
      FROM myTable3 as t
      GROUP BY Route, TotalAssets_Calculated
      ) AS t 
   INNER JOIN 
     (
      SELECT Route, max(cnt) as maxcnt
      FROM (
            SELECT Route, TotalAssets_Calculated, Count(TotalAssets_Calculated) as cnt
            FROM myTable3
            GROUP BY Route, TotalAssets_Calculated
            ) as t
      GROUP BY Route
      ) AS n 
    ON (t.cnt = n.maxcnt) AND (t.Route = n.Route); 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close