The following is what I have managed to put together so far, but it doesn't seem to
quite work, It generates a list of all users of certain equipment and some statistics
about them:
SELECT Users.[First Name], Screeners.[Last Name], Report2.LoginID,
Sum(Report2.BagCount) AS SumOfBagCount, Sum(Report2.TipCount) AS SumOfItemCount,
Sum(Report2.Hit) AS SumOfHit, Sum(Report2.[False]) AS SumOfFalse, Sum(Report2.Miss)
AS SumOfMiss, IIf([SumOfItemCount]=0,0,([SumOfHit]/[SumOfItemCount])) AS [Percentage
Hit]
FROM Users INNER JOIN (MachineList INNER JOIN Report2 ON MachineList.Machine =
Report2.MachineCode) ON Users.LoginID = Report2.LoginID
WHERE (((MachineList.Location)=[Forms]![ReportMenu]![Combo339]))
GROUP BY Screeners.[First Name], Screeners.[Last Name], Report2.LoginID
HAVING
(SumOfHit]/[SumOfItemCount])<=(Sum([SumOfHit])/Sum([SumOfItemCount]))-StDev([Percenta
ge Hit])));
I know it is a little confusing. What I have is some performance data, and a
column for how each person does as a percent. I am wanting to get a list of those
perople whose percentage falls below One Standard Deviation away from the Average.
To do this I took the percent column and add the expression that is in the "HAVING"
section above.
Basically I tried to sum up all of the "Hit" numbers and divide by the sum of the
"ItemCount" numbers, then subtract the StDev of all of the "Percentage Hit" values.
When I run this I get a prompt box asking me for all the values of this expression.
It is not calculating them on its own.
If anybody has any ideas how to fix this or accomplich what I am trying to do here,
I would greatly appreciate any assistance you can give me.Hi Amanda,
I am not really the one to give you an answer
(my eyes glaze over just looking at your SQL)...
but, since no one has responded, I have a
"general idea" of what you need to do.....
It might be that you could just save your
query w/o the HAVING clause, then use
that query in another query applying condition
with WHERE clause.
If Steve Dassin were lurking here, he could
probably whip out a crosstab solution.
I can only repeat some of the things he has
provided previously.
****quote****
From: Steve Dassin (
[email protected])
Subject: Re: getting a calculated field in a cross tab query?
Newsgroups: microsoft.public.access.queries
Date: 2002-01-26 17:58:00 PST
Greetings,
Hidden within the Access crosstab query is the
ability to reference the pivot column data,reference
the pivot column values and manipulate data.Unfortunately
this methodology has gone unheaded since it was first
discussed (by moi) with Access 97.This methodology
should work with all versions of Access since 97.Once
you understand how to work with the crosstab in this
fashion you will come to see that this methodology not
only allows you to drill within a row of data as your
trying to do but allows the crosstab to be used as a
general problem solving environment that can be used
in place of a SELECT query.While the technical details
are beyond the scope of this post suffice it to say that
terms in the SELECT list are constructed with aggregate functions
and can include if/then logic by use of the SWITCH/IIF functions.
It is even possible to use subqueries let alone domain functions.
Terms with aggregate functions are required since the crosstab query is a special
case of a GROUP BY query.
And in a GROUP BY query, column(s)/expression(s) not part of the GROUP BY
must be expressed as arguments to an aggregate function.
The reason an alias must be defined for the pivot column is that internally the new
terms involve referencing (like that of a correlated subquery).
Such queries require aliasing of tables and columns.
The same principle applies here.
For more details see my reply (trysql) to the following post:
TJake <
[email protected]>
microsoft.public.access.queries
Thursday, January 17, 2002
Subject:Crosstab Calculations
This example uses the Northwind orders table and pivots
employeeid for shipcountry.The cells of the crosstab contain
counts.For each row (shipcountry) the most extreme value
in terms of deviating from the mean count for the row is
returned in the column [cnt for maxdiff].Also returned is
the pivot column (employeeid) corresponding to this extreme
value in the column [extreme employeeid].This is precisely
what you are trying to accomplish and shows that the crosstab
can perform in a similar manner as excel if not more so.
To run the query delete all comment lines (lines starting
with --).This methodology can only be used in query VIEW.
Do not attempt to put the query or make modifications/run
in DESIGN view.This is a limitation of DESIGN view only
just as DESIGN view cannot support non equal joins.
Examine all the columns created in the SELECT list.The
logic is straightforward and follows a natural order to
obtain the results.
transform count(*) as cnt
select [shipcountry],
-- Define alias for employeeid.
[employeeid] as emp,
-- Get average of the counts (cnt).Every reference to 'cnt' causes a
-- cycling thru all count terms for the row.The counts can only
-- be referenced by using an alias for the transform term,ie. count(*),
avg(cnt) as avg1,
-- Find the maximum difference (count-average).Sometimes the aggregate
-- used in the term is consistent with what your trying to accomplish.
-- Sometimes the aggregate is not particularly related to the task.
-- When only a single value can satify the logic any aggregate will surfice
-- to fullfill the requirement that the term be within an aggregate function.
-- This requires some experimenting but it will become quite easy to
-- think in these terms and pick the appropriate aggregate.
max(abs(avg1-cnt)) as maxdiff,
-- Find the count belonging to the maximum difference.
max(switch(avg1+maxdiff-cnt between 0 and .01 or avg1-maxdiff-cnt between 0 and
..01,cnt)) as [cnt for maxdiff],
-- Find the employeeid (pivot column value) associated with
-- the count for maximum difference.The employeeid is referenced
-- by the alias 'emp'.If two counts have the same deviation using MIN
-- instead of MAX will make a difference.In cases like this the term
-- can be repeated with the MIN aggregate as well.
max(switch(cnt=cint( [cnt for maxdiff]),emp)) as [extreme employeeid]
from orders
group by [shipcountry]
pivot [employeeid];
Using the SWITCH function allows almost any level of granularity
to the logic you can use.
Here the extreme count is computed only if the difference
is >= 1 std dev. unit from the mean.When the max difference is
less than the standard deviation the [extreme employeeid] column
will be blank and the cnt for maxdiff column will contain a -9.
This method allows you to easily read the column values and
omit particular rows with a subsequent query.
transform count(*) as cnt
select [shipcountry],
[employeeid] as emp,
avg(cnt) as avg1,
stdev(cnt) as std1,
max(abs(avg1-cnt)) as maxdifftest,
switch(maxdifftest>=std1,maxdifftest,true,0) as maxdiff,
max( switch(maxdiff<>0,
switch(avg1+maxdiff-cnt between 0 and 0.01 or avg1-maxdiff-cnt between 0 and
0.01,cnt),true,-9)) as [cnt for maxdiff],
max(switch(cnt=cint([cnt for maxdiff]),emp)) as [extreme employeeid]
from orders
group by [shipcountry]
pivot [employeeid];
This material first appeared in a paper entitled:
Heavy Duty Crosstabs with Access 97 by Steve Dassin
THT
Steve Dassin
****unquote****
Pretty powerful stuff, but I am not
sufficiently skilled in this method to
zero in on your problem...only can
guess that the solution can be found
in this "method."
Apologies,
Gary Walter
