Doing crosstab query - need info from current week only for 1 field

[Tina]

My employer tracks productivity/performance of clinicians (how much
they
bill) each week, its averages for the month, and the 6 months.
These averages are compared to their expected productivity.

However, the expectation changes - it may be 60% for a while, then
change to
50%. Initially, I was averaging the expectation, along with the
productivity, but what I'm being asked is to look at the average
productivity/performance compared to the expectation for the CURRENT
week
(not the average across weeks).

I do a crosstab to look at all of this and am not sure how to tell
Access to
look only the CURRENT week's expectation compared to average
performance.

For example -- my field is "Week of" which would be 4/17/06 for last
week and
4/24/06 for this week. I have another field called "Expectation" which
may
have been 50% last week, but it 55% this week. When I run the
crosstab, I'm averaging the actual performance but only want to look
at the expectation (the 55%) for this week. I don't want to average
the 50% and 55%.

Does this make sense and can anyone help?

Thanks -

Tina

[CDMAPoster]

Tina wrote:

My employer tracks productivity/performance of clinicians (how much
they
bill) each week, its averages for the month, and the 6 months.
These averages are compared to their expected productivity.

However, the expectation changes - it may be 60% for a while, then
change to
50%. Initially, I was averaging the expectation, along with the
productivity, but what I'm being asked is to look at the average
productivity/performance compared to the expectation for the CURRENT
week
(not the average across weeks).

I do a crosstab to look at all of this and am not sure how to tell
Access to
look only the CURRENT week's expectation compared to average
performance.

For example -- my field is "Week of" which would be 4/17/06 for last
week and
4/24/06 for this week. I have another field called "Expectation" which
may
have been 50% last week, but it 55% this week. When I run the
crosstab, I'm averaging the actual performance but only want to look
at the expectation (the 55%) for this week. I don't want to average
the 50% and 55%.

Does this make sense and can anyone help?

Tina,

Please show a few sample lines of data with fieldnames along with a
sample calculation. I feel confident that someone here can help once
we understand what you are trying to do.

Thanks,

James A. Fortune
CD********@FortuneJames.com

[manningfan]

Try using a multi-dimensional array to read the data in.

First, capture Expectation where week = current (you can use
**Format(Now(), "ww")** to get the week number). Then pull the
expectation data out of your crosstab, or write the data from your
crosstab into a multi-dimensional array. You should be able to marry
the two pieces of data with a simple query, or write them together into
an array.

Dig?

http://www.eyesoft.net

[Tina]

CD********@FortuneJames.com wrote:

Tina wrote:

My employer tracks productivity/performance of clinicians (how much
they
bill) each week, its averages for the month, and the 6 months.
These averages are compared to their expected productivity.

However, the expectation changes - it may be 60% for a while, then
change to
50%. Initially, I was averaging the expectation, along with the
productivity, but what I'm being asked is to look at the average
productivity/performance compared to the expectation for the CURRENT
week
(not the average across weeks).

I do a crosstab to look at all of this and am not sure how to tell
Access to
look only the CURRENT week's expectation compared to average
performance.

For example -- my field is "Week of" which would be 4/17/06 for last
week and
4/24/06 for this week. I have another field called "Expectation" which
may
have been 50% last week, but it 55% this week. When I run the
crosstab, I'm averaging the actual performance but only want to look
at the expectation (the 55%) for this week. I don't want to average
the 50% and 55%.

Does this make sense and can anyone help?

Tina,

Please show a few sample lines of data with fieldnames along with a
sample calculation. I feel confident that someone here can help once
we understand what you are trying to do.

Thanks,

James A. Fortune
CD********@FortuneJames.com

Here's line of data from the cross tab datasheet. In the cross tab, my
rows are LName, FName, Ave Expection, and Director. My month is the
column heading. My productivity averaged for each month is the value.
I don't know if this is making any sense or not.. sorry..

LASTNAME FIRSTNAME Avg Expectation Director
ANDERSON KARON 50.00%
Dave Shiel

September, 05 October, 05
38.00% 39.33%

[Tina]

Thanks for the response. Unfortunately, I don't know how to do a
multi-dimensional array - I guess I'm still somewhat of a beginner.

[CDMAPoster]

Tina wrote:

CD********@FortuneJames.com wrote:

Tina wrote:

My employer tracks productivity/performance of clinicians (how much
they
bill) each week, its averages for the month, and the 6 months.
These averages are compared to their expected productivity.

However, the expectation changes - it may be 60% for a while, then
change to
50%. Initially, I was averaging the expectation, along with the
productivity, but what I'm being asked is to look at the average
productivity/performance compared to the expectation for the CURRENT
week
(not the average across weeks).

I do a crosstab to look at all of this and am not sure how to tell
Access to
look only the CURRENT week's expectation compared to average
performance.

For example -- my field is "Week of" which would be 4/17/06 for last
week and
4/24/06 for this week. I have another field called "Expectation" which
may
have been 50% last week, but it 55% this week. When I run the
crosstab, I'm averaging the actual performance but only want to look
at the expectation (the 55%) for this week. I don't want to average
the 50% and 55%.

Does this make sense and can anyone help?

Tina,

Please show a few sample lines of data with fieldnames along with a
sample calculation. I feel confident that someone here can help once
we understand what you are trying to do.

Thanks,

James A. Fortune
CD********@FortuneJames.com

Here's line of data from the cross tab datasheet. In the cross tab, my
rows are LName, FName, Ave Expection, and Director. My month is the
column heading. My productivity averaged for each month is the value.
I don't know if this is making any sense or not.. sorry..

LASTNAME FIRSTNAME Avg Expectation Director
ANDERSON KARON 50.00%
Dave Shiel

September, 05 October, 05
38.00% 39.33%

Tina,

Thanks for providing the extra information. I think it might help to
see the SQL of the crosstab query you are using. I'll see if I can
figure it out without it tomorrow. It's more confusing than usual
because there's a statistical measure called Expectation that
determines whether or not a gamble -- any kind of gamble from lottery
tickets to a poker pot -- is likely to pay off. I'm a little tired
after four days at the state VNEA championships. My team placed second
out of 101 teams. All I can do right now is yawn a flaccid hurrah.

James A. Fortune
CD********@FortuneJames.com

I've never purchased a lottery ticket.

[CDMAPoster]

CD********@FortuneJames.com wrote:

Tina wrote:

CD********@FortuneJames.com wrote:

Tina wrote:
> My employer tracks productivity/performance of clinicians (how much
> they
> bill) each week, its averages for the month, and the 6 months.
> These averages are compared to their expected productivity.
>
> However, the expectation changes - it may be 60% for a while, then
> change to
> 50%. Initially, I was averaging the expectation, along with the
> productivity, but what I'm being asked is to look at the average
> productivity/performance compared to the expectation for the CURRENT
> week
> (not the average across weeks).
>
> I do a crosstab to look at all of this and am not sure how to tell
> Access to
> look only the CURRENT week's expectation compared to average
> performance.
>
> For example -- my field is "Week of" which would be 4/17/06 for last
> week and
> 4/24/06 for this week. I have another field called "Expectation" which
> may
> have been 50% last week, but it 55% this week. When I run the
> crosstab, I'm averaging the actual performance but only want to look
> at the expectation (the 55%) for this week. I don't want to average
> the 50% and 55%.
>
> Does this make sense and can anyone help?
>

Tina,

Please show a few sample lines of data with fieldnames along with a
sample calculation. I feel confident that someone here can help once
we understand what you are trying to do.

Thanks,

James A. Fortune
CD********@FortuneJames.com

Here's line of data from the cross tab datasheet. In the cross tab, my
rows are LName, FName, Ave Expection, and Director. My month is the
column heading. My productivity averaged for each month is the value.
I don't know if this is making any sense or not.. sorry..

LASTNAME FIRSTNAME Avg Expectation Director
ANDERSON KARON 50.00%
Dave Shiel

September, 05 October, 05
38.00% 39.33%

Tina,

Thanks for providing the extra information. I think it might help to
see the SQL of the crosstab query you are using. I'll see if I can
figure it out without it tomorrow. It's more confusing than usual
because there's a statistical measure called Expectation that
determines whether or not a gamble -- any kind of gamble from lottery
tickets to a poker pot -- is likely to pay off. I'm a little tired
after four days at the state VNEA championships. My team placed second
out of 101 teams. All I can do right now is yawn a flaccid hurrah.

James A. Fortune
CD********@FortuneJames.com

I've never purchased a lottery ticket.

The design view of the crosstab emailed to me was something like:

LASTNAME FIRSTNAME Director Month AdjustedBillTime
ProductivityExpectation Month
Group By Group By Group By Group By Avg Avg Where
Row Row Row Column Value Row

Result of crosstab query (abbreviated version):

LASTNAME FIRSTNAME AvgOfProductivity Director 05 Sep, 05 Oct, ... 06
Feb
Anders Kar 50% Dave Shi Null Null Null Null 38.0% 39.3%
Azar Jord 50% Dave Shi 59.0% 52.2% 52.5% 55.5% 55.6% 52.9%
Bale Sar 50% Dave Shi 31.5% 27.4% 23.8% 24.4% 34.6% Null

My best guess for the SQL for the crosstab query was:

TRANSFORM Avg(AdjustedBillTime) AS AvgOfAdjustedBillTime
SELECT LASTNAME, FIRSTNAME, Director, Avg(ProductivityExpectation) AS
AvgOfProductivityExpectation
FROM tblExpectation
GROUP BY LASTNAME, FIRSTNAME, Director
PIVOT Format([Month],"yy mmm") In ("06 Jan","06 Feb","05 Sep","05
Oct","05 Nov","05 Dec");

The direction I started moving in was:

TRANSFORM Avg(AdjustedBillTime) AS AvgOfAdjustedBillTime
SELECT LASTNAME, FIRSTNAME, Director, Avg(ProductivityExpectation) AS
AvgOfProductivityExpectation
FROM tblExpectation
WHERE [Week Of] = DateAdd("d", 1 - Weekday(Date()), Date())
GROUP BY LASTNAME, FIRSTNAME, Director
PIVOT Format([Week Of], "yy mmm");

The problem with this is that I believe the WHERE clause would limit
the averages to the current week also.

The A97 help file on the TRANSFORM Statement says:

Optionally, you can include other clauses, such as WHERE, that specify
additional selection or sorting criteria. You can also use subqueries
as predicates -- specifically, those in the WHERE clause -- in a
crosstab query.

So there's hope that a subquery can be used to get the monthly average
for ProductivityExpectation for the month containing the [Week Of]
value, or that the monthly averages can be found like before and that a
subquery can get the averages for the current week. I'm relatively new
at crosstab queries so anyone can feel free to jump in anytime. If I
put a subquery with an average in the SELECT part I get the dreaded
"Multi-level GROUP BY clause not allowed in subqueries." message.
Perhaps Tina can confirm my suspicions about what the SQL of the
crosstab query is like. Also, I would like to confirm my guess that
the average for the current week is being compared to the average for
the current month. Hopefully I have provided enough information for
her to be able to explain what she is trying to do in more detail.

James A. Fortune
CM********@FortuneJames.com

[Tina]

Hi James -

Congrats on placing 2nd!

My SQL is basically what you thought -

TRANSFORM Avg([Adjusted bill time]) AS [AvgOfAdjusted bill time]
SELECT LASTNAME,FIRSTNAME,Director, Avg([Productivity Expectation]) AS
[AvgOfProductivity Expectation]
FROM [Monthly report]
GROUP BY LASTNAME,FIRSTNAME,Director
PIVOT Month In ("September, 05","October, 05","November, 05","December,
05","January, 06","February, 06");

When I add the WHERE statement, I don't receive any records -
WHERE [Week Of] = DateAdd("d", 1 - Weekday(Date()), Date())

I also tried -
WHERE ([Week of])=#2/6/2006#))
to see what happened when I specify a date. It pulled people who had a
record for that week and only gave me averages for the month of
February.

The expectation for the current week is being compared to the average
for
the current month. Example: For the 4 weeks of April, let's say I
produced 45%, 50%, 55%, and 60%, for an average productivity of 52.5%.
For the 1st 3 weeks of the month, my expectation was 60%, but it has
since moved to 50% by the 4th week. (Why it's adjusted for
substandard performance is a WHOLE different story!!). So, although my
average expectation of 60%, 60%, 60%, and 50% comes to 57.5%, my
overall expectation has been reduced to 50%. What they want to do is
compared my average productivity of 52.% to my new expectation of 50%.
Management looks at this data weekly, monthly, and for the 6 months.

Thanks again for the help. Please note that I'll be off my account
from May 6th - May 14th.

[CDMAPoster]

Tina wrote:

Hi James -

Congrats on placing 2nd!

My SQL is basically what you thought -

TRANSFORM Avg([Adjusted bill time]) AS [AvgOfAdjusted bill time]
SELECT LASTNAME,FIRSTNAME,Director, Avg([Productivity Expectation]) AS
[AvgOfProductivity Expectation]
FROM [Monthly report]
GROUP BY LASTNAME,FIRSTNAME,Director
PIVOT Month In ("September, 05","October, 05","November, 05","December,
05","January, 06","February, 06");

When I add the WHERE statement, I don't receive any records -
WHERE [Week Of] = DateAdd("d", 1 - Weekday(Date()), Date())

I also tried -
WHERE ([Week of])=#2/6/2006#))
to see what happened when I specify a date. It pulled people who had a
record for that week and only gave me averages for the month of
February.

The expectation for the current week is being compared to the average
for
the current month. Example: For the 4 weeks of April, let's say I
produced 45%, 50%, 55%, and 60%, for an average productivity of 52.5%.
For the 1st 3 weeks of the month, my expectation was 60%, but it has
since moved to 50% by the 4th week. (Why it's adjusted for
substandard performance is a WHOLE different story!!). So, although my
average expectation of 60%, 60%, 60%, and 50% comes to 57.5%, my
overall expectation has been reduced to 50%. What they want to do is
compared my average productivity of 52.% to my new expectation of 50%.
Management looks at this data weekly, monthly, and for the 6 months.

Thanks again for the help. Please note that I'll be off my account
from May 6th - May 14th.

Thanks. This information helps a lot. Does anyone have an example of
using a subquery within a crosstab query? That would save me some
experimenting.

James A. Fortune
CD********@FortuneJames.com

[CDMAPoster]

CD********@FortuneJames.com wrote:

Tina wrote:

Hi James -

Congrats on placing 2nd!

My SQL is basically what you thought -

TRANSFORM Avg([Adjusted bill time]) AS [AvgOfAdjusted bill time]
SELECT LASTNAME,FIRSTNAME,Director, Avg([Productivity Expectation]) AS
[AvgOfProductivity Expectation]
FROM [Monthly report]
GROUP BY LASTNAME,FIRSTNAME,Director
PIVOT Month In ("September, 05","October, 05","November, 05","December,
05","January, 06","February, 06");

When I add the WHERE statement, I don't receive any records -
WHERE [Week Of] = DateAdd("d", 1 - Weekday(Date()), Date())

I also tried -
WHERE ([Week of])=#2/6/2006#))
to see what happened when I specify a date. It pulled people who had a
record for that week and only gave me averages for the month of
February.

The expectation for the current week is being compared to the average
for
the current month. Example: For the 4 weeks of April, let's say I
produced 45%, 50%, 55%, and 60%, for an average productivity of 52.5%.
For the 1st 3 weeks of the month, my expectation was 60%, but it has
since moved to 50% by the 4th week. (Why it's adjusted for
substandard performance is a WHOLE different story!!). So, although my
average expectation of 60%, 60%, 60%, and 50% comes to 57.5%, my
overall expectation has been reduced to 50%. What they want to do is
compared my average productivity of 52.% to my new expectation of 50%.
Management looks at this data weekly, monthly, and for the 6 months.

Thanks again for the help. Please note that I'll be off my account
from May 6th - May 14th.
Thanks. This information helps a lot. Does anyone have an example of
using a subquery within a crosstab query? That would save me some
experimenting.

James A. Fortune
CD********@FortuneJames.com

I realize you won't see this until the 15th.

In order to test the crosstab I used:

tblExpectation
ExpectationID
LASTNAME
FIRSTNAME
ProductivityExpectation
Director
AdjustedBillTime
theMonth
Week Of

With my sample data I tried:

TRANSFORM IIf([Week Of] = DateAdd("d", 1 - Weekday(#2/5/06#),
#2/5/06#), Avg(AdjustedBillTime), Null) AS AvgOfAdjustedBillTime
SELECT LASTNAME, FIRSTNAME, Director, Avg(ProductivityExpectation) AS
AvgOfProductivityExpectation FROM tblExpectation GROUP BY LASTNAME,
FIRSTNAME, Director, [Week Of] PIVOT Format([theMonth],"yy mmmm") In
("06 January","06 February","05 September","05 October","05
November","05 December");

Note that #2/5/06# should be Date() in both places but I needed to get
values for the past data in my test table.

The crosstab put blanks everywhere that [Week Of] was not equal to the
current week. I'm not positive it works correctly, but the IIf
function can limit the values selected for averaging. For example,

TRANSFORM Avg(IIf([Week
Of]=DateAdd("d",1-Weekday(#2/5/2006#),#2/5/2006#),AdjustedBillTime,Null))
AS AvgOfAdjustedBillTime, ...
From A97 Avg Function Help:

The Avg function doesn't include any Null fields in the calculation.

So this might be a way to get the average you want without requiring a
subquery in the crosstab.

James A. Fortune
CD********@FortuneJames.com

[Tina]

CD********@FortuneJames.com wrote:

CD********@FortuneJames.com wrote:

Tina wrote:

Hi James -

Congrats on placing 2nd!

My SQL is basically what you thought -

TRANSFORM Avg([Adjusted bill time]) AS [AvgOfAdjusted bill time]
SELECT LASTNAME,FIRSTNAME,Director, Avg([Productivity Expectation]) AS
[AvgOfProductivity Expectation]
FROM [Monthly report]
GROUP BY LASTNAME,FIRSTNAME,Director
PIVOT Month In ("September, 05","October, 05","November, 05","December,
05","January, 06","February, 06");

When I add the WHERE statement, I don't receive any records -
WHERE [Week Of] = DateAdd("d", 1 - Weekday(Date()), Date())

I also tried -
WHERE ([Week of])=#2/6/2006#))
to see what happened when I specify a date. It pulled people who had a
record for that week and only gave me averages for the month of
February.

The expectation for the current week is being compared to the average
for
the current month. Example: For the 4 weeks of April, let's say I
produced 45%, 50%, 55%, and 60%, for an average productivity of 52.5%.
For the 1st 3 weeks of the month, my expectation was 60%, but it has
since moved to 50% by the 4th week. (Why it's adjusted for
substandard performance is a WHOLE different story!!). So, although my
average expectation of 60%, 60%, 60%, and 50% comes to 57.5%, my
overall expectation has been reduced to 50%. What they want to do is
compared my average productivity of 52.% to my new expectation of 50%.
Management looks at this data weekly, monthly, and for the 6 months.

Thanks again for the help. Please note that I'll be off my account
from May 6th - May 14th.

Thanks. This information helps a lot. Does anyone have an example of
using a subquery within a crosstab query? That would save me some
experimenting.

James A. Fortune
CD********@FortuneJames.com

I realize you won't see this until the 15th.

In order to test the crosstab I used:

tblExpectation
ExpectationID
LASTNAME
FIRSTNAME
ProductivityExpectation
Director
AdjustedBillTime
theMonth
Week Of

With my sample data I tried:

TRANSFORM IIf([Week Of] = DateAdd("d", 1 - Weekday(#2/5/06#),
#2/5/06#), Avg(AdjustedBillTime), Null) AS AvgOfAdjustedBillTime
SELECT LASTNAME, FIRSTNAME, Director, Avg(ProductivityExpectation) AS
AvgOfProductivityExpectation FROM tblExpectation GROUP BY LASTNAME,
FIRSTNAME, Director, [Week Of] PIVOT Format([theMonth],"yy mmmm") In
("06 January","06 February","05 September","05 October","05
November","05 December");

Note that #2/5/06# should be Date() in both places but I needed to get
values for the past data in my test table.

The crosstab put blanks everywhere that [Week Of] was not equal to the
current week. I'm not positive it works correctly, but the IIf
function can limit the values selected for averaging. For example,

TRANSFORM Avg(IIf([Week
Of]=DateAdd("d",1-Weekday(#2/5/2006#),#2/5/2006#),AdjustedBillTime,Null))
AS AvgOfAdjustedBillTime, ...

From A97 Avg Function Help:

The Avg function doesn't include any Null fields in the calculation.

So this might be a way to get the average you want without requiring a
subquery in the crosstab.

James A. Fortune
CD********@FortuneJames.com

Thanks James - this may be a silly question, but for the
Format([theMonth],"yy mmmm") part, am I literally entering "theMonth"
or do I enter the month I want my data from? I get a prompt either way
and am not sure if I'm supposed to?

[CDMAPoster]

Tina wrote:

CD********@FortuneJames.com wrote:

CD********@FortuneJames.com wrote:

Tina wrote:
> Hi James -
>
> Congrats on placing 2nd!
>
> My SQL is basically what you thought -
>
> TRANSFORM Avg([Adjusted bill time]) AS [AvgOfAdjusted bill time]
> SELECT LASTNAME,FIRSTNAME,Director, Avg([Productivity Expectation]) AS
> [AvgOfProductivity Expectation]
> FROM [Monthly report]
> GROUP BY LASTNAME,FIRSTNAME,Director
> PIVOT Month In ("September, 05","October, 05","November, 05","December,
> 05","January, 06","February, 06");
>
> When I add the WHERE statement, I don't receive any records -
> WHERE [Week Of] = DateAdd("d", 1 - Weekday(Date()), Date())
>
> I also tried -
> WHERE ([Week of])=#2/6/2006#))
> to see what happened when I specify a date. It pulled people who had a
> record for that week and only gave me averages for the month of
> February.
>
> The expectation for the current week is being compared to the average
> for
> the current month. Example: For the 4 weeks of April, let's say I
> produced 45%, 50%, 55%, and 60%, for an average productivity of 52.5%.
> For the 1st 3 weeks of the month, my expectation was 60%, but it has
> since moved to 50% by the 4th week. (Why it's adjusted for
> substandard performance is a WHOLE different story!!). So, although my
> average expectation of 60%, 60%, 60%, and 50% comes to 57.5%, my
> overall expectation has been reduced to 50%. What they want to do is
> compared my average productivity of 52.% to my new expectation of 50%.
> Management looks at this data weekly, monthly, and for the 6 months.
>
> Thanks again for the help. Please note that I'll be off my account
> from May 6th - May 14th.

Thanks. This information helps a lot. Does anyone have an example of
using a subquery within a crosstab query? That would save me some
experimenting.

James A. Fortune
CD********@FortuneJames.com

I realize you won't see this until the 15th.

In order to test the crosstab I used:

tblExpectation
ExpectationID
LASTNAME
FIRSTNAME
ProductivityExpectation
Director
AdjustedBillTime
theMonth
Week Of

With my sample data I tried:

TRANSFORM IIf([Week Of] = DateAdd("d", 1 - Weekday(#2/5/06#),
#2/5/06#), Avg(AdjustedBillTime), Null) AS AvgOfAdjustedBillTime
SELECT LASTNAME, FIRSTNAME, Director, Avg(ProductivityExpectation) AS
AvgOfProductivityExpectation FROM tblExpectation GROUP BY LASTNAME,
FIRSTNAME, Director, [Week Of] PIVOT Format([theMonth],"yy mmmm") In
("06 January","06 February","05 September","05 October","05
November","05 December");

Note that #2/5/06# should be Date() in both places but I needed to get
values for the past data in my test table.

The crosstab put blanks everywhere that [Week Of] was not equal to the
current week. I'm not positive it works correctly, but the IIf
function can limit the values selected for averaging. For example,

TRANSFORM Avg(IIf([Week
Of]=DateAdd("d",1-Weekday(#2/5/2006#),#2/5/2006#),AdjustedBillTime,Null))
AS AvgOfAdjustedBillTime, ...

From A97 Avg Function Help:

The Avg function doesn't include any Null fields in the calculation.

So this might be a way to get the average you want without requiring a
subquery in the crosstab.

James A. Fortune
CD********@FortuneJames.com

Thanks James - this may be a silly question, but for the
Format([theMonth],"yy mmmm") part, am I literally entering "theMonth"
or do I enter the month I want my data from? I get a prompt either way
and am not sure if I'm supposed to?

Tina,

This may be a silly answer. When I saw [Month] in your post I thought
it referred to a field in your table named 'Month' rather than
something you typed in at a prompt. Naturally, I changed the name of
the field from 'Month' to 'theMonth' because Month is a reserved name
in Access. Perhaps what you really want is to get the month of the
WeekOf date using something like:

DateSerial(Year([WeekOf]), Month([WeekOf]), 1) = [MonthForReport]

I'm not sure what you need here. What does a row in your table look
like?

James A. Fortune
CD********@FortuneJames.com

[Tina]

CD********@FortuneJames.com wrote:

Tina wrote:

CD********@FortuneJames.com wrote:

CD********@FortuneJames.com wrote:
> Tina wrote:
> > Hi James -
> >
> > Congrats on placing 2nd!
> >
> > My SQL is basically what you thought -
> >
> > TRANSFORM Avg([Adjusted bill time]) AS [AvgOfAdjusted bill time]
> > SELECT LASTNAME,FIRSTNAME,Director, Avg([Productivity Expectation]) AS
> > [AvgOfProductivity Expectation]
> > FROM [Monthly report]
> > GROUP BY LASTNAME,FIRSTNAME,Director
> > PIVOT Month In ("September, 05","October, 05","November, 05","December,
> > 05","January, 06","February, 06");
> >
> > When I add the WHERE statement, I don't receive any records -
> > WHERE [Week Of] = DateAdd("d", 1 - Weekday(Date()), Date())
> >
> > I also tried -
> > WHERE ([Week of])=#2/6/2006#))
> > to see what happened when I specify a date. It pulled people who had a
> > record for that week and only gave me averages for the month of
> > February.
> >
> > The expectation for the current week is being compared to the average
> > for
> > the current month. Example: For the 4 weeks of April, let's say I
> > produced 45%, 50%, 55%, and 60%, for an average productivity of 52.5%.
> > For the 1st 3 weeks of the month, my expectation was 60%, but it has
> > since moved to 50% by the 4th week. (Why it's adjusted for
> > substandard performance is a WHOLE different story!!). So, although my
> > average expectation of 60%, 60%, 60%, and 50% comes to 57.5%, my
> > overall expectation has been reduced to 50%. What they want to do is
> > compared my average productivity of 52.% to my new expectation of 50%.
> > Management looks at this data weekly, monthly, and for the 6 months.
> >
> > Thanks again for the help. Please note that I'll be off my account
> > from May 6th - May 14th.
>
> Thanks. This information helps a lot. Does anyone have an example of
> using a subquery within a crosstab query? That would save me some
> experimenting.
>
> James A. Fortune
> CD********@FortuneJames.com

I realize you won't see this until the 15th.

In order to test the crosstab I used:

tblExpectation
ExpectationID
LASTNAME
FIRSTNAME
ProductivityExpectation
Director
AdjustedBillTime
theMonth
Week Of

With my sample data I tried:

TRANSFORM IIf([Week Of] = DateAdd("d", 1 - Weekday(#2/5/06#),
#2/5/06#), Avg(AdjustedBillTime), Null) AS AvgOfAdjustedBillTime
SELECT LASTNAME, FIRSTNAME, Director, Avg(ProductivityExpectation) AS
AvgOfProductivityExpectation FROM tblExpectation GROUP BY LASTNAME,
FIRSTNAME, Director, [Week Of] PIVOT Format([theMonth],"yy mmmm") In
("06 January","06 February","05 September","05 October","05
November","05 December");

Note that #2/5/06# should be Date() in both places but I needed to get
values for the past data in my test table.

The crosstab put blanks everywhere that [Week Of] was not equal to the
current week. I'm not positive it works correctly, but the IIf
function can limit the values selected for averaging. For example,

TRANSFORM Avg(IIf([Week
Of]=DateAdd("d",1-Weekday(#2/5/2006#),#2/5/2006#),AdjustedBillTime,Null))
AS AvgOfAdjustedBillTime, ...

>From A97 Avg Function Help:

The Avg function doesn't include any Null fields in the calculation.

So this might be a way to get the average you want without requiring a
subquery in the crosstab.

James A. Fortune
CD********@FortuneJames.com

Thanks James - this may be a silly question, but for the
Format([theMonth],"yy mmmm") part, am I literally entering "theMonth"
or do I enter the month I want my data from? I get a prompt either way
and am not sure if I'm supposed to?

Tina,

This may be a silly answer. When I saw [Month] in your post I thought
it referred to a field in your table named 'Month' rather than
something you typed in at a prompt. Naturally, I changed the name of
the field from 'Month' to 'theMonth' because Month is a reserved name
in Access. Perhaps what you really want is to get the month of the
WeekOf date using something like:

DateSerial(Year([WeekOf]), Month([WeekOf]), 1) = [MonthForReport]

I'm not sure what you need here. What does a row in your table look
like?

James A. Fortune
CD********@FortuneJames.com

Here's some sample data - it's entered each week for all clinical
staff. Productivity is a calculated field based on hours worked.

Staff ID Office CodeLast NameFirst NameExpectation Week of
Productivity
5 Team 2 Smith Jack 50.00% 07-Feb-05 48%
10 Team 2 Jones Mary 50.00% 08-Feb-05 65%

[CDMAPoster]

Tina wrote:

Here's some sample data - it's entered each week for all clinical
staff. Productivity is a calculated field based on hours worked.

Staff ID Office CodeLast NameFirst NameExpectation Week of
Productivity
5 Team 2 Smith Jack 50.00% 07-Feb-05 48%
10 Team 2 Jones Mary 50.00% 08-Feb-05 65%

Thanks for showing me some sample data. My first reaction is that
[Week of] seems to be on arbitrary days of the week. Perhaps

WHERE [Week Of] = DateAdd("d", 1 - Weekday(Date()), Date())

should be

WHERE DateAdd("d", 1 - Weekday([Week of]), [Week of]) = DateAdd("d", 1
- Weekday(Date()), Date())

which would match any [Week of] values contained within the week
containing Date(). I'll look at this problem in more detail tonight.

James A. Fortune
CD********@FortuneJames.com

With Access, I see that there's no end to the number of $5000 contract
jobs you can have. -- Ed Steele (1998)

[CDMAPoster]

CD********@FortuneJames.com wrote:

Thanks for showing me some sample data. My first reaction is that
[Week of] seems to be on arbitrary days of the week. Perhaps

WHERE [Week Of] = DateAdd("d", 1 - Weekday(Date()), Date())

should be

WHERE DateAdd("d", 1 - Weekday([Week of]), [Week of]) = DateAdd("d", 1
- Weekday(Date()), Date())

which would match any [Week of] values contained within the week
containing Date(). I'll look at this problem in more detail tonight.

Expanding on this idea, try averaging [Productivity] only for the [Week
of] values within the current week from all the data using:

Avg(IIf(DateAdd("d", 1 - Weekday([Week of]), [Week of]) = DateAdd("d",
1-Weekday(Date()), Date()), [Productivity], Null))

James A. Fortune
CD********@FortuneJames.com