B
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?
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?