Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Averages: Queries (97 SR2)

    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
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Averages: Queries (97 SR2)

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

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Averages: Queries (97 SR2)

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

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Averages: Queries (97 SR2)

    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 ... <img src=/S/aflame.gif border=0 alt=aflame width=15 height=15>) <img src=/S/wink.gif border=0 alt=wink width=15 height=15> perhaps you can clarify a bit? <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Averages: Queries (97 SR2)

    Drk <img src=/S/smile.gif border=0 alt=smile width=15 height=15>,

    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

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Averages: Queries (97 SR2)

    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");
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Averages: Queries (97 SR2)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Averages: Queries (97 SR2)

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

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Averages: Queries (97 SR2)

    How might I reverse the month order?

    Almost there, thanks millions!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Averages: Queries (97 SR2)

    <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    Descending... Errr....

    Thanks!

    -K
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •