Mode Value (1 Viewer)

[akb]

Is there a way to calculate the mode value in a query?

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. Is there a function built in to Access or an easy way to perform this, or do I have to make my own function to handle this?

 

[plog]

Yes, there's a way, but no there's no simple Mode function in Access. You're going to have to probably roll your own using a few Aggregate queries and possibly VBA.

If you want to post some sample data of what you are starting with and then what you expect to end with I can help. Include table and field names and enough data to cover all cases.

 

[akb]

Attached is my data.

I want to apply the mode to the column named Pr_Code.

Does this help?

 

[plog]

Ok, that's the starting data. What do you want as the final output of your query? Just '07'?

If so, that's kind of simple: build an aggreate query that gets the count of every value and return only the top 1 result when sorted by that count.

 

[akb]

I am creating a report off of this data line by line - at the end of the report I want it to tell me what the most used Pr_Code was so I don't have to go through the report and figure out myself.

Hard to explain on here. If you want a copy of my report I can send one.

 

[plog]

You can create a query in the manner I laid out to determine the most used value.