To

[chelseacraft]

I have a query that shows the number of days elapsed between a start date and stop date using the DateDiff function. What I need now is to find the total number of days per each month (NOT a running total). I also need to return the average elapsed days for each month. This needs to be done in a query, NOT a report, so I can chart it and data will stay 'evergreen'. Can anyone point me in the right direction? This would be of great help to the patients at the charity hospital I work at and be a really good bit of karma for the person who can assist. Thanks!

 

[PHV]

Take a look at the aggregate functions Sum, Count, Avg, ... and at the GROUP BY clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[chelseacraft]

Thank you for your tip. What I am looking for is a way for these functions to recognize a date range from the test date field and total or average only the days elapsed for that month. Grouping the records does not account for how to return calculations for a specific individual month. I believe I need some assistance using dates in a query to go forward.

 

[PHV]

And what have you so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

I am totally lost with this request. Perhaps some sample records and desired output would help us figure out what you want.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[chelseacraft]

OK... let's see if this helps. It's really very simple. Excel can do this in a flash, but doesn't automatically update itself based on input in the form we have in the database. So we must do this in Access to save days of work each month. Below is a sample query from a database I called Widget Production Database. You will see the number of units produced each week of the month. What I need is a formula that will deliver the average number of units produced EACH month. Not a running total. A running total will add the first month's totals into the second months. That would not give me the true average for the second month. A running total would give me the average of both Jan. and Feb. So, for the month of January the avg. is 10. The number 10 should appear in the Monthly Avg. field four times - once for each weekly record of that month.

ProductionWeek TotalUnitsProduced Monthly Avg
1/ 1/2006 8 ? (10)
1/ 7/2006 10 ? (10)
1/15/2006 15 ? (10)
1/30/2006 7 ? (10)
2/ 1/2006 12 ?
2/ 7/2006 11 ?
2/15/2006 8 ?
2/28/2006 9 ?
3/ 1/2006 16 ?
3/ 7/2006 5 ?
3/15/2006 12 ?
3/30/2006 11 ?

Hope this helps to visualize it. And thank you all for your patience with my problem.

 

[chelseacraft]

Well... after submitting the post the columns got skewed. The numbers should be under Total Units Produced and the ? marks should be under the Monthly avg column.

 

[lespaul]

Something like this might work:

SELECT ProductionWeek, TotalUnitsProduced, (SELECT Avg(TotalUnitsProduced) From tblName T2 WHERE Month(T2.ProductionWeek) = Month(T1.ProductionWeek) Group By Month(T2.ProductionWeek)) As MonthlyAverage From tblName T

Leslie

 

[PHV]

You may try something like this:
SELECT A.ProductionWeek, A.TotalUnitsProduced, B.[Monthly Avg]
FROM yourTable AS A INNER JOIN
(SELECT Format(ProductionWeek,'yyyymm') AS ProdMonth, Avg(TotalUnitsProduced) AS [Monthly Avg]
FROM yourTable GROUP BY Format(ProductionWeek,'yyyymm')) AS B
ON B.ProdMonth = Format(A.ProductionWeek,'yyyymm')
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244