Which Function? (Return String based on Count Value) (1 Viewer)

Hi,

This is probably going to be pretty basic, but...

I have a query that returns each record with three fields:

A stable identifier (StableID)
Text string with multiple varieties for each StableID (VariableText)
A numeric count of how many times each VariableText is associated with each StableID (TotalCount)

I'd like to create another query that lists each StableID only once with the VariableText with the highest count. That's the reason for the count, it's not valuable otherwise. What function would I use for that?

Basically the count is the first half of a MODE function, which Access doesn't appear to have. If I could do it all in one query that would work too. If this doesn't work out I'll just switch over to VBA.

Thanks in advance!

You should be able to modify this function to do what you are after.

Thanks, but I don't really see the relevant part of that function.

Edit: Oh, my bad. I guess what I said could be taken as I want a text version of the count, which I don't. The VariableText is actually data that has been inputted into the database in a variety of ways. For example, for an institution with multiple buildings, each employee has a name (StableID) and has multiple shift entries with building names (VariableText). The idea is to find the MODE for that text, or the most common value, and return it. The query (or function) should count and then return the text that has been entered the most number of times. In my example that would be the building the employee works in most often.

Basically I would need a function that goes:

For each <StableID> for greatest <TotalCount> return associated <VariableText>.

I guess I could write a VBA function for that, but I thought it would be easier and faster to make an Access query... I'm just not used to making them. Is there really no way to do that as a query?

I managed to do this with design view by making extra queries. I'm not sure how to do it in SQL (i.e. subqueries). I'll put it here in case anyone uses the forum search function.

Query1 = the data I am working from.

Create a count for each variation on the text string and order by that count descending (Query2):

Select out the first text encountered in Query2 (this is Query3):

At that point I was actually done for one of the five fields that I wanted the mode for. That field consisted of data typed into a form. The other four were selected from a drop-down list in the same form, and thus had a defined domain. In that case, Query3 actually reports just the ID number for that field from the table. This requires one more Query to exchange the ID number for the text from the table (Query4):


Now, I'm sure that there are much neater and cleaner ways to do that. (Also I think a couple of those AS clauses are just there to rename the column heading...) Regardless, it's what I did, and I hope this is helpful to someone at some point.

EDIT: After a little more digging I'm not sure that this is working as intended. Query3 isn't necessarily grabbing the earlier records in Query2, which would be the more common ones. I'll revisit it later.

Use max not first

Query1

Group by stableid and variabletext and count variabletext

Query2 takes query1

Groupby stableid and max countofvariabletext

Query3

Joins the 2 queries on stableid and count fields to select all 3 fields required from query1

Brian

Brianwarnock said:

Group by stableid and variabletext and count variabletext
. . .
Groupby stableid and max countofvariabletext

Click to expand...

I'm not sure what you mean by this. Access gives an error:

Cannot have aggregate function Count/Max in GROUP BY clause xxxxxxxxxxxx.

Click to expand...

For the former, I already have a column in the datasheet view from my SELECT line called 'TotalCount' from the line:


For the latter, I can replace my with BUT, there is a serious difference. The First() function grabs the first string it comes across. The Max() function only takes numeric data, so instead of feeding the function the VariableText strings, I am instead feeding it the count of how many times those strings occur. Which means I need to somehow grab the associated text after ordering it or something like that, which is puzzling at this time of morning.

I was giving you the Whole picture rather than picking it up part way through so thae fact that you have done quer 1 is irrelevent.

quer3 is where you pick up the other text required and as I believe you have discovered First does not always work as expected, the method I have shown is a standard approach to this type of query.

Brian

Forgive the shorthand for your field names but here are the 3 queries

SELECT [tablename].sid, [tablename].vt, Count([tablename].vt) AS CountOfvt
FROM [tablename]
GROUP BY [tablename].sid, [tablename].vt;

SELECT query1.sid, Max(query1.CountOfvt) AS MaxOfCountOfvt
FROM query1
GROUP BY query1.sid;

SELECT query1.sid, query1.vt, query1.CountOfvt
FROM query1 INNER JOIN query2 ON (query1.CountOfvt = query2.MaxOfCountOfvt) AND (query1.sid = query2.sid);


Brian

That's absolutely fabulous and all my queries are working great and my overall report looks good now too!

Curiously, as a side effect, if there is a tie for the Max() value then the INNER JOIN keeps multiple copies - anything with an equivalent value to the Max() value returned.

I guess I could go back to using First() (post-Max()) to trim those down for the fields that aren't critical.

Thanks again for the help!

EDIT: I was creating this series of queries to find the modes of 5 different fields in a larger query. For only 1 of them I added an extra query at the end using First() as I mentioned. It nicely trimmed out the duplicate records since that particular field is more suggestive than definitive. Using Max() followed by First() is the way to go, IMHO.

Thanks for the feedback. I have never come across the tie situation so had not thought about what would happen, glad you sorted that.


Brian