Need to Run Query 'For Each' Field Value (1 Viewer)

[nalgarryn]

I have a MODE query that I am trying to reference in another query.

SELECT TOP 1 [Query1].[VariableText], [Query1].[StableID]
FROM [Query1]
GROUP BY [Query1].[VariableText], [Query1].[StableID]
ORDER BY Count([Query1].[VariableText]) DESC;

I have a query, Query1, which has two columns. A StableID appears multiple times in one column and there is variable text that occurs in the column next to it. I want to select the mode for the variable text (the most common value) and return it for another query.

If there is only one value, or if there are only two which are different (no mode value) then returning the first one encountered is fine.

My problem is that I want it done once for each StableID, not just once for the whole dataset.

Thanks for any help!

 

[plog]

Your SQL is a little contradictory--your SELECT clause references fields from Query1, but your FROM clause references [Find Unpaid Triplets]. Because your explanation says Query1 is the one to use, I used that.

To get what you want, you first need to create a sub-query to count the total amount of times each VariableText/StableID permutation appears. This is the SQL for that:

 SELECT VariableText, StableID, COUNT(StableID) As TotalCount
FROM Query1
GROUP BY VariableText, StableID;

Save that as 'subModeQuery'. Then use it to build another query using this SQL:

SELECT StableID, First(VariableText) As VariableText
FROM subModeQuery
WHERE TotalCount = DMAX("[TotalCount]", "subModeQuery", "[StableID]=" & StableID); 
GROUP BY StableID;

That will give you all the VariableText values that appear most frequently for each StableID.

 

[nalgarryn]

The first part works like a charm, thanks for your help. The contradiction I made was due to not replacing the actual name of my queries with generic names, which I simply have to do because my database has confidential information in it.

When I tried using the second block of code to reference the first query, I get this error, "The LEVEL clause includes a reserved word or argument that is misspelled or missing, or the punctuation is incorrect."

Access Help isn't, merely saying that I "entered an SQL statement that has an invalid reserved word clause." But I am pretty sure that this is because of the semicolon at the end of the third line.

When I take that out, I get a different error (of course):

Syntax eror (missing operator) in query expression 'Query1.StableID=content'

Where content is clearly one of the StableIDs from a record in the database.

Here is my current code, and I'm not sure if I need it or not, but I used an inner join so I can reference StableIDs directly from Query 1 instead of the subModeQuery. I actually have 5 different subModeQuerys, so I think it would be more elegant to reference them all from Query1 (which is the base of data I am querying).

SELECT [Query1].[StableID], First([subModeQuery].[VariableText]) As "VariableText"
FROM [subModeQuery] INNER JOIN [Query1] ON [subModeQuery].[StableID] = [Query1].[StableID]
WHERE TotalCount = DMAX("[TotalCount]", "subModeQuery", "[Query1].[StableID]=" & [Query1].[StableID]) 
GROUP BY [Query1].[StableID];

If you can find my syntax error (missing operator) I'd appreciate it!

 

[plog]

Is StableID a text or numeric field? The query you have now treats it as numeric, if it is text you need to escape the field name with quote marks:

"[Query1].[StableID]='" & [Query1].[StableID] & "'")