Show Records by Week per Month

[Sheila.deJesus]

Good day!

I'm planning to create a form that would show total number of sales transaction per week: If today's month is February, i would like users to see records like this: (Week should start on a Monday and ends on Saturday)

GroupNo______Week1_____Week2_____Week3____Week4____Week5___MTD
GroupA________12_________2__________10________11_______15_____50
GroupB_________8_________5___________7_________1_______21_____42

I can't find a sample code for this particular date criteria. :-(

Thanks in advance!


Sheila

 

[namliam]

You could make a crosstab query to do this, assuming you can fix the column names...

 

[Dennisk]

be aware of the pitfalls of grouping by year by month etc. As you need to include the year for a month grouping otherwise you will group the same month accross all the years in the recordset.

 

[Sheila.deJesus]

Thanks for all your inputs. I am familiar with the croostab query but i don't have an idea on how to change the column heading to "Weeks".

 

[Sheila.deJesus]

If below is the criteria for pulling records between the 1st day and last day of the current week (Sunday being the 1st), what would be the criteria for pulling records for later weeks?

Between Date()-Weekday(Date())+1 And Date()-Weekday(Date())+7

For the month of February, there are 5 weeks:
Feb 1 to 3 = 1st week
Feb 4 to 10 = 2nd week
Feb 11 to 17 = 3rd week
Feb 18 to 24 = 4th week
Feb 25 to 28 = 5th week

I would like to show total records for each week on a form.

Thank you!


Sheila

 

[Dennisk]

in crostabs you can specify the order of the columns. Go to query properties and look up context sensitive help for column headings.

 

[namliam]

If below is the criteria for pulling records between the 1st day and last day of the current week (Sunday being the 1st), what would be the criteria for pulling records for later weeks?

Between Date()-Weekday(Date())+1 And Date()-Weekday(Date())+7

lets see... ok that pulls for this week...
First of this month: date() - day(date()) + 1
Last of this month
1) dateadd("M",1,date() - day(date()))+1
2) dateadd("M",1,date() - day(date())+1)-1

For the month of February, there are 5 weeks:
Feb 1 to 3 = 1st week
Feb 4 to 10 = 2nd week
Feb 11 to 17 = 3rd week
Feb 18 to 24 = 4th week
Feb 25 to 28 = 5th week

I would like to show total records for each week on a form.

You have to take the date field in your table and convert that to a week, substract the first week of this month... and done...

Weeknumber for the first of the month: format(date() - day(date()) +1, "WW", 2, 2)

Thank you!


Sheila

Happy to help (as allways)