weekly report

[Ramesh]

Hi,

What function do i use to get weekly reports? Like numbers for each week of
the year.

I tried the function Weeknum i found in the Help file, but Access says
Undefined function.

Am sure there must be a simple way for doing this.

any pointers please.

Thanks
Ramesh

 

[Guest]

Hi Ramesh

Try format(DateField, "ww")

You can set the first day of the week and first week of the year by adding 2
further parameters...

format(DateField, "ww", P1, P2)

For P1: 0 = uses NLS API setting, 1 = Sunday (default), 2 = Monday, 3 =
Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday

For P2: 0 = Uses NLS API setting, 1 = The week that contains Jan 1, 2 = The
1st week that has at least 4 days in the year, 3 = The first full week of the
year.

Regards

Andy Hull

 

[Ramesh]

Thanks Hull.

But i didnt quite get the point.

If i need say the weekly sales of last quarter, can i use the weeknum
function or is there a function called format? sorry if i m mising
something basic.

How do i specify that i need the numbers of this week, which is week No.19
of this year? Default sunday should be fine.

didnt quite get the P2 part.

request a little further light please.

thansk
Ramesh

 

[Michael Gramelspacher]

Thanks Hull.

But i didnt quite get the point.

If i need say the weekly sales of last quarter, can i use the weeknum
function or is there a function called format? sorry if i m mising
something basic.

How do i specify that i need the numbers of this week, which is week No.19
of this year? Default sunday should be fine.

didnt quite get the P2 part.

request a little further light please.

thansk
Ramesh

Run using Northwind:

SELECT Sum(a.[Daily Sales]) AS [Sum of Daily Sales], DATEPART
("ww",a.orderdate) AS [Sales Week]
FROM [SELECT Orders.OrderDate, Sum([Order Subtotals].Subtotal) AS [Daily
Sales]
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID=[Order
Subtotals].OrderID
WHERE DATEDIFF("q", 2, OrderDAte) = DATEDIFF("q", 2, #1998-05-11#)-1
GROUP BY Orders.OrderDate]. AS a
GROUP BY DATEADD("ww", DATEDIFF("ww", 0, a.orderDAte),0), DATEPART
("ww",a.orderdate);

 

[Guest]

Hi Ramesh

The function is called format.

Bearing in mind that different companies have different definitions for
quarters...

Let's say you want weekly sales for the first qtr of 2007 and let's say each
qtr is 13 weeks of 7 days each from Mon 1st Jan to Sun 1st April.

Query will be something like...

SELECT cbyte(format(SalesDate, "ww",2)), SUM(SalesValue)
FROM tblSales
WHERE SalesDate between #01/01/2007# AND #04/01/2007#
GROUP BY cbyte(format(SalesDate, "ww",2))

I have enclosed the format function in cbyte() to convert to a number format
so the weeks are ordered correctly.
Also, the 3rd parameter of the format function specifies Monday as the first
day of the week.

The 4th parameter mentioned in my previous post and not used here defines
when week 1 starts in the year. You might want it to start on Jan 1st or you
might want week 1 to start on the first Sunday etc. Look up the format
command (in help and on google) and experiment with the values.

Regards

Andy Hull

 

[Ramesh]

Beautiful!! thanks Andy and Michael for your contribution.

Gratefully taken.

Ramesh