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

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
LVL 1
filtrationproductsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
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
filtrationproductsAuthor Commented:
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
filtrationproductsAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

filtrationproductsAuthor Commented:
Forgot to update file.
ReportTest.accdb
0
Rey Obrero (Capricorn1)Commented:
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
Gustav BrockCIOCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
filtrationproductsAuthor Commented:
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
Gustav BrockCIOCommented:
ISO Week numbering is used outside the US for unique combo of yyyy-ww.

/gustav
0
filtrationproductsAuthor Commented:
Gustav,

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

Thanks again for your help.
Dan
0
Luke ChungPresidentCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.