Run Query to group Data by Week Number

[BYoung via AccessMonster.com]

I'm trying to build a query off of a table that basically sumarizes by week
number instead of date.

so in my query I have these fields
tech number - shows which tech completed the job
jobs - counts 1 for every job
tc_within_x - counts a 1 if there was a trouble call created on this job
within 12 days
TC Percentage - based on a public function that I built to calculate the TC %
from TC/Jobs

Here's how I have it set up right now
tech_number jobs tc_within_x
TCPercentage: Avg(getTCPercentage
tblVIRData tblVIRData tblVIRData
Group By Sum Sum
Expression
Ascending

This returns a table like this:
Tech Number Jobs tc_within_x TC %
4567 91 1
1.10%
4893 169 10
5.92%

What this returns is the totals from all the data which spans several months.

This is all good at this point, but now I want to add in the schedule dates
and get the same output that I get now, but have the data grouped by week
number.
When I have added schedule date after the tech number field I get a read out
like this:
tech number job date jobs
tc_within_x TC%
4567 7/1/06 2
0 0
4567 7/2/06 4
0 0
4567 7/3/06 3
0 0
4893 7/1/06 3
0 0
4893 7/2/06 4
1 25%
4893 7/3/06 2
1 50%

I would like this query to give me the totals for by week instead of by day.
So i would get this:

tech number week jobs tc_within_x
TC%
4567 25 12 0
0
4567 26 10 1
10%
4567 27 14 0
0
4893 25 12 0
0
4893 26 10 1
10%
4893 27 14 0
0

Hopefully this makes sense. Once i've figured out how to do this, i'm going
to add in a user inputed criteria to the tech number so we can enter the tech
number and see the totals by week for that tech.

Any Ideas?

 

[Guest]

Group the query first by Year([SceduleDate]) and then by
DatePart("ww",[ScheduleDate]). The yearly grouping ensures that the same
weeks from separate years are grouped independently.

Ken Sheridan
Stafford, England

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT [tech number], DatePart("ww",[job date]) As WeekOfYear, SUM(jobs)
As SumOfJobs , SUM(tc_within_x) As SumOfTroubCalls
FROM <table name>
WHERE <criteria>
GROUP BY [tech number], DatePart("ww",[job date]) As [Week]
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRR7GJoechKqOuFEgEQK9JQCdEQD2HBAIiIGHPEoXF41GKVp68xAAoIUW
j95pW4q0IOVKktT8n6Zm7wKv
=ANwZ
-----END PGP SIGNATURE-----

 

[BYoung via AccessMonster.com]

Thank you both, The SQL query worked great after a little tinkering. Taking
it a step further I wanted to use the same setup for a query to show months
not weeks. I tried to change the datepart portion to
DatePart("mmm", [job Date]) however when I do this it tells me "Invalid
Procedure Call". I'm not very versed in queries or SQL, what is going on
with that?

Try this:

SELECT [tech number], DatePart("ww",[job date]) As WeekOfYear, SUM(jobs)
As SumOfJobs , SUM(tc_within_x) As SumOfTroubCalls
FROM <table name>

I'm trying to build a query off of a table that basically sumarizes by week
number instead of date.

[quoted text clipped - 65 lines]

to add in a user inputed criteria to the tech number so we can enter the tech
number and see the totals by week for that tech.

 

[MGFoster]

Months is just the letter M:

DatePart("m", [job date])

--
MGFoster:::mgf
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

Thank you both, The SQL query worked great after a little tinkering. Taking
it a step further I wanted to use the same setup for a query to show months
not weeks. I tried to change the datepart portion to
DatePart("mmm", [job Date]) however when I do this it tells me "Invalid
Procedure Call". I'm not very versed in queries or SQL, what is going on
with that?

Try this:

SELECT [tech number], DatePart("ww",[job date]) As WeekOfYear, SUM(jobs)
As SumOfJobs , SUM(tc_within_x) As SumOfTroubCalls
FROM <table name>

I'm trying to build a query off of a table that basically sumarizes by week
number instead of date.

[quoted text clipped - 65 lines]

to add in a user inputed criteria to the tech number so we can enter the tech
number and see the totals by week for that tech.

 

[BYoung via AccessMonster.com]

Ahh...well then I'll give that a shot. Thank You very much

Months is just the letter M:

DatePart("m", [job date])

Thank you both, The SQL query worked great after a little tinkering. Taking
it a step further I wanted to use the same setup for a query to show months

[quoted text clipped - 15 lines]