Solved

Accress Report - Group by week - Limit from current week to 10 weeks out

Posted on 2014-09-30
10
301 Views
Last Modified: 2014-10-01
I have an access database where I have created a report. The report has sales quantities and totals broke down by the week number. What I would like to do is limit the week numbers to include the current week through 10 weeks out.

Does anyone know how I could accomplish this? I have attached the access database.

Thanks in advance!
Dan
ReportTest.accdb
0
Comment
Question by:filtrationproducts
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
try this query


SELECT admin_SOTD.ORDERED_QTY, admin_SOTD.REQ_SHIP_DATE, admin_SOTD.UNIT_PRICE, [ORDERED_QTY]*[unit_price] AS AMOUNT, DatePart("ww",[REQ_SHIP_DATE]) AS Expr1
FROM admin_SOTD
WHERE (((DatePart("ww",[REQ_SHIP_DATE])) Between DatePart("ww",Date()) And DatePart("ww",Date())+10))
ORDER BY admin_SOTD.REQ_SHIP_DATE;


.
0
 
LVL 1

Author Comment

by:filtrationproducts
Comment Utility
I tried doing this from the query but there was one problem with it - The numbers reset at the beginning of the new year. So since it is week 40 right now it would pull 40 through 50 but if their were any records from any other years within weeks 40 and 50 it would pull them as well.

Access Reports is smart enough to know that and organize them according to year as well. I am hoping there is a way to do this within the report if possible.

Thanks!
Dan
0
 
LVL 1

Author Comment

by:filtrationproducts
Comment Utility
I was working on a Form that would calculate the dates and maybe use the form fields as query criteria but that is when I found the ability to sort by week numbers from within reports and that seemed easier.

I attached a copy of the access file that includes the form. (select the first date on the form and click generate dates. It then saves each as a date string in VB.

Thanks,
Dan
0
 
LVL 1

Author Comment

by:filtrationproducts
Comment Utility
Forgot to update file.
ReportTest.accdb
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
how about this

SELECT admin_SOTD.ORDERED_QTY, admin_SOTD.REQ_SHIP_DATE, admin_SOTD.UNIT_PRICE, [ORDERED_QTY]*[unit_price] AS AMOUNT, DatePart("ww",[REQ_SHIP_DATE]) AS Expr1
 FROM admin_SOTD
 WHERE (((DatePart("ww",[REQ_SHIP_DATE])) Between DatePart("ww",Date()) And DatePart("ww",Date())+10)) And Year([REQ_SHIP_DATE])= Year(Date())
 ORDER BY admin_SOTD.REQ_SHIP_DATE
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
Week numbers may cross calendar years, so you must use DateDiff so sort this out:

SELECT
    admin_SOTD.ORDERED_QTY,
    admin_SOTD.REQ_SHIP_DATE,
    admin_SOTD.UNIT_PRICE, [ORDERED_QTY]*[unit_price] AS AMOUNT
FROM
    admin_SOTD
WHERE
    DateDiff("ww",Date(),[REQ_SHIP_DATE]) Between 0 And 10;

Note too, that the Order By is taken out as it is of no use as all sorting must be specified in the report.

Also, if you use ISO week numbering, you are missing a parameter throughout your application.

/gustav
0
 
LVL 1

Author Comment

by:filtrationproducts
Comment Utility
Rey,

I tried the last query code you posted. But if I were to run the report in December it will limit the report to 2014 and not show me any of the sales for January/February in 2015.

Gustav,

Using the code with Datediff you posted makes it work just like I wanted. But I am not sure what you mean by ISO week numbering. And what am I missing?

Thank you both for your help!
Dan
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
ISO Week numbering is used outside the US for unique combo of yyyy-ww.

/gustav
0
 
LVL 1

Author Comment

by:filtrationproducts
Comment Utility
Gustav,

I am not using ISO Week numbering so that should work fine for me.

Thanks again for your help.
Dan
0
 
LVL 10

Expert Comment

by:LukeChung-FMS
Comment Utility
Here's a whitepaper I wrote on using a crosstab in a report to handle any 12 monthly window without having to write any VBA code:

Creating an Annual 12 Month Summary Report without VBA Code by Creatively Using a Microsoft Access Crosstab Query

It can be easily adjusted to use weeks instead of months by replacing the "mm" in the SQL string with "ww" and adjusting the PIVOT to the number of columns you want.

Hope this helps.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

772 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

10 Experts available now in Live!

Get 1:1 Help Now