Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-09-30
10
Medium Priority
?
310 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 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 52

Accepted Solution

by:
Gustav Brock earned 2000 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 52

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

916 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