ouestque
asked on
Access:Average Year to Date in Query
I have a table(Name: "Deliveries", Columns: "Ontime", "Late", "Month", "Year"
When a delivery is ontime it is labled 1, when it is late it is labled 0
The SQL below lists each month, number of deliveries ontime, number deliveries late, as well as the number of average deliveries on-time for each month. How do I add Average year to date to the mix?
Current CODE:
SELECT Deliveries.MonthandYear, Sum(Deliveries.ontime) AS [Delivery On-Time], Format(Avg([ontime]*100)," Fixed") AS [Percent Delivery On-Time for month], Sum(Deliveries.late) AS [Late Deliveries]
FROM Deliveries
GROUP BY Deliveries.MonthandYear, Deliveries.year_thru, Deliveries.month_thru
ORDER BY Deliveries.year_thru, Deliveries.month_thru;
Current Table Looks Like:
Field: Month Delivery On-Time Delivery Late Percent Delivery On-Time for month
Row1 August 2006 53 20 72.60
Row2 September 2006 30 10 75.00
Row3 October 2006 60 5 92.31
Row4 November 2006 20 3 86.96
Row5 December 2006 15 1 93.75
I NEED THE TABLE TO LOOK LIKE THIS:
Field: Month Delivery On-Time Delivery Late Percent Delivery On-Time for month YearToDate
Row1 August 2006 53 20 72.60 72.60
Row2 September 2006 30 10 75.00 73.45
Row3 October 2006 60 5 92.31 80.34
Row4 November 2006 20 3 86.96 81.09
Row5 December 2006 15 1 93.75
When a delivery is ontime it is labled 1, when it is late it is labled 0
The SQL below lists each month, number of deliveries ontime, number deliveries late, as well as the number of average deliveries on-time for each month. How do I add Average year to date to the mix?
Current CODE:
SELECT Deliveries.MonthandYear, Sum(Deliveries.ontime) AS [Delivery On-Time], Format(Avg([ontime]*100),"
FROM Deliveries
GROUP BY Deliveries.MonthandYear, Deliveries.year_thru, Deliveries.month_thru
ORDER BY Deliveries.year_thru, Deliveries.month_thru;
Current Table Looks Like:
Field: Month Delivery On-Time Delivery Late Percent Delivery On-Time for month
Row1 August 2006 53 20 72.60
Row2 September 2006 30 10 75.00
Row3 October 2006 60 5 92.31
Row4 November 2006 20 3 86.96
Row5 December 2006 15 1 93.75
I NEED THE TABLE TO LOOK LIKE THIS:
Field: Month Delivery On-Time Delivery Late Percent Delivery On-Time for month YearToDate
Row1 August 2006 53 20 72.60 72.60
Row2 September 2006 30 10 75.00 73.45
Row3 October 2006 60 5 92.31 80.34
Row4 November 2006 20 3 86.96 81.09
Row5 December 2006 15 1 93.75
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome Jerry thanks!!!
ASKER
Increadible. Jerry had just the solution I needed. Thanks again.!
But, try this.
Did not format the percents as you would like them, but
SELECT a.monthandyear, Sum(a.ontime) AS [Delivery On-Time], Format([ontime]/([ontime]+
FROM Deliveries AS a
GROUP BY a.monthandyear, Format([ontime]/([ontime]+