Solved

query to average weekly data

Posted on 2008-10-31
2
224 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
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

920 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

17 Experts available now in Live!

Get 1:1 Help Now