Solved

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

Posted on 2014-09-30
10
308 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 51

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 51

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month8 days, 21 hours left to enroll

615 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