We help IT Professionals succeed at work.

MS Access Weekly query

Hiroyuki Tamura
on
250 Views
Last Modified: 2017-03-30
I'm trying to create a weekly query. from Mon to Sun.
I tried format ([time],"ww") but can't understand how it works.
Comment
Watch Question

CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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:
CountByWeek.JPG
Hiroyuki TamuraField Engineer

Author

Commented:
Thank you. How does "ww" define a week?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Gus' function returns 2017W13 for today's date.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Who knows? Questioneer is  silent.

/gustav
Hiroyuki TamuraField Engineer

Author

Commented:
Thank you, all. I'm trying to add ISO method.
is this the right way?
Snap1972.png
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes.
Hiroyuki TamuraField Engineer

Author

Commented:
Thank you all

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions