Results 1 to 6 of 6

Count Unique Fields and then get Average

  1. #1
    Kirsti is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Location
    New Zealand
    Posts
    172

    Count Unique Fields and then get Average

    Hi,

    I am having trouble writing a query and need some help.

    I have a table that has a list of addresses. The addresses can be duplicated based on other criteria.

    What I need to achieve is to write a query that gives me the Average number of times each address appears in the table, and I'm totally stuck.

    Any help would be much appreciated,

    Thanks
    Kirsti

  2. #2
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    26,920
    You can do an aggregate (GROUP BY) query and count how many times the address appears in the database. "Average" doesn't make sense to me.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    Aragan is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    22
    Quote Originally Posted by June7 View Post
    You can do an aggregate (GROUP BY) query and count how many times the address appears in the database. "Average" doesn't make sense to me.
    Yeah, something like:

    SELECT Address, COUNT(*)
    FROM Table
    GROUP BY Address

  4. #4
    Kirsti is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Location
    New Zealand
    Posts
    172
    Hi,

    Thanks for your responses, however I don't think I have explained myself properly.

    I don't actually need to count the number of unique entries, I need to count the number of times each unique entry appears in the table, and then get the average number of times it appears.

    e.g.

    ADDRESS
    1 Smith Street
    2 Brown Street
    3 Jones Street
    1 Smith Street
    3 Jones Street
    1 Smith Street

    I want to count that "1 Smith Street" appears 3 times, "2 Brown Street" appears once & "3 Jones Street" appears twice. Therfore the average number of times that an address appears is twice.

    I hope that makes it clearer - thanks again for your help.

    Kirsti

  5. #5
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    26,920
    You could do a report using Grouping & Sorting with Aggregate calcs to easily present this data manipulation.

    The report would allow presentation of detail records as well as show the summary calcs.

    If you just want to open a query and see this, build the GROUP BY query as described, open in Datasheet view and click the Totals button. This will open a Totals row at bottom of the query. Select the Avg aggregate function under the CountOfAddress column.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  6. #6
    Kirsti is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Location
    New Zealand
    Posts
    172
    Works perfectly. Thanks!

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

Similar Threads

  1. Unique queries - Unique Count
    By bigfish in forum Queries
    Replies: 7
    Last Post: 03-14-2012, 12:28 PM
  2. Count unique records - no duplicates
    By Kevo in forum Queries
    Replies: 4
    Last Post: 08-15-2011, 12:19 AM
  3. Count Unique Records
    By PonderingAccess in forum Queries
    Replies: 2
    Last Post: 08-19-2010, 05:54 AM
  4. Replies: 13
    Last Post: 05-28-2010, 10:57 AM
  5. Count of Unique Values
    By vsmk in forum Queries
    Replies: 2
    Last Post: 03-13-2010, 10:07 PM

Posting Permissions

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