472,373 Members | 1,563 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,373 software developers and data experts.

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

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

Apr 25 '06 #1
14 3361
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

Apr 25 '06 #2
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

Apr 25 '06 #3

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%

May 1 '06 #4
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.

May 1 '06 #5
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.

May 2 '06 #6
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

May 3 '06 #7
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.

May 4 '06 #8
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

May 4 '06 #9
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

May 8 '06 #10

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?

May 17 '06 #11
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

May 17 '06 #12

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%

May 18 '06 #13
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)

May 18 '06 #14
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

May 18 '06 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Darleen | last post by:
I am seeking conceptual here on how to get started with a "3D Matrix" in Access. We run a training center which holds multiple classes in multiple cities at multiple times. So I need to create a...
1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
2
by: Sherman H. | last post by:
I have a few questions for crosstab and popup form questions: 1. I created a crosstab as follows: IT Financial Operation John 21 22 ...
2
by: deejayquai | last post by:
Hi I'm trying to produce a report based on a dynamic crosstab. Ultimately i'd like the report to actually become a sub report within a student end of year record of achievement. The dynamic...
13
by: salad | last post by:
Operating in A97. I didn't receive much of a response conserning Pivot tables in Access. Pivot tables are nice, but a CrossTab will work for me too. Using a Pivot table, one is actually...
7
Boxcar74
by: Boxcar74 | last post by:
Hi, This is probably easy but Iím canít find the answer. Iím using Access 2003 (mdb). Ok Here is my Issue Iím running a crosstab query to retrieve data for the Last 13 weeks from a single date...
2
by: hwalker | last post by:
Hello all. Long time reader, first time poster :) I'm creating a repot that shows "the last two weeks of data the next 6 weeks of data, week over week". So, I have a calculated field called...
6
kcdoell
by: kcdoell | last post by:
Hello: I have a report that is based on the following crosstab query: SELECT tblProduct.ProductName, QryProd_Bud.GWP_BUD, Nz(,0) AS Week_1, Nz(,0) AS Week_2, Nz(,0) AS Week_3, Nz(,0) AS Week_4,...
4
by: zion4ever | last post by:
Hello good people, Please bear with me as this is my first post and I am relative new to ASP. I do have VB6 experience. I have a form which enables users within our company to do an intranet...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
2
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
1
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
0
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.