average line

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
"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?
 
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
 
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...
 
Back
Top