Grouping weeks within years

[BDibz]

Hello,



I have a dataset that spans a few years of revenue and relevant data. I want to create a chart that shows the revenue per week.

I currently have a query field called "week" that gives the week number for the date. However, there are multiple data points for each week number since each year has the same week numbers, so I can't group by week number. I need to find a way to group the weeks within the years, and can't think of an effective way to do this.

Here's a picture of my Weekly Revenue Query. The WHERE query for the date field is linked to a radio-button selector that just allows you to select which year you're viewing the weeks by, which is my current work-around. Ideally, I want to see all of the data, in order, by week.

[ranman256]

make a sort field: YEAR & "-" & week

[BDibz]

make a sort field: YEAR & "-" & week

What is a sort field? And for year and week, would these be [Year] and [Week] from my query?

[June7]

Don't need DatePart to extract year. Year() function will do that, as you have done in criteria.

Don't understand the issue. You already have Year and Week in the grouping criteria. This should accomplish what you want without creating another field. If you want all years retrieved then don't apply filter and don't include the Date field. If you do want to see all the raw data then build a report and use its Sorting & Grouping feature with aggregate calcs in group section textboxes instead of the GROUP BY query. Report allows display of detail records as well as summary data.

Date and Year are reserved words (they are functions). Should not use reserved words as names for anything.

Also should not use spaces nor punctuation/special characters (underscore is only exception) in any names.

[BDibz]

Don't need DatePart to extract year. Year() function will do that, as you have done in criteria.

Don't understand the issue. You already have Year and Week in the grouping criteria. This should accomplish what you want without creating another field. If you want all years retrieved then don't apply filter and don't include the Date field. If you do want to see all the raw data then build a report and use its Sorting & Grouping feature with aggregate calcs in group section textboxes instead of the GROUP BY query. Report allows display of detail records as well as summary data.

Date and Year are reserved words (they are functions). Should not use reserved words as names for anything.

Also should not use spaces nor punctuation/special characters (underscore is only exception) in any names.

This all works fine in the query, yes, but when I create the chart, it just groups by Week since I can only put one assign one field to the x-axis. So, it will display weeks 1-52 along the x-axis, but it is summing up that week's values across all of the years. So I need to create some type of axis label that makes each week of each year a unique value.

[June7]

Okay, if you want a chart to display multiple years, then yes, calculate another field that returns the Year and Week as a compound value and use that field in the chart design. Otherwise, apply filter criteria as you show in posted query and that should return only a single year of data.

That sounds like a very long x-axis. How many years?

[BDibz]

Once I set it up to allow for it to span multiple years or cross over years (IE if I want to go back 6 months, and its only April, I need to cross the year line and still keep my weeks in order) I will set up a text-box input on my form that says "show last ___ weeks" and use that as an input for the criteria on the query.

Would it work to just concatenate the year and week values into a year-week field and use that? Seems pretty simple. I'll try that.

[June7]

Actually, 'show last x weeks' criteria is probably going to get rather complicated.

[isladogs]

Actually, 'show last x weeks' criteria is probably going to get rather complicated.

Sort by Year-Week field in descending order and select top x
That will work if the YearWeek field is e.g. 201815, 201803, 201751 etc

[BDibz]

Actually, 'show last x weeks' criteria is probably going to get rather complicated.

On my first attempt at this DB, I managed to do a rolling chart for the revenue that showed the last 'x' days:



Using the following query criteria:

Between Date() And Date()-[Forms]![Dashboard].[txtDayCount]

Which I would consider pretty clever, but I stole it from somewhere on the internet. Anyways, I figure I can do the same thing but with my concatenated field, lets call it WeekYear. So

between Week(Date())&Year(Date()) And Week(Date())&Year(Date()) - [Forms]![Dashboard].[WeekYear]

However, that would only work if SQL will interpret the WeekYear data as integers or dates or something with a value attached so that the Between function works... which I'm not sure if it will. What do you think?

[BDibz]

Can anyone tell me why Access seems to think 20182 is bigger than 201910, and how I could fix this?

Edit: Fixed with Val(expression)