Date Averages: Queries (97 SR2)

Topic

New Reply

I’ve got a field that contains the date a project was started, and another for the date the project was completed.

I can get the average of these by using “Avg([DateComplete]-[DateStarted]”
Simple enough.

How do I build a query that lists the Average Time per month, and lists it in Month format? The query’s result would be two columns, one for Month, one for Average Time.

Thanks for all your help!

-K

Reply | Quote

Replies

My first thought (untested though it may be) is have you tried a crosstab query?

Reply | Quote

What do you mean by “lists it in month format”? If you just want to see the average for each month, create a GroupBy (Totals) query and group by the month and year of whichever date is appropriate. Create an expression using your average, and that should give you the results you want.

Reply | Quote

Listed in Month Format:

January 12.2
February 9.3
March 4.2
….

I’m sorry Charlotte, but i’m having some problems with your directions… (Here you go … ) perhaps you can clarify a bit?

Reply | Quote

Drk ,

If I understand your problem, it sounds like you want to average all the durations that end in a given month (or start in a given month?). If so, then I’d include a field in your query something like dteMonth:dateserial(year(dteEnd),Month(dteEnd),1). Then group on dteMonth and avg(dteEnd-dteStart). The average, of course, will be the average number of days between the start and end. If you want the average number of months, then divide the result by 365.25/12 (an approximation!). If you want to display the months spelled out then use format(dteMonth,”mmmm”)

Tom

Reply | Quote

Here’s a sample of a query that does what I think you’re trying to do. This one was based on the Orders table from the Northwind database.

SELECT Format([ShippedDate],”mmmm”) AS [Month],
Avg([ShippedDate]-[OrderDate]) AS Days
FROM Orders
WHERE ((([ShippedDate]) Is Not Null))
GROUP BY Format([ShippedDate],”mmmm”);

Reply | Quote

Charlotte’s solution will group months of different years together (e.g., you’ll get a composite average for January 2000 and January 2001 together, if your dates span more than the last 12 months). If you want to differentiate months from different years, then specify “mmmm yyyy” in the format functions.

Also, if you want the months to list in chronological order, add DateSerial(Year([ShipDate]),Month([ShipDate]),1) to the GROUP BY and to the ORDER BY clauses.

So, your query might look something like:

SELECT Format([ShipDate],”mmmm yyyy”) AS [Month], Avg([ShipDate]-[OrderDate]) AS Days
FROM Orders
WHERE ([ShipDate] Is Not Null)
GROUP BY DateSerial(Year([ShipDate]),Month([ShipDate]),1), Format([ShipDate],”mmmm yyyy”)
ORDER BY DateSerial(Year([ShipDate]),Month([ShipDate]),1);

Finally, if you want average months rather than average days you can get close by substituting “Avg([ShipDate]-[OrderDate]) AS Days” with “Avg([ShipDate]-[OrderDate])/(365.25/12) AS Months” in the above query.

Tom

Reply | Quote

FYI, it wasn’t a solution, it was a direction to start in. There wasn’t enough information posted in the question to provide a solution.

Reply | Quote

How might I reverse the month order?

Almost there, thanks millions!

Reply | Quote

Descending… Errr….

Thanks!

-K

Reply | Quote