MS Access Weekly query

3/30/2017

3/29/2017

I'm not sure where you are going with this so I just made a sample from one of my tables.

SELECT Year([LastChangeDT]) AS ChngYear, Format([LastChangeDT],"ww") AS ChngWeek, Count(*) AS Expr1
FROM tblChangeDT
GROUP BY Year([LastChangeDT]), Format([LastChangeDT],"ww");

This query returns:

3/29/2017

ASKER

Thank you. How does "ww" define a week?

ASKER CERTIFIED SOLUTION

3/29/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

SOLUTION

3/29/2017

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

3/29/2017

Gus' function returns 2017W13 for today's date.

3/30/2017

Problem is, that Format and DatePart don't always get week 53 right. Also, both week 1 and 52/53 can belong to either a calendar year before or after New Year. Thus, you need that year part as well to sort and group correctly when data spans calendar years.

/gustav

3/30/2017

That's why I included Year in my query example. Since year does not divide cleanly into weeks, it is necessary to decide how to handle the first week of the year when it doesn't start on the first day of the week and that also impacts the definition of the Last week of the year. The ISO method may be what Tamura wants but in this case, the correct answer is actually what satisfies the business users.

3/30/2017

Who knows? Questioneer is silent.

/gustav

3/30/2017

ASKER

Thank you, all. I'm trying to add ISO method.
is this the right way?

3/30/2017

Yes.