Charts in Access

[D Willett]

I have a chart which is embedded on a subform which returns data from a date ranged query.
The chart shows the sum of 4 fields, "Paint, Parts,Other,Labour" therefore showing only one dataline.
I want to redesign this to show the sum of each field instead.
How do I change the chart to show the individual totals and have 4 separate datalines?

[HansV]

Hi Dave,

Does the query sun the values of Paint, Parts etc., or does it have a separate column for each of these fields?

[D Willett]

Hi Hans

Thisis the query:

SELECT tblEST.STA, tblEST.Finished, qryPaintAfterDiscount.[Sell Paint], qryLabour.[Labour Total], qryOtherCost.[Retail Other], [Sell Paint]+[Labour Total] AS Totals, tblEST.AMD_PRT_CST AS Parts, tblEST.Est, tblEST.Sup
FROM ((tblEST LEFT JOIN qryPaintAfterDiscount ON (tblEST.SUP_NO = qryPaintAfterDiscount.Supp) AND (tblEST.EST_NO = qryPaintAfterDiscount.[Estimate No])) LEFT JOIN qryLabour ON (tblEST.Sup = qryLabour.Supp) AND (tblEST.Est = qryLabour.[Estimate No])) LEFT JOIN qryOtherCost ON (tblEST.Sup = qryOtherCost.Supp) AND (tblEST.Est = qryOtherCost.[Estimate No])
GROUP BY tblEST.STA, tblEST.Finished, qryPaintAfterDiscount.[Sell Paint], qryLabour.[Labour Total], qryOtherCost.[Retail Other], [Sell Paint]+[Labour Total], tblEST.AMD_PRT_CST, tblEST.Est, tblEST.Sup
HAVING (((tblEST.STA)="F") AND ((tblEST.Finished)>=[forms]![frmStatus]![txtDateFrom] And (tblEST.Finished)<=[forms]![frmStatus]![txtDateTo]));

The chart shows sales for each item sold on a daily basis between two dates selected, does this help ?

[HansV]

Thanks!

What is the Row Source of the chart on the subform?

[D Willett]

SELECT (Format([Finished],"DDDDD")),Sum([Totals]) AS [SumOfTotals] FROM [qryGraph] GROUP BY (Int([Finished])),(Format([Finished],"DDDDD"));

Regards

Dave

[HansV]

Sorry to keep on asking! What is the definition of qryFinished?

[D Willett]

Hi Hans, if you mean the field [Finished] it is a date field. The chart uses the dates on the horizontal bottom line .

There is no qryFinished, I have a qryF which pulls all date from tblEst where one of the field stores the letter "F" but not sure if it hold relevance to the graph..

Cheers

[HansV]

Oops, sorry, I meant qryGraph.

[D Willett]

lol .......... !!
Here's qryGraph

SELECT tblEST.STA, tblEST.Finished, qryPaintAfterDiscount.[Sell Paint], qryLabour.[Labour Total], qryOtherCost.[Retail Other], [Sell Paint]+[Labour Total] AS Totals, tblEST.AMD_PRT_CST AS Parts, tblEST.Est, tblEST.Sup
FROM ((tblEST LEFT JOIN qryPaintAfterDiscount ON (tblEST.EST_NO = qryPaintAfterDiscount.[Estimate No]) AND (tblEST.SUP_NO = qryPaintAfterDiscount.Supp)) LEFT JOIN qryLabour ON (tblEST.Est = qryLabour.[Estimate No]) AND (tblEST.Sup = qryLabour.Supp)) LEFT JOIN qryOtherCost ON (tblEST.Est = qryOtherCost.[Estimate No]) AND (tblEST.Sup = qryOtherCost.Supp)
GROUP BY tblEST.STA, tblEST.Finished, qryPaintAfterDiscount.[Sell Paint], qryLabour.[Labour Total], qryOtherCost.[Retail Other], [Sell Paint]+[Labour Total], tblEST.AMD_PRT_CST, tblEST.Est, tblEST.Sup
HAVING (((tblEST.STA)="F") AND ((tblEST.Finished)>=[forms]![frmStatus]![txtDateFrom] And (tblEST.Finished)<=[forms]![frmStatus]![txtDateTo]));

[HansV]

Try changing the Row Source of the chart to

SELECT (Format([Finished],"DDDDD")), Sum([Sell Paint]) As SumOfPaint, Sum([Parts]) As SumOfParts, Sum([Retail Other]) As SumOfRetail, Sum([Labour Total]) As SumOfLabour FROM [qryGraph] GROUP BY (Int([Finished])),(Format([Finished],"DDDDD"));

[D Willett]

I love this lounge