473,883 Members | 1,604 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 "Expectatio n" 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 3511
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 "Expectatio n" 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********@Fort uneJames.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********@Fort uneJames.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 "Expectatio n" 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********@Fort uneJames.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********@Fort uneJames.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 "Expectatio n" 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********@Fort uneJames.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********@Fort uneJames.com

I've never purchased a lottery ticket.

May 2 '06 #6
CD********@Fort uneJames.com wrote:
Tina wrote:
CD********@Fort uneJames.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 "Expectatio n" 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********@Fort uneJames.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********@Fort uneJames.com

I've never purchased a lottery ticket.


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

LASTNAME FIRSTNAME Director Month AdjustedBillTim e
ProductivityExp ectation 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 AvgOfProductivi ty 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(AdjustedBil lTime) AS AvgOfAdjustedBi llTime
SELECT LASTNAME, FIRSTNAME, Director, Avg(Productivit yExpectation) AS
AvgOfProductivi tyExpectation
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(AdjustedBil lTime) AS AvgOfAdjustedBi llTime
SELECT LASTNAME, FIRSTNAME, Director, Avg(Productivit yExpectation) AS
AvgOfProductivi tyExpectation
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 ProductivityExp ectation 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********@Fort uneJames.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,FIRSTN AME,Director, Avg([Productivity Expectation]) AS
[AvgOfProductivi ty Expectation]
FROM [Monthly report]
GROUP BY LASTNAME,FIRSTN AME,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,FIRSTN AME,Director, Avg([Productivity Expectation]) AS
[AvgOfProductivi ty Expectation]
FROM [Monthly report]
GROUP BY LASTNAME,FIRSTN AME,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********@Fort uneJames.com

May 4 '06 #9
CD********@Fort uneJames.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,FIRSTN AME,Director, Avg([Productivity Expectation]) AS
[AvgOfProductivi ty Expectation]
FROM [Monthly report]
GROUP BY LASTNAME,FIRSTN AME,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********@Fort uneJames.com


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

In order to test the crosstab I used:

tblExpectation
ExpectationID
LASTNAME
FIRSTNAME
ProductivityExp ectation
Director
AdjustedBillTim e
theMonth
Week Of

With my sample data I tried:

TRANSFORM IIf([Week Of] = DateAdd("d", 1 - Weekday(#2/5/06#),
#2/5/06#), Avg(AdjustedBil lTime), Null) AS AvgOfAdjustedBi llTime
SELECT LASTNAME, FIRSTNAME, Director, Avg(Productivit yExpectation) AS
AvgOfProductivi tyExpectation 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#),Adjusted BillTime,Null))
AS AvgOfAdjustedBi llTime, ...
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********@Fort uneJames.com

May 8 '06 #10

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

Similar topics

3
3217
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 matrix with 3 "axis". The 3 axes are: City Location Week of Class (in 14 week increments that repeat) Name of Class The end goal is to be able to have a form that shows these classes in
1
17692
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 Create a Dynamic Crosstab Report PRODUCT :Microsoft Access PROD/VER:1.00 1.10 OPER/SYS:WINDOWS
2
3055
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 22 Mary 15 17 16 Joe 28 29 31 For some other instances, the Operation column was not created because the
2
2950
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 sub-report will capture what grades the student has achieved in a list of different subjects and the reason I need it to be dynamic is that students take different subjects. Basically I've been trying to doctor the KB article on dynamic
13
17177
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 launching Excel for data viewing. I'd prefer the user stay in Access. Creating dynamic crosstab queries is pretty simple. The problem is that the column count may shrink or grow depending on the filter.
7
3693
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 field. I can get the data using the Parameters: WHERE (((-Weekday()+1) Between Date()-96 And Date()-7))
2
5636
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 "Week number" as the column header in a crosstab and then in the criteria for that field I put: DateDiff("ww",#12/22/2007#,Now(),0,#12/22/2007#)-2 DateDiff("ww",#12/22/2007#,Now(),0,#12/22/2007#)-1 DateDiff("ww",#12/22/2007#,Now(),0,#12/22/2007#)...
6
3172
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, Nz(,0) AS Week_5, QryProd_Prior.GWP_PRI FROM ((tblProduct LEFT JOIN ctqQueryP_G ON tblProduct.ProductID = ctqQueryP_G.ProductIDFK) LEFT JOIN QryProd_Bud ON tblProduct.ProductID = QryProd_Bud.ProductIDFK) LEFT JOIN QryProd_Prior ON...
4
4595
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 reservation of available resources (laptops, beamers, etc). The MySql database queries are already in place, as is the ASP administration panel. The frontend that users will see however, still needs some work. I'm really close, but since I'm no...
0
9933
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9781
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10732
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10836
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9564
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7960
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
4606
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4209
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3230
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.