Line Chart question

  • Thread starter Thread starter BobRoyAce
  • Start date Start date
B

BobRoyAce

I have a report that has a line chart on it. The chart's
Row Source is as follows:

SELECT MonthNames.MonthName, MonthData.FiscalMonthNumber,
Sum(qryMonthlyMachine.[Time(min)]) AS [SumOfTime(min)]
FROM MonthNames INNER JOIN
(MonthData INNER JOIN qryMonthlyMachine ON
MonthData.SystemMonthNumber =
qryMonthlyMachine.SystemMonthNumber)
ON (MonthNames.MonthName = qryMonthlyMachine.MonthName)
AND (MonthNames.MonthOrder = MonthData.FiscalMonthNumber)
GROUP BY MonthNames.MonthName,
MonthData.FiscalMonthNumber, qryMonthlyMachine.Machine,
qryMonthlyMachine.FiscalYear
HAVING (qryMonthlyMachine.Machine=207) AND
(qryMonthlyMachine.FiscalYear=2004)
ORDER BY MonthData.FiscalMonthNumber

My problem is that the line chart plots both the
MonthData.FiscalMonthNumber value AND the Sum... value
(as two separate lines). I only want the chart to plot
the Sum... value. My understanding is that, to have the
MonthData.FiscalMonthNumber in the ORDER BY clause, it
must be in the GROUP BY clause and, in order for
MonthData.FiscalMonthNumber to be in the GROUP BY clause,
it must be in the SELECT clause. Otherwise, I would
simply remove it from everywhere except the
ORDER BY clause and be done with it. How can I get the
chart to only plot the Sum...value?
 
You can group on a field without selecting it. So make your Select bit of the SQL as follows
SELECT MonthNames.MonthName, Sum(qryMonthlyMachine.[Time(min)]) AS [SumOfTime(min)], leave the rest as is

It might be easier to understand if you were to use the design view - here you could uncheck a box to hide the field from your output (I learnt SQL by playing around in design view and then seeing what difference it made to the SQL behind)

Basi

----- BobRoyAce wrote: ----

I have a report that has a line chart on it. The chart's
Row Source is as follows

SELECT MonthNames.MonthName, MonthData.FiscalMonthNumber,
Sum(qryMonthlyMachine.[Time(min)]) AS [SumOfTime(min)
FROM MonthNames INNER JOIN
(MonthData INNER JOIN qryMonthlyMachine ON
MonthData.SystemMonthNumber =
qryMonthlyMachine.SystemMonthNumber
ON (MonthNames.MonthName = qryMonthlyMachine.MonthName)
AND (MonthNames.MonthOrder = MonthData.FiscalMonthNumber
GROUP BY MonthNames.MonthName,
MonthData.FiscalMonthNumber, qryMonthlyMachine.Machine,
qryMonthlyMachine.FiscalYea
HAVING (qryMonthlyMachine.Machine=207) AND
(qryMonthlyMachine.FiscalYear=2004
ORDER BY MonthData.FiscalMonthNumbe

My problem is that the line chart plots both the
MonthData.FiscalMonthNumber value AND the Sum... value
(as two separate lines). I only want the chart to plo
the Sum... value. My understanding is that, to have the
MonthData.FiscalMonthNumber in the ORDER BY clause, it
must be in the GROUP BY clause and, in order fo
MonthData.FiscalMonthNumber to be in the GROUP BY clause,
it must be in the SELECT clause. Otherwise, I would
simply remove it from everywhere except the
ORDER BY clause and be done with it. How can I get the
chart to only plot the Sum...value
 
Back
Top