How to get the count for each week??

[blyssz]

Hello All,

I have library books database in MS Access and people borrow books and submit them.
In the table I have Status field, BorrowedDate, SubmitDate and several other fields like BookName, BookType etc
I want to checkthe count i.e
# of New books borrowed at the end of each week for the past 5 weeks and
# of New books submitted at the end of the week for the past 5 weeks.
Also Total # of all books submitted at the end of each week
Total # of all books borrowed at the end of each week.
So I just want to check if the rate of submission is more than rate of borrowing.

I understand that we can get the date, or month from the query but I could not find any function for the week and If I have the date then how can I use Datepart Function for the week.

How should I write the query to get the number new books submitted and borrowed at the end of each week and To get the total of all books submitted and borrowed at the end of each week for the past 4 week.

Any suggestion would be appreciated.

Thanks
Blyssz

 

[FancyPrairie]

Your query is a simple Count query. The Where clause, obviously, is going to control what data is included.

I would create a form from which the users can select the criteria they want. For example, the user can select the book type(s) they want included in the count (simple multi-select list box). And a date range for the borrowed and a date range for the submitted. Depending on how you want it to work you could default the end date of the borrowed books to end of the current week (i.e. Saturday) and the number of weeks to 4. The begin date for the borrowed books would be a hidden field that you would compute based on the number of weeks selected.

Your form would look something like this:

Borrowed Books (End Date): 2/7/2009 Number of weeks: 4
Hidden Begin Date (Begin Date): 1/17/2009

Book Types (List Box): Type 1
Type 2
Type 3
...

Now, assume the end date is named txtDate_EndR and the Begin date is txtDate_BeginR and the Number of Weeks field is named txtNumWks. The Onload event of your form would look like this:

Private Sub Form_Load()

    txtDate_EndR = DateAdd("d", Weekday(Date) + (7 - Weekday(Date)), Date)
    txtNumWks = 4
    Call txtNumWks_AfterUpdate
    
End Sub

The AfterUpdate event of the number of weeks field would look like this:

Private Sub txtNumWks_AfterUpdate()

    txtDate_BeginR = DateAdd("ww", -txtNumWks, txtDate_EndR)
End Sub

This FAQ faq181-5497 contains a routine that will build the Where clause for you without the word Where. Simply copy the code from the FAQ and paste it in a new module. Set the Tag property of txtDate_BeginR to Where=YourTable.BorrowedDate,date. To see how it works, add a button on your form and on the onclick event add the following code.

Private Sub Command6_Click()

    MsgBox BuildWhere(Me)
    
End Sub

The msgbox will display the Where clause based on the begin and end date range. For example, with the data entered above, the msgbox should display (YourTable.BorrwedDate Between #1/17/2009# and #2/7/2009#) Note, however, that the begin date is controlled by the number of weeks entered. Also note that the BuildWhere function will not include items that are invisible. Therefore, you cannot set the Visible property of the txtDate_BeginR to No, rather, you will need to set the background and foreground colors the same basically making it invisble.

To open your report from the form, use this command:
DoCmd.OpenReport "ReportName,acViewPreview,,BuildWhere(Me)

The FAQ explains how to use it. The main thing is setting up the Tag properties correctly

To get a feel for how what I've explained above works, create a new form and add 3 text boxes and a command button. The text boxes are named txtDate_BeginR, txtDate_EndR, and txtNumWks. The command button is named Command6. Add the OnLoad event code I've shown above, the AfterUpdate code from above and the Onclick code. Set the Tag property of the field txtDate_BeginR to what I've stated above. Now open the form and click the command button.

 

[blyssz]

Thanks for your response. I got your point but my problem is I want to get the count for the submitted and borrowed books at the end of each week for the past 4 weeks and my week starts from Monday and ends on Sunday. So If want to check the count for the past 4 week any day in the current week, it should always result the same numbers.Alos I want to get the total count of Submitted and borrowed til the end of the week for particular week periods.
I want to get something like this:
1/18/09 1/25/09 02/1/09 02/08/09
New Borrowed 8 42 36 17
New Submitted 3 29 21 24
TotalBorrowed 60 168 189 213
TotalSubmitted 139 73 88 81

Next week, I want to get Counts for week ending 1/25, 2/1, 2/8., 2/15 .
How can I do that??

Thanks,
Blyssz

 

[FancyPrairie]

What you need to to is to convert your dates to weeks so your can group your dates by week number. So, your query should look something like this:

Select GetWeek([dteFromYourTable]) as Week from YourTable

The followng function converts the date passed to a week number.

Function GetWeek(dteFind As Date) As Integer

    Dim dteBegin As Date
    Dim dteCheck As Date
    Dim dteEnd As Date
    Dim dteTmp As Date
    Dim i As Integer
    Dim intNumWks As Integer
    
    dteCheck = Forms!YourFormName!EndDateOfReport
    intNumWks = Forms!YourFormName!NumberOfWeeks
    
   
    For i = 1 To 4

        dteEnd = DateAdd("d", 7 - Weekday(dteCheck, vbMonday), dteCheck)
        dteBegin = DateAdd("ww", -1, dteEnd) + 1
    
        If (dteFind >= dteBegin) And (dteFind <= dteEnd) Then
            Debug.Print i
            GetWeek = i
            Exit Function
        Else
            dteCheck = DateAdd("d", -7, dteCheck)
        End If
    Next
    
End Function

 

[blyssz]

Thanks a lot