Solved

Access:Average Year to Date in Query

Posted on 2007-11-27
4
1,147 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 500 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now