Solved

Access:Average Year to Date in Query

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
database opened as read only 10 32
Comparison query - 4 columns 9 25
rECORD SET NOT UPDATEABLE 21 59
Importing Excel file into Access 5 27
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

860 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