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?
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.