Results 1 to 4 of 4
  1. #1
    nalgarryn's Avatar
    nalgarryn is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2013
    Location
    Vancouver, BC
    Posts
    3

    Question Finding a MODE for text entries (multiple times, for selections of records)

    Hi,

    I'm trying to do something a little bit difficult. We have an Access parking database. Unfortunately I inherited it and it's a bit messy. Vehicles are generally identifiable by their license plate but the text entered for their description may be missing or wrong. For example, one license plate may appear on several violation entries but have the model missing from one and appear as 'Black' three times, 'Grey' once, and 'Brown' once (no, seriously).

    The end result is to generate an Excel-style table which lists license plates and selects out the most common value (the MODE) from text descriptions for a variety of fields (make, model, colour, violation location, etc). So the vehicle I just mentioned would have the model showing and 'Black' as the colour.

    To make it extra difficult, I'm not doing this for the entire database, only for license plates that appear within a query (violations > n), so I want to use the query results as a selection to do the work on.

    I've tried a few things, but I can't seem to get a Query which returns a text MODE (Honda for make for our entire database) to return a value for each license plate, and only using data from records with that particular plate. I can't create a query for each plate in the table and I'm suspecting I'll need a macro for the 'for each' style functioning.



    Any help would be wonderful and I'm sorry if I'm unclear.

    Edit:
    This is the query I'm using to get a text MODE.
    Code:
    SELECT TOP 1 Table1.Data
    FROM Table1GROUP BY Table1.DataORDER BY Count(Table1.Data) DESC;
    I need to be able to dynamically select the .Data (because I have 4 or 5 Table1.Data values I want to find the MODE of) and only for a certain selection of records for each one, as determined by a common field value (License Plate, which occurs in Table1, but the selection I want is actually from Query1).

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Your query has a syntax error

    Try
    SELECT TOP 1 Table1.Data FROM Table1
    GROUP BY Table1.Data
    ORDER BY Count(Table1.Data) DESC;
    Also it might be better if you showed some sample data and sample result.

  3. #3
    nalgarryn's Avatar
    nalgarryn is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2013
    Location
    Vancouver, BC
    Posts
    3
    Okay, I tried what you suggested. It gave me a query result with only one record and the most common value in the whole table.

    What I need is the most common value for each <StableID>.


    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)

    Here is the code:
    Code:
    SELECT [Query1].[StableID], [Query1].[VariableText], Count([Query1].[VariableText]) AS TotalCount
    FROM [Query1]
    GROUP BY [Query1].[StableID], [Query1].[VariableText]
    ORDER BY Count([Query1].[VariableText]) DESC;


    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.

    Thanks for your help.

  4. #4
    nalgarryn's Avatar
    nalgarryn is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2013
    Location
    Vancouver, BC
    Posts
    3

    Thumbs up

    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!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-04-2012, 03:52 AM
  2. Not able to make multiple entries (records) in the table
    By ramindya in forum Database Design
    Replies: 3
    Last Post: 03-04-2012, 12:53 PM
  3. Finding and counting (not records but entries)
    By gpbanseo in forum Queries
    Replies: 3
    Last Post: 07-14-2011, 04:35 AM
  4. Replies: 3
    Last Post: 05-25-2010, 02:16 PM
  5. Replies: 5
    Last Post: 12-10-2009, 10:33 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums