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?
Charts in Access
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Charts in Access
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78481
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Charts in Access
Hi Dave,
Does the query sun the values of Paint, Parts etc., or does it have a separate column for each of these fields?
Does the query sun the values of Paint, Parts etc., or does it have a separate column for each of these fields?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Charts in Access
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 ?
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 ?
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78481
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Charts in Access
SELECT (Format([Finished],"DDDDD")),Sum([Totals]) AS [SumOfTotals] FROM [qryGraph] GROUP BY (Int([Finished])),(Format([Finished],"DDDDD"));
Regards
Dave
Regards
Dave
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78481
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Charts in Access
Sorry to keep on asking! What is the definition of qryFinished?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Charts in Access
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
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
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78481
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Charts in Access
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]));
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]));
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78481
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Charts in Access
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"));
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"));
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England