average line

G

geebee

hi,

I have 2 lines in my line graph that I want to get the average of. For
example, line 1 has point values of 44, 22 and 33.
Line 2 has point values of 11, 1 and 11.

I want to add a third line that shows the average, which would be line1
values divided by line1 values.

HOW?

Thanks in advance,
geebee
 
D

Duane Hookom

"which would be line1 values divided by line1 values" would be the value of
1. Average is usually (A+B)/2. Do you have a Row Source of the chart control
that you could share?
 
G

geebee

Here is row source:

TRANSFORM
Sum(resolvedno_vs_noofCACScalls_betweenpopenterdtandresolveddt.[SumOfCOUNT( *
)]) AS [SumOfSumOfCOUNT( * )]
SELECT [ResolvedStatus] & " " & IIf([line]="line1","call
count",IIf([line]="line2","resolved count",IIF([line]="line3", "resolved, no
calls", IIF([line]="line4","avg days to resolved")) AS Expr1
FROM resolvedno_vs_noofCACScalls_betweenpopenterdtandresolveddt
GROUP BY [ResolvedStatus] & " " & IIf([line]="line1","call
count",IIf([line]="line2","resolved count","resolved, no calls")),
resolvedno_vs_noofCACScalls_betweenpopenterdtandresolveddt.line,
resolvedno_vs_noofCACScalls_betweenpopenterdtandresolveddt.line,
resolvedno_vs_noofCACScalls_betweenpopenterdtandresolveddt.line
PIVOT Format([ResolvedDt],'mm/dd');

the resolvedno_vs_noofCACScalls_betweenpopenterdtandresolveddt query is:
SELECT Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt,
Sum(Tbl_CACS_calls_between_popenterdt_and_resolveddt.[COUNT( * )]) AS
[SumOfCOUNT( * )], "line1" AS line
FROM Tbl_Resolved INNER JOIN
Tbl_CACS_calls_between_popenterdt_and_resolveddt ON Tbl_Resolved.[Loan Acct
#] = Tbl_CACS_calls_between_popenterdt_and_resolveddt.[Loan Acct #]
GROUP BY Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt
HAVING (((Tbl_Resolved.ResolvedStatus) Like "curr_pd*") AND
((Tbl_Resolved.ResolvedDt) Between #6/1/2006# And #6/30/2006#))
UNION ALL SELECT
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt,
Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode.[CountOfLoan Acct #])
AS [SumOfCountOfLoan Acct #],
"line2" AS line
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode
WHERE (((tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus)
Like "curr_pd*"))
GROUP BY tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt
UNION ALL
SELECT Tbl_Resolved.ResolvedStatus,
Tbl_Resolved.ResolvedDt,
Count(Tbl_Resolved.[Loan Acct #]) AS [CountOfLoan Acct #],
"line3" as line
FROM Tbl_Resolved LEFT JOIN Tbl_CACS_calls_between_popenterdt_and_resolveddt
ON Tbl_Resolved.[Loan Acct #] =
Tbl_CACS_calls_between_popenterdt_and_resolveddt.[Loan Acct #]
GROUP BY Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt,
Tbl_CACS_calls_between_popenterdt_and_resolveddt.[Loan Acct #]
HAVING (((Tbl_Resolved.ResolvedStatus) Like "curr_pd*") AND
((Tbl_Resolved.ResolvedDt) Between #6/1/2006# And #6/30/2006#) AND
((Tbl_CACS_calls_between_popenterdt_and_resolveddt.[Loan Acct #]) Is Null))
UNION ALL SELECT DISTINCT Tbl_Resolved.ResolvedStatus,
Tbl_Resolved.ResolvedDt, Avg([resolveddt]-[popenterdt]) AS days, "line4" AS
line
FROM Tbl_Resolved
GROUP BY Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt,
Tbl_Resolved.ResolvedStatus
HAVING (((Tbl_Resolved.ResolvedStatus) Like "curr_pd*") AND
((Tbl_Resolved.ResolvedDt) Between #6/1/2006# And #6/30/2006#));

SO... I want the average of lines 1 and 2 into a new line 5.

HOW?

Thanks,
geebee
 
D

Duane Hookom

I'm confused why you have so many references to the Line field in your group
by. I would think you only need one.

The only method that I can think of to average multiple rows in a crosstab
is to create another crosstab the combines/sums the multiple rows (remove
some group by fields) and then divides by the count of the number of removed
groups. Then use a union query to combine the two crosstabs into a single
query that can be used as the Row Source of the chart.

--
Duane Hookom
MS Access MVP

geebee said:
Here is row source:

TRANSFORM
Sum(resolvedno_vs_noofCACScalls_betweenpopenterdtandresolveddt.[SumOfCOUNT(
*
)]) AS [SumOfSumOfCOUNT( * )]
SELECT [ResolvedStatus] & " " & IIf([line]="line1","call
count",IIf([line]="line2","resolved count",IIF([line]="line3", "resolved,
no
calls", IIF([line]="line4","avg days to resolved")) AS Expr1
FROM resolvedno_vs_noofCACScalls_betweenpopenterdtandresolveddt
GROUP BY [ResolvedStatus] & " " & IIf([line]="line1","call
count",IIf([line]="line2","resolved count","resolved, no calls")),
resolvedno_vs_noofCACScalls_betweenpopenterdtandresolveddt.line,
resolvedno_vs_noofCACScalls_betweenpopenterdtandresolveddt.line,
resolvedno_vs_noofCACScalls_betweenpopenterdtandresolveddt.line
PIVOT Format([ResolvedDt],'mm/dd');

the resolvedno_vs_noofCACScalls_betweenpopenterdtandresolveddt query is:
SELECT Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt,
Sum(Tbl_CACS_calls_between_popenterdt_and_resolveddt.[COUNT( * )]) AS
[SumOfCOUNT( * )], "line1" AS line
FROM Tbl_Resolved INNER JOIN
Tbl_CACS_calls_between_popenterdt_and_resolveddt ON Tbl_Resolved.[Loan
Acct
#] = Tbl_CACS_calls_between_popenterdt_and_resolveddt.[Loan Acct #]
GROUP BY Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt
HAVING (((Tbl_Resolved.ResolvedStatus) Like "curr_pd*") AND
((Tbl_Resolved.ResolvedDt) Between #6/1/2006# And #6/30/2006#))
UNION ALL SELECT
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt,
Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode.[CountOfLoan Acct
#])
AS [SumOfCountOfLoan Acct #],
"line2" AS line
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode
WHERE (((tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus)
Like "curr_pd*"))
GROUP BY tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt
UNION ALL
SELECT Tbl_Resolved.ResolvedStatus,
Tbl_Resolved.ResolvedDt,
Count(Tbl_Resolved.[Loan Acct #]) AS [CountOfLoan Acct #],
"line3" as line
FROM Tbl_Resolved LEFT JOIN
Tbl_CACS_calls_between_popenterdt_and_resolveddt
ON Tbl_Resolved.[Loan Acct #] =
Tbl_CACS_calls_between_popenterdt_and_resolveddt.[Loan Acct #]
GROUP BY Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt,
Tbl_CACS_calls_between_popenterdt_and_resolveddt.[Loan Acct #]
HAVING (((Tbl_Resolved.ResolvedStatus) Like "curr_pd*") AND
((Tbl_Resolved.ResolvedDt) Between #6/1/2006# And #6/30/2006#) AND
((Tbl_CACS_calls_between_popenterdt_and_resolveddt.[Loan Acct #]) Is
Null))
UNION ALL SELECT DISTINCT Tbl_Resolved.ResolvedStatus,
Tbl_Resolved.ResolvedDt, Avg([resolveddt]-[popenterdt]) AS days, "line4"
AS
line
FROM Tbl_Resolved
GROUP BY Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt,
Tbl_Resolved.ResolvedStatus
HAVING (((Tbl_Resolved.ResolvedStatus) Like "curr_pd*") AND
((Tbl_Resolved.ResolvedDt) Between #6/1/2006# And #6/30/2006#));

SO... I want the average of lines 1 and 2 into a new line 5.

HOW?

Thanks,
geebee


Duane Hookom said:
"which would be line1 values divided by line1 values" would be the value
of
1. Average is usually (A+B)/2. Do you have a Row Source of the chart
control
that you could share?
Click to expand...
Click to expand...
 
Top