Solved

group by week access

Posted on 2011-03-23
7
354 Views
Last Modified: 2012-05-11
Hello experts.

I have grouping by week in my report.
I need show in group header date range for each week instead number of week what I have right now.

Existing formula (showing number of week in year):
=formula$[(Date Worked)],"ww",0,0)

I' looking something like that instead:
Running report for march 2011
week 03-01-2011 thru 03-05-2011
detalis......
week ......etc
how to do that
thx
0
Comment
Question by:henryk123
  • 4
  • 2
7 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35199150
Yeah, I did this once in a previous Q....

I'll dig it up tonight if another Expert does not chime in...


JeffCoachman
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 35199491
I have a table (tbl_Numbers) that I use for this purpose.  It contains a single field (intNumber) and 10 records (the values 0 through 9).  From this table, I create a query (qyr_Numbers) which generates numbers from 0 through 99 (easy to expand to 999 or larger) that looks like:

SELECT Tens.intNumber * 10 + Ones.intNumber as intNumber
FROM tbl_Numbers as Tens, tbl_Numbers as Ones

from qry_Numbers, you can then create another query which gives you the week start and end dates for a given 100 day period (given the start date).

SELECT DatePart("ww",DateAdd("d",[intNumber],[StartDate])) AS RangeWeek,
            Min(DateAdd("d",[intNumber],[StartDate])) AS WeekStart,
            Max(DateAdd("d",[intNumber],[StartDate])) AS WeekEnd
FROM qry_Numbers
WHERE Weekday(DateAdd("d",[intNumber],[StartDate]),2)<6
GROUP BY DatePart("ww",DateAdd("d",[intNumber],[StartDate]));
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35199600
Thanks fyed, mine was much more convoluted than yours...

;-)

Jeff
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35199633
henryk123,

Just keep in mind that anytime you do things "Weekly", you need to take into account the year.

For example, if your data spans multiple years, you could have week 12 for *all* years, in the same Group.

Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35199640
...so your primary grouping should be the Year, then the week...
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35199657
Can you confirm you want to do this for a given month for data to date in the current year?
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 35199862
If you modify my query slightly,  you could limit it to a particular week.  It would look something like:

PARAMETERS [StartDate] DateTime, [MyWeek] Short;
SELECT DatePart("ww",DateAdd("d",[intNumber],[StartDate])) AS RangeWeek,
             Min(DateAdd("d",[intNumber],[StartDate])) AS WeekStart,
             Max(DateAdd("d",[intNumber],[StartDate])) AS WeekEnd
FROM qry_Numbers
WHERE Weekday(DateAdd("d",[intNumber],[StartDate]),2)<6
 AND DatePart("ww",DateAdd("d",[intNumber],[StartDate]))=[MyWeek]
GROUP BY DatePart("ww",DateAdd("d",[intNumber],[StartDate]));

You could then use this query (call it qry_Dates) and create a function that would wrap that up and return the string for the header group header.

Public Function fnWeekStartAndEndDates(MyYear as integer, MyMonth as integer, MyWeek as integer) as String

    Dim dtStartDate as Date
    Dim qdf as dao.querydef
    Dim rs as DAO.Recordset

    dtStartDate = dateserial(MyYear, MyMonth, 1)
    set qdf = currentdb.querydefs(qry_Dates)
    qdf.parameters(0) = dtStartDate
    qdf.parameters(1) = MyWeek

    set rs = qdf.openrecordset
    if rs.eof then
        fnWeekStartAndEndDates = "Invalid combination of Year, Month, and week!"
    Else
        fnWeekStartAndEndDates = "Report running for " & format(dtStartDate, "mmmm yyyy") & " week " _
                                                  & Format(rs(1), "mm-dd-yyyy") & " thru " & Format(rs(2), "mm-dd-yyyy")
    End If

    rs.close
    set rs = nothing
    set qdf = nothing

End Function

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question