Solved

query to average weekly data

Posted on 2008-10-31
2
227 Views
Last Modified: 2012-05-05
Experts,
I'm trying to create a date parameter query for a line chart that:
1) Based upon the date range create a line chart for each week ending on a Friday where it returns an average for the week. For example, a date range of 6/1/08 - 6/30/08 would return the following:
06/08/08      750
06/13/08      655
06/20/08      600
06/27/08      825
I started with this:

SELECT tblGrossStrokesPerHour.GSPHDate, tblGrossStrokesPerHour.GrossStrokesPerHour, tblGrossStrokesPerHour.LineCode
FROM tblGrossStrokesPerHour
WHERE (((tblGrossStrokesPerHour.GSPHDate) Between [Start Date] And [End Date]) AND ((tblGrossStrokesPerHour.LineCode)=8))
ORDER BY tblGrossStrokesPerHour.GSPHDate;

I attached the a database
 
gsph.zip
0
Comment
Question by:Frank Freese
[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 Comments
 
LVL 22

Accepted Solution

by:
Flyster earned 500 total points
ID: 22853233
I didn't have time to try this on your DB. See if this works for you:

SELECT Avg(tblGrossStrokesPerHour.GrossStrokesPerHour) AS AvgOfGrossStrokesPerHour, tblGrossStrokesPerHour.LineCode, Format([GSPHDate],"ww",7) AS WeekNumber, Max([GSPHDate]) AS WeekEnding
FROM tblGrossStrokesPerHour
GROUP BY tblGrossStrokesPerHour.LineCode, Format([GSPHDate],"ww",7)
HAVING (((tblGrossStrokesPerHour.LineCode)=8) AND ((Max([GSPHDate])) Between [Start Date] And [End Date]));

Flyster
0
 

Author Closing Comment

by:Frank Freese
ID: 31512152
You nailed it! Many thanks!!!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

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…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

740 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