dates into week total

[OTWarrior via AccessMonster.com]

how do i have a query that displays information via the totals of each week
than each day (i am thinking that i would have to have multiple "step"
queries to acheive this)
eg:

qry1 (daily)
01/01/07 - data1 - count (1)
01/01/07 - data2 - count (1)
02/01/07 - data3 - count (1)
02/01/07 - data4 - count (1)
06/01/07 - data5 - count (1)
07/01/07 - data6 - count (1)
08/01/07 - data7 - count (1)
09/01/07 - data8 - count (1)
09/01/07 - data9 - count (1)
20/01/07 - data10 - count (1)

qry2 (weekly)
01/01/07 until 06/01/07 - count (5)
07/01/07 until 13/01/07 - count (4)
14/01/07 until 20/01/07 - count (1)

hope my question is clear, and thanks in advance for any help you can give.

 

[Marshall Barton]

how do i have a query that displays information via the totals of each week
than each day (i am thinking that i would have to have multiple "step"
queries to acheive this)
eg:

qry1 (daily)
01/01/07 - data1 - count (1)
01/01/07 - data2 - count (1)
02/01/07 - data3 - count (1)
02/01/07 - data4 - count (1)
06/01/07 - data5 - count (1)
07/01/07 - data6 - count (1)
08/01/07 - data7 - count (1)
09/01/07 - data8 - count (1)
09/01/07 - data9 - count (1)
20/01/07 - data10 - count (1)

qry2 (weekly)
01/01/07 until 06/01/07 - count (5)
07/01/07 until 13/01/07 - count (4)
14/01/07 until 20/01/07 - count (1)

Daily totals:
SELECT datefield,
Sum(countfield) As DayTotal
FROM qry1
GROUP BY datefield

Weekly totals:
SELECT DatePart("ww", datefield),
Sum(countfield) As WeekTotal
FROM qry1
GROUP BY DatePart("ww", datefield)

 

[John W. Vinson]

how do i have a query that displays information via the totals of each week
than each day (i am thinking that i would have to have multiple "step"
queries to acheive this)
eg:

qry1 (daily)
01/01/07 - data1 - count (1)
01/01/07 - data2 - count (1)
02/01/07 - data3 - count (1)
02/01/07 - data4 - count (1)
06/01/07 - data5 - count (1)
07/01/07 - data6 - count (1)
08/01/07 - data7 - count (1)
09/01/07 - data8 - count (1)
09/01/07 - data9 - count (1)
20/01/07 - data10 - count (1)

qry2 (weekly)
01/01/07 until 06/01/07 - count (5)
07/01/07 until 13/01/07 - count (4)
14/01/07 until 20/01/07 - count (1)

hope my question is clear, and thanks in advance for any help you can give.

A Totals query will do this:

SELECT DatePart("w", [datefield]), Count(*)
FROM yourtable
GROUP BY DatePart("w", [datefield])

This will give you week numbers from 1 to 52 (or even 54 depending on the
year). If you need the date ranges you can build some more complex expressions
but the date grouping will still use Datepart.

John W. Vinson [MVP]

 

[OTWarrior via AccessMonster.com]

Both of you guys, that was awesome, works like a charm