I've accomplished this with a series of queries, which I'll post here as they may be informative to someone else trying to find the MODE for strings. StableID represents the identifier that remains constant - that you'll be grouping by, and VariableText represents the strings that you're trying to find the MODE for.
For data which exists in Table1, first count how many times each string is associated with each record:
Query1:
Code:
SELECT [Table1].[StableID], [Table1].[VariableText], Count([Table1].[VariableText]) AS TotalCount
FROM [Table1]
GROUP BY [Table1].[StableID], [Table1].[VariableText]
ORDER BY [Table1].[StableID];
Then get the Max() values from your count:
Query2:
Code:
SELECT [Query1].[StableID], Max([Query1].[TotalCount]) AS maxVALUE
FROM [Query1]
GROUP BY [Query1].[StableID];
Then use an INNER JOIN to trim out anything that doesn't have a Max() value:
Note - my field values are defined by Table2, so since this query reports the IDs I convert them back to the strings by an INNER JOIN.
Query3:
Code:
SELECT [Query1].[StableID], [Table2].[VariableText]
FROM [Table2] INNER JOIN ([Query1] INNER JOIN [Query2] ON ([Query1].[StableID] = [Query2].[StableID]) AND ([Query1].[TotalCount] = [Query2].[maxVALUE])) ON [Table2].ID = [Query1].[VariableText];
This gives me the MODE for any strings that don't tie with another string for how many times it appears. I didn't want multiple records, and for my purposes I just wanted one of the most common strings. To eliminate "duplicates" I used First():
Query4:
Code:
SELECT [Table1].[StableID], First([Query3].[VariableText]) AS trimTEXTFROM [Query3] INNER JOIN [Table1] ON [Query3].[StableID] = [Table1].[StableID]
GROUP BY [Table1].[StableID];
Be aware that First() behaves oddly and it's a gamble which string gets plucked by it. I hope this helps. Credit goes to Brianwarnock at http://www.access-programmers.co.uk/forums/ for walking me through it!