Solved

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

Posted on 2014-09-30
10
307 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
[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
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40353153
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
ID: 40353195
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
ID: 40353206
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:filtrationproducts
ID: 40353219
Forgot to update file.
ReportTest.accdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40353270
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
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40354058
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
ID: 40354517
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 50

Expert Comment

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

/gustav
0
 
LVL 1

Author Comment

by:filtrationproducts
ID: 40354556
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:Luke Chung
ID: 40354678
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

710 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