?
Solved

Access:Average Year to Date in Query

Posted on 2007-11-27
4
Medium Priority
?
1,192 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 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