Query weekly totals

  • Thread starter Thread starter swas
  • Start date Start date
S

swas

Hi,

I want to create a query that returns total sales grouped on a weekly basis.
This isn't hard for monthly by just grouping on the month.

Ideally I want to define what constitutes the week (Mon - Sun, or Thu - Wed
etc...) and then return total daily sales for each.

With no 'week' style function I can't get my head around a simple solution,
without making up a bunch of seperate queries defining each week.


Thanks in advance.


swas
 
It is difficult to offer a valid solution based on the information
provided. You may want to provide a table definition to your post.

Based on the information provided you may want to look at the DatePart
function.

Good Luck.
 
Swas,
Its not a great answer, but one of the things I do with important dates is
this. In the query, I add in a month, year and somethimes quarter field,
Year: Year([Invoice]) so I can easily filter a form or print selected
records. You might want a day field in your query, Day: Day([Invoice]). Then
you could build a case statement, 1-5 for all possible weeks in a month with
criteria strings in each. So for the second week of the month, your case 2
would have an SQL criteria as "Day = 8 or 9 or 10 or 11 or 12 or 13 or 14"
and the criteria would also have to include the month and year. Obviously you
could shorten the criteria to Day >7 and <15 and Month = 5 and Year = 2008,
(not exact syntax) for this to work. Hope that helps.
 
Thanks for all the responses and thoughts.

Datepart solved my problem - I had it in my head there was a function that
defined weeks but couldn't find it when I had an initial browse.

Becomes a problem when you don't spend a lot of time coding...


Thanks again


swas
 
Back
Top