Charting performance data on a time line

[Bobk]

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?

 

[Duane Hookom]

Can you provide the Row Source SQL of your chart control?

 

[Bobk]

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"));

 

[Steve]

Show us the SQL for qryData-1.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

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"));

Can you provide the Row Source SQL of your chart control?

 

[Bobk]

The SQL for qryData-1 is:

SELECT DISTINCT tblDATA.[Vendor No], tblDATA.Vendor, tblDATA.Date,
tblDATA.Month, tblDATA.[On Time], tblDATA.[This Mo-OT], tblDATA.[Month-Qual],
tblDATA.Quality, tblDATA.[This Mo-Qual], tblDATA.[CI Program],
tblDATA.[Quality Sys], tblDATA.[This Mo - Qual], tblDATA.[Last Visit],
tblDATA.Flexibility, tblDATA.Response, tblDATA.Profess, tblDATA.[This Mo -
Purch], tblDATA.[Last Update], *
FROM tblDATA
WHERE (((tblDATA.[Vendor No]) Between [Forms]![Select Vendor].[Qvendnost]
And [Forms]![Select Vendor].[Qvendnoend]) AND ((tblDATA.Date) Between
[Forms]![Select Vendor].[Qdatest] And [Forms]![Select Vendor].[Qdateend]))
ORDER BY tblDATA.[Vendor No], tblDATA.Date;

Show us the SQL for qryData-1.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

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"));

Can you provide the Row Source SQL of your chart control?

--
Duane Hookom
Microsoft Access MVP


:

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?

 

[Duane Hookom]

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

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"));

Can you provide the Row Source SQL of your chart control?

 

[Bobk]

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.

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

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"));

Can you provide the Row Source SQL of your chart control?

--
Duane Hookom
Microsoft Access MVP


:

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?

 

[Duane Hookom]

That's exactly how I expect it will work.
--
Duane Hookom
Microsoft Access MVP

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.

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

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"));



:

Can you provide the Row Source SQL of your chart control?

--
Duane Hookom
Microsoft Access MVP


:

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?

 

[Bobk]

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.

That's exactly how I expect it will work.
--
Duane Hookom
Microsoft Access MVP

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.

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


:

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"));



:

Can you provide the Row Source SQL of your chart control?

--
Duane Hookom
Microsoft Access MVP


:

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?