Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access:Average Year to Date in Query

Posted on 2007-11-27
4
Medium Priority
?
1,210 Views
Last Modified: 2010-04-21
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

0
Comment
Question by:ouestque
  • 2
  • 2
4 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 20361557
Not sure how you got your sql to work.  Looks like something from another query.
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]+[late])*100,"Fixed") AS [Percent Delivery On-Time for month], Sum(a.late) AS [Late Deliveries], DSum("ontime","deliveries","datevalue(monthandyear) <= #" & [a].[monthandyear] & "#")/(DSum("ontime","deliveries","datevalue(monthandyear) <= #" & [a].[monthandyear] & "#")+DSum("late","deliveries","datevalue(monthandyear) <= #" & [a].[monthandyear] & "#")) AS Year_To_Date_On_Time
FROM Deliveries AS a
GROUP BY a.monthandyear, Format([ontime]/([ontime]+[late])*100,"Fixed"), DSum("ontime","deliveries","datevalue(monthandyear) <= #" & [a].[monthandyear] & "#")/(DSum("ontime","deliveries","datevalue(monthandyear) <= #" & [a].[monthandyear] & "#")+DSum("late","deliveries","datevalue(monthandyear) <= #" & [a].[monthandyear] & "#"));
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 2000 total points
ID: 20361568
OK, fixed formatting.
I had Monthandyear as a text field, hence the datevalue.

SELECT a.monthandyear, Sum(a.ontime) AS [Delivery On-Time], Format([ontime]/([ontime]+[late])*100,"Fixed") AS [Percent Delivery On-Time for month], Sum(a.late) AS [Late Deliveries], Format(DSum("ontime","deliveries","datevalue(monthandyear) <= #" & [a].[monthandyear] & "#")/(DSum("ontime","deliveries","datevalue(monthandyear) <= #" & [a].[monthandyear] & "#")+DSum("late","deliveries","datevalue(monthandyear) <= #" & [a].[monthandyear] & "#"))*100,"Fixed") AS Year_To_Date_On_Time
FROM Deliveries AS a
GROUP BY a.monthandyear, Format([ontime]/([ontime]+[late])*100,"Fixed"), Format(DSum("ontime","deliveries","datevalue(monthandyear) <= #" & [a].[monthandyear] & "#")/(DSum("ontime","deliveries","datevalue(monthandyear) <= #" & [a].[monthandyear] & "#")+DSum("late","deliveries","datevalue(monthandyear) <= #" & [a].[monthandyear] & "#"))*100,"Fixed"), DateValue([monthandyear])
ORDER BY DateValue([monthandyear]);
0
 

Author Comment

by:ouestque
ID: 20404956
Awesome Jerry thanks!!!
0
 

Author Closing Comment

by:ouestque
ID: 31411295
Increadible. Jerry had just the solution I needed. Thanks again.!
0

Featured Post

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question