Query with Weekly Sum or Total instead Month,Quarter,year

[Sandip]

Hi
I want to make query which should give sum or total or Avg on week basis instead of Unique Date/Time,Day,Month,Quarter or Year
If anyone can help ( I need Sum from Monday to Sunday)
Sandip
I need detail for full year on weekly basis not only for any selected week

 

[Pat Hartman]

Use the DatePart() function to extract the week number of a date. Read the help entry to find out how to change the first day of the week or the first week of the year. Be sure to have a code window open when asking for help on functions. This type of help is not available when you ask for help from the database window view. (Don't yell at me, I didn't make help this stupid. Write to Microsoft to complain.)

 

[Sandip]

Thanks
Partly problem solved as I came to know extraction of weeks but still i have to work and think how to total pertcular field on week basis and in report display start and end date for the same
My actual need is like this
I have Patients data who takes treatments every week, some patients take 1 treatment per week some 2 some 3 etc. I need count of patients taking treatments 1,2,3 etc per week on monthly basis.
If you can help further
Thanks again
Sandip

 

[Pat Hartman]

If you use the report wizard to create a report, it is easy to select grouping and summing options as you go. If you choose to group by a date field, you can choose, day, month, year, week, or quarter.

 

[Sandip]

Thanks Pat
My problem is now I need Start date and End date of that week numbers to print in that report such that eg. week no 43 of 2004 Start date 10/17/04 and End date as 10/24/04 so I need to convert week no. to start date and end date(Reverse of Datepart("ww",[date])) I don't know how to do this in query??
Sandip

 

[Sandip]

Still waiting to get some help on above
Thanks
Sandip

 

[Pat Hartman]

I have never seen a function that does this. I played around a little and came up with the following expression which returns the last day of the week. To find the first day, you would subtract 7 from this expression.

print ((51 * 7)) + #1/1/4# - datepart("w",(#1/1/4#))

This is quite rough. It looks like if you multiply the week number by 7 and add that to the first day of the year and then subtract out the day number of the first day of the year you end up with the end date of the week. This will be fine for weeks 2-52 but if you don't want the first week of the year to start with a date in the previous year, you'll need to do something. Also you may end up with a week 53 so you need to figure out what to do with that. It will start in the current year and end in the next year.

 

[Sandip]

Hi Pat
Thanks again
But I did found some solution
In my main query I made Expression field as
Start Date: Format([date]-Weekday([date],2)+1,"mm-dd-yyyy")
End Date: Format([date]-Weekday([date],2)+7,"mm-dd-yyyy")
which gives me Start date(Monday) and End date (Sunday)
This I can use any time to get Start and End date of perticular Week
Sandip

 

[Pat Hartman]

I mis-understood your question.

My problem is now I need Start date and End date of that week numbers to print in that report such that eg. week no 43 of 2004 Start date 10/17/04 and End date as 10/24/04

I thought you wanted to calculate the begin and end dates for a particular week NUMBER. Given a number from 1-53 and the year, calculate the first day and last day of the week number. Here are the two functions from my Useful Date Functions db

Public Function FstDayCurWeek(InDate As Date) As Date
    FstDayCurWeek = InDate - WeekDay(InDate) + 1
End Function

Public Function LstDayCurWeek(InDate As Date) As Date
    LstDayCurWeek = InDate - WeekDay(InDate) + 7
End Function

PS - I hope your date field is not really called [date]. Date is the name of a function and you will at some point run into a problem and you may not even notice. Change the field name as soon as possible.

 

[Sandip]

Thanks Pat
I will keep this in mind
but I am not using Public Functions or Modules because of lack of knowladge
but will be keen on learning the same so if you can suggest Website or Samples of Datbase to look
Sandip

 

[Pat Hartman]

To use Public functions, copy the functions I posted and paste them into a new empty module. Save the module and name it "mDateFunctions". You can now use these functions any place you would use built in functions.

If you are going to do this calculation in more than one place, the function is safer since it will be less prone to errors from typos.

 

[Sandip]

Thanks Pat
But couple of times I tried to use module or Function it did not work may be I don't know exect way to use it in Forms or Report
I will keep trying
Thanks again

 

[Pat Hartman]

Reports and forms can have their own class modules. Functions in those class modules can be used by other code within the form or report. For any other use, the procedure name would need to be properly qualified. If you want a procedure to be truely public, place it in a module that you create from the modules tab.