Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    2

    Unanswered: Counting unique dates in a list

    I have a table in Access that tracks people conducting interviews with clients. I track the length of the interviews with the following expression in a query: "IntDuration: DateDiff("n",[IntStartTime],[IntEndTime])".
    I'd like to be able to calculate the average length of interview witin a given time period (a month, usually, entered as a parameter) by dividing the total interview minutes by the number of dates. The problem is that because there are more than one interviews in one day, I have duplicate values in the date field. How can I count the number of unique days so I can use that figure to divide the total interview minutes by?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you use the COUNT(DISTINCT...) aggregate function if you set the database to use ANSI:92 syntax. Of course, I can't find where to do this in 2007! If this is a mature database with lots of SQL written though you will want to be careful doing this. Another option is to create a derived table that selects the DISTINCT dates and then perform a COUNT() on that.

  3. #3
    Join Date
    Jun 2010
    Posts
    2
    Thank you for responding. I'm not that familiar with writing queries in SQL view-I've always used Access' query design view to do it: could you show me how to do this?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Counting distinct in Access SQL (not using the ANSI:92 option):
    Code:
    SELECT COUNT(*) AS count_of_distinct
    FROM (SELECT DISTINCT myCol FROM myTable) AS distinct_list_of_values

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
  •