If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Charting performance data on a time line
I have a report containing an Access vertical bar chart that shows
performance on the Y axis and months on the X axis. The data is based on supplier delivery performance. The data is one entry for each month for each supplier that has delivered product. The data is the vendor number, the performance as a % and the month (which is a date as 5/1/07 for May 07, 6/1/07 for June 07 etc.) - one entry per supplier per month. This works ok unless a supplier has no activity in a month. When there is no activity there is no data for that month. When the data is plotted on the chart the performance data slips by the missing month - ie there is data for January, February and March, no data for April and data for May. The chart plots data for Jan, Feb,Mar, and Apr. The May data is plotted as April data and nothing is plotted for May. I have manually entered a blank record for April and everthing works ok. Is there a way to change the chart to force placing the data in the correct month? Or is there a way to automatically generate a blank record with a query? I want to avoid having to manually enter blank records. Anyone have an idea on how to approach this? |
#2
|
|||
|
|||
Charting performance data on a time line
Can you provide the Row Source SQL of your chart control?
-- Duane Hookom Microsoft Access MVP "Bobk" wrote: I have a report containing an Access vertical bar chart that shows performance on the Y axis and months on the X axis. The data is based on supplier delivery performance. The data is one entry for each month for each supplier that has delivered product. The data is the vendor number, the performance as a % and the month (which is a date as 5/1/07 for May 07, 6/1/07 for June 07 etc.) - one entry per supplier per month. This works ok unless a supplier has no activity in a month. When there is no activity there is no data for that month. When the data is plotted on the chart the performance data slips by the missing month - ie there is data for January, February and March, no data for April and data for May. The chart plots data for Jan, Feb,Mar, and Apr. The May data is plotted as April data and nothing is plotted for May. I have manually entered a blank record for April and everthing works ok. Is there a way to change the chart to force placing the data in the correct month? Or is there a way to automatically generate a blank record with a query? I want to avoid having to manually enter blank records. Anyone have an idea on how to approach this? |
#3
|
|||
|
|||
Charting performance data on a time line
I misspoke regarding what gets plotted. In my example the plot would be
Jan,Feb,Mar and May. No Apr. April is skipped as there is no record for that month. The SQL Is: SELECT (Format([Date],"MMM 'YY")),Sum([On Time]) AS [SumOfOn Time] FROM [qryData-1] GROUP BY (Year([Date])*12 + Month([Date])-1),(Format([Date],"MMM 'YY")); "Duane Hookom" wrote: Can you provide the Row Source SQL of your chart control? -- Duane Hookom Microsoft Access MVP "Bobk" wrote: I have a report containing an Access vertical bar chart that shows performance on the Y axis and months on the X axis. The data is based on supplier delivery performance. The data is one entry for each month for each supplier that has delivered product. The data is the vendor number, the performance as a % and the month (which is a date as 5/1/07 for May 07, 6/1/07 for June 07 etc.) - one entry per supplier per month. This works ok unless a supplier has no activity in a month. When there is no activity there is no data for that month. When the data is plotted on the chart the performance data slips by the missing month - ie there is data for January, February and March, no data for April and data for May. The chart plots data for Jan, Feb,Mar, and Apr. The May data is plotted as April data and nothing is plotted for May. I have manually entered a blank record for April and everthing works ok. Is there a way to change the chart to force placing the data in the correct month? Or is there a way to automatically generate a blank record with a query? I want to avoid having to manually enter blank records. Anyone have an idea on how to approach this? |
#4
|
|||
|
|||
Charting performance data on a time line
|
#6
|
|||
|
|||
Charting performance data on a time line
I would create a table of all possible dates. Create a query of this table
like: === qgrpMMMYY === SELECT Format([TheDate],"MMM 'YY") FROM tblAllDates WHERE TheDate Between [Forms]![Select Vendor].[Qdatest] And [Forms]![Select Vendor].[Qdateend] GROUP BY Format([TheDate],"MMM 'YY"); Then use qgrpMMMYY in your chart query with a join that includes all records from qgrpMMMYY. -- Duane Hookom Microsoft Access MVP "Bobk" wrote: I misspoke regarding what gets plotted. In my example the plot would be Jan,Feb,Mar and May. No Apr. April is skipped as there is no record for that month. The SQL Is: SELECT (Format([Date],"MMM 'YY")),Sum([On Time]) AS [SumOfOn Time] FROM [qryData-1] GROUP BY (Year([Date])*12 + Month([Date])-1),(Format([Date],"MMM 'YY")); "Duane Hookom" wrote: Can you provide the Row Source SQL of your chart control? -- Duane Hookom Microsoft Access MVP "Bobk" wrote: I have a report containing an Access vertical bar chart that shows performance on the Y axis and months on the X axis. The data is based on supplier delivery performance. The data is one entry for each month for each supplier that has delivered product. The data is the vendor number, the performance as a % and the month (which is a date as 5/1/07 for May 07, 6/1/07 for June 07 etc.) - one entry per supplier per month. This works ok unless a supplier has no activity in a month. When there is no activity there is no data for that month. When the data is plotted on the chart the performance data slips by the missing month - ie there is data for January, February and March, no data for April and data for May. The chart plots data for Jan, Feb,Mar, and Apr. The May data is plotted as April data and nothing is plotted for May. I have manually entered a blank record for April and everthing works ok. Is there a way to change the chart to force placing the data in the correct month? Or is there a way to automatically generate a blank record with a query? I want to avoid having to manually enter blank records. Anyone have an idea on how to approach this? |
#7
|
|||
|
|||
Charting performance data on a time line
Thanks for the quick response. I will have to work on this a little. If I
understand what is going to happen is that a date will be forced by the join even though there is no data to plot. This will force the chart to move to the next date and plot data if there is data to be plotted. "Duane Hookom" wrote: I would create a table of all possible dates. Create a query of this table like: === qgrpMMMYY === SELECT Format([TheDate],"MMM 'YY") FROM tblAllDates WHERE TheDate Between [Forms]![Select Vendor].[Qdatest] And [Forms]![Select Vendor].[Qdateend] GROUP BY Format([TheDate],"MMM 'YY"); Then use qgrpMMMYY in your chart query with a join that includes all records from qgrpMMMYY. -- Duane Hookom Microsoft Access MVP "Bobk" wrote: I misspoke regarding what gets plotted. In my example the plot would be Jan,Feb,Mar and May. No Apr. April is skipped as there is no record for that month. The SQL Is: SELECT (Format([Date],"MMM 'YY")),Sum([On Time]) AS [SumOfOn Time] FROM [qryData-1] GROUP BY (Year([Date])*12 + Month([Date])-1),(Format([Date],"MMM 'YY")); "Duane Hookom" wrote: Can you provide the Row Source SQL of your chart control? -- Duane Hookom Microsoft Access MVP "Bobk" wrote: I have a report containing an Access vertical bar chart that shows performance on the Y axis and months on the X axis. The data is based on supplier delivery performance. The data is one entry for each month for each supplier that has delivered product. The data is the vendor number, the performance as a % and the month (which is a date as 5/1/07 for May 07, 6/1/07 for June 07 etc.) - one entry per supplier per month. This works ok unless a supplier has no activity in a month. When there is no activity there is no data for that month. When the data is plotted on the chart the performance data slips by the missing month - ie there is data for January, February and March, no data for April and data for May. The chart plots data for Jan, Feb,Mar, and Apr. The May data is plotted as April data and nothing is plotted for May. I have manually entered a blank record for April and everthing works ok. Is there a way to change the chart to force placing the data in the correct month? Or is there a way to automatically generate a blank record with a query? I want to avoid having to manually enter blank records. Anyone have an idea on how to approach this? |
#8
|
|||
|
|||
Charting performance data on a time line
That's exactly how I expect it will work.
-- Duane Hookom Microsoft Access MVP "Bobk" wrote: Thanks for the quick response. I will have to work on this a little. If I understand what is going to happen is that a date will be forced by the join even though there is no data to plot. This will force the chart to move to the next date and plot data if there is data to be plotted. "Duane Hookom" wrote: I would create a table of all possible dates. Create a query of this table like: === qgrpMMMYY === SELECT Format([TheDate],"MMM 'YY") FROM tblAllDates WHERE TheDate Between [Forms]![Select Vendor].[Qdatest] And [Forms]![Select Vendor].[Qdateend] GROUP BY Format([TheDate],"MMM 'YY"); Then use qgrpMMMYY in your chart query with a join that includes all records from qgrpMMMYY. -- Duane Hookom Microsoft Access MVP "Bobk" wrote: I misspoke regarding what gets plotted. In my example the plot would be Jan,Feb,Mar and May. No Apr. April is skipped as there is no record for that month. The SQL Is: SELECT (Format([Date],"MMM 'YY")),Sum([On Time]) AS [SumOfOn Time] FROM [qryData-1] GROUP BY (Year([Date])*12 + Month([Date])-1),(Format([Date],"MMM 'YY")); "Duane Hookom" wrote: Can you provide the Row Source SQL of your chart control? -- Duane Hookom Microsoft Access MVP "Bobk" wrote: I have a report containing an Access vertical bar chart that shows performance on the Y axis and months on the X axis. The data is based on supplier delivery performance. The data is one entry for each month for each supplier that has delivered product. The data is the vendor number, the performance as a % and the month (which is a date as 5/1/07 for May 07, 6/1/07 for June 07 etc.) - one entry per supplier per month. This works ok unless a supplier has no activity in a month. When there is no activity there is no data for that month. When the data is plotted on the chart the performance data slips by the missing month - ie there is data for January, February and March, no data for April and data for May. The chart plots data for Jan, Feb,Mar, and Apr. The May data is plotted as April data and nothing is plotted for May. I have manually entered a blank record for April and everthing works ok. Is there a way to change the chart to force placing the data in the correct month? Or is there a way to automatically generate a blank record with a query? I want to avoid having to manually enter blank records. Anyone have an idea on how to approach this? |
#9
|
|||
|
|||
Charting performance data on a time line
Your idea worked, but I couldn't get the query to work so I just used the
date table instead. Here is the SQL I ended up with for the chart after making the join: SELECT (Format([TheDate],"mmm"" '""yy")) AS Expr2, Sum(qryOntime.[On Time]) AS [SumOfOn Time] FROM qryOntime RIGHT JOIN tblAlldates ON qryOntime.Date = tblAlldates.TheDate GROUP BY (Format([TheDate],"mmm"" '""yy")), (Year([TheDate])*12+Month([TheDate])-1) ORDER BY (Year([TheDate])*12+Month([TheDate])-1); I did have to change [Date] to [TheDate] in every case except one. This works great. I can generate the date table based on any beginning and ending date which forces the chart to display the dates whether there is data to plot or not. Thanks again for the idea. "Duane Hookom" wrote: That's exactly how I expect it will work. -- Duane Hookom Microsoft Access MVP "Bobk" wrote: Thanks for the quick response. I will have to work on this a little. If I understand what is going to happen is that a date will be forced by the join even though there is no data to plot. This will force the chart to move to the next date and plot data if there is data to be plotted. "Duane Hookom" wrote: I would create a table of all possible dates. Create a query of this table like: === qgrpMMMYY === SELECT Format([TheDate],"MMM 'YY") FROM tblAllDates WHERE TheDate Between [Forms]![Select Vendor].[Qdatest] And [Forms]![Select Vendor].[Qdateend] GROUP BY Format([TheDate],"MMM 'YY"); Then use qgrpMMMYY in your chart query with a join that includes all records from qgrpMMMYY. -- Duane Hookom Microsoft Access MVP "Bobk" wrote: I misspoke regarding what gets plotted. In my example the plot would be Jan,Feb,Mar and May. No Apr. April is skipped as there is no record for that month. The SQL Is: SELECT (Format([Date],"MMM 'YY")),Sum([On Time]) AS [SumOfOn Time] FROM [qryData-1] GROUP BY (Year([Date])*12 + Month([Date])-1),(Format([Date],"MMM 'YY")); "Duane Hookom" wrote: Can you provide the Row Source SQL of your chart control? -- Duane Hookom Microsoft Access MVP "Bobk" wrote: I have a report containing an Access vertical bar chart that shows performance on the Y axis and months on the X axis. The data is based on supplier delivery performance. The data is one entry for each month for each supplier that has delivered product. The data is the vendor number, the performance as a % and the month (which is a date as 5/1/07 for May 07, 6/1/07 for June 07 etc.) - one entry per supplier per month. This works ok unless a supplier has no activity in a month. When there is no activity there is no data for that month. When the data is plotted on the chart the performance data slips by the missing month - ie there is data for January, February and March, no data for April and data for May. The chart plots data for Jan, Feb,Mar, and Apr. The May data is plotted as April data and nothing is plotted for May. I have manually entered a blank record for April and everthing works ok. Is there a way to change the chart to force placing the data in the correct month? Or is there a way to automatically generate a blank record with a query? I want to avoid having to manually enter blank records. Anyone have an idea on how to approach this? |
Thread Tools | |
Display Modes | |
|
|