filtrationproducts
asked on
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
Does anyone know how I could accomplish this? I have attached the access database.
Thanks in advance!
Dan
ReportTest.accdb
ASKER
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
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
ASKER
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
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
ASKER
Forgot to update file.
ReportTest.accdb
ReportTest.accdb
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_DA TE]) 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
SELECT admin_SOTD.ORDERED_QTY, admin_SOTD.REQ_SHIP_DATE, admin_SOTD.UNIT_PRICE, [ORDERED_QTY]*[unit_price]
FROM admin_SOTD
WHERE (((DatePart("ww",[REQ_SHIP
ORDER BY admin_SOTD.REQ_SHIP_DATE
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ISO Week numbering is used outside the US for unique combo of yyyy-ww.
/gustav
/gustav
ASKER
Gustav,
I am not using ISO Week numbering so that should work fine for me.
Thanks again for your help.
Dan
I am not using ISO Week numbering so that should work fine for me.
Thanks again for your help.
Dan
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.
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.
SELECT admin_SOTD.ORDERED_QTY, admin_SOTD.REQ_SHIP_DATE, admin_SOTD.UNIT_PRICE, [ORDERED_QTY]*[unit_price]
FROM admin_SOTD
WHERE (((DatePart("ww",[REQ_SHIP
ORDER BY admin_SOTD.REQ_SHIP_DATE;
.