Solved

Access:Average Year to Date in Query

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

808 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