Query calculating total weekly enquiry nos

[aparnawangu]

Hi Team,
I have a table called Enquiry_Form table and i want to write a query for calculating total no of enquiries in a week.
The columns in the enquiry table are
Enquiry_Form_No,Enquiry_Date,Centre_Name.
Pls help me in creating this query as i am very new to Access.

Regards
Aparna

 

[Ranman256]

Build a query,Q1, to pull [Centre] , [WeekEnding] into the query
The Weekending is calculated by the function below to calculate the ending day of the week.
below I am using Saturday. (change yours as needed)
usage: WeekEnding: getWeekEnding([Enquiry_Date])
This query will pull all the weeks.

Now make another query Q2 that uses Q1 to count them.

Public Function getWeekEnding(ByVal pvDate)
Dim iDayNum As Integer, iLastDay As Integer, iDaz2Add As Integer
Dim vDate

iLastDay = vbSaturday
If IsNull(pvDate) Then
   getWeekEnding = ""
Else
   iDayNum = Format(pvDate, "w")
   iDaz2Add = iLastDay - iDayNum
   getWeekEnding = DateAdd("d", iDaz2Add, pvDate)
End If
End Function