CODEWITHSUNDEEP
sqlms-access
user2059972
user2059972

Reputation: 151

MS Access SQL for Week by Week Trending Chart

I'm trying to create a chart in Access 365 that will show the trend of Opened Sales orders over the past 4 weeks. I have a table that contains all of the orders with a unique "Opening Date" so that part is fairly easy. The part I'm having an issue with is getting the chart to display to display "Week of MM/DD" along the X axis as a result of the SQL query. I currently have:

SELECT 
     SalesTable.Sales_Number, 
     SalesTable.OpeningDate, 
     ((DatePart("ww",[SalesTable.OpeningDate])- DatePart("ww",Date()))) AS WeekNum
FROM SalesTable
WHERE ((SalesTable.OpeningDate) Between DateAdd("d", -28, Date()) And Date())
GROUP BY SalesTable.OpeningDate, SalesTable.Sales_Number;

But the issue is that I get the X axis label displaying "-4 / -3 / -2 / etc..." displaying the weeks back form the current date instead of Displaying "Week of 4/19" as the last week. I cant quite figure out how to represent that in SQL.

Upvotes: 0

Views: 93

Answers (1)

Gustav
Gustav

Reputation: 55816

Not sure what you mean, as a weeknumber is not a "week of a mm/dd" but a "week of a year".

But try with:

"Week of " & Format(DateAdd("ww, -DateDiff("ww",[SalesTable.OpeningDate], Date()), Date()), "mm/dd") AS WeekNum

Upvotes: 0

Related Questions