Results 1 to 6 of 6
  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 VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  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 VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  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, 01:28 PM
  2. Count unique records - no duplicates
    By Kevo in forum Queries
    Replies: 4
    Last Post: 08-15-2011, 01:19 AM
  3. Count Unique Records
    By PonderingAccess in forum Queries
    Replies: 2
    Last Post: 08-19-2010, 06:54 AM
  4. Replies: 13
    Last Post: 05-28-2010, 11:57 AM
  5. Count of Unique Values
    By vsmk in forum Queries
    Replies: 2
    Last Post: 03-14-2010, 12:07 AM

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