Graph changing date order

J

Jon

I have a chart in a report in Access 2002 that seems to be doing
something odd with the date values. The underlying query has results
which are aggregated by month for one full year's worth of information,
for example, Aug-05, Sep-05, Oct-05, Nov-05, Dec-05, Jan-06, Feb-06,
Mar-06, Apr-06, May-06, Jun-06, Jul-06.

The problem is the chart is displaying the information as followed:
Jan-06, Feb-06, Mar-06, Apr-06, May-06, Jun-06, Jul-06, Aug-06, Sep-06,
Oct-06, Dec-06. It's starting at the wrong point (January instead of
August), and it's assigning the wrong year to the months between August
and December. The values for the months are correct - e.g., Sep-06 on
the chart has the proper value for Sep-05.

The part of the underlying query that deals with the month is this:

MonthYear: Format$([shift_calc.Date],"mmm-yy")

so the information should still be in a form that it can manipulate.

How do I get Access to display the dates in the fashion I want?
 
F

fredg

I have a chart in a report in Access 2002 that seems to be doing
something odd with the date values. The underlying query has results
which are aggregated by month for one full year's worth of information,
for example, Aug-05, Sep-05, Oct-05, Nov-05, Dec-05, Jan-06, Feb-06,
Mar-06, Apr-06, May-06, Jun-06, Jul-06.

The problem is the chart is displaying the information as followed:
Jan-06, Feb-06, Mar-06, Apr-06, May-06, Jun-06, Jul-06, Aug-06, Sep-06,
Oct-06, Dec-06. It's starting at the wrong point (January instead of
August), and it's assigning the wrong year to the months between August
and December. The values for the months are correct - e.g., Sep-06 on
the chart has the proper value for Sep-05.

The part of the underlying query that deals with the month is this:

MonthYear: Format$([shift_calc.Date],"mmm-yy")

so the information should still be in a form that it can manipulate.

How do I get Access to display the dates in the fashion I want?
Click to expand...

You wish to sort differently than Access sorts, therefore you must
include a field in the query (graph rowsource) that tells the graph
how you want the data sorted.
Add a new column to the query:

SortThis:IIf(Year([DateField]) =
2005,Month([DateField])-7),Month([DateField])+5)

Sort the query on this column.

You'll also want to limit the data to 8/1/2005 to 7/31/2006

8/2005 = 1
etc.
12/2005 = 5
1/2006 = 6
etc.
7/2006 = 12

You can make it more versatile for future years by using:

SortThis:IIf(Year([DateField]) = [Enter start year],
Month([DateField])-7,Month([DateField])+5)

and you'll be prompted each time.
 
J

Jon

Okay, I think that'll solve it for now... but the chart will always
display monthly values for one full year, ending in the current month.
For instance the chart is supposed to display from Aug 05 to Jul 06 for
this month, next month it'll go from Sep-05 to Aug-06; if I'd generated
it last month, it would've gone from Jul-05 to Jun-06.

So how does your solution change then to reflect the changing month?
I have a chart in a report in Access 2002 that seems to be doing
something odd with the date values. The underlying query has results
which are aggregated by month for one full year's worth of information,
for example, Aug-05, Sep-05, Oct-05, Nov-05, Dec-05, Jan-06, Feb-06,
Mar-06, Apr-06, May-06, Jun-06, Jul-06.

The problem is the chart is displaying the information as followed:
Jan-06, Feb-06, Mar-06, Apr-06, May-06, Jun-06, Jul-06, Aug-06, Sep-06,
Oct-06, Dec-06. It's starting at the wrong point (January instead of
August), and it's assigning the wrong year to the months between August
and December. The values for the months are correct - e.g., Sep-06 on
the chart has the proper value for Sep-05.

The part of the underlying query that deals with the month is this:

MonthYear: Format$([shift_calc.Date],"mmm-yy")

so the information should still be in a form that it can manipulate.

How do I get Access to display the dates in the fashion I want?
Click to expand...

You wish to sort differently than Access sorts, therefore you must
include a field in the query (graph rowsource) that tells the graph
how you want the data sorted.
Add a new column to the query:

SortThis:IIf(Year([DateField]) =
2005,Month([DateField])-7),Month([DateField])+5)

Sort the query on this column.

You'll also want to limit the data to 8/1/2005 to 7/31/2006

8/2005 = 1
etc.
12/2005 = 5
1/2006 = 6
etc.
7/2006 = 12

You can make it more versatile for future years by using:

SortThis:IIf(Year([DateField]) = [Enter start year],
Month([DateField])-7,Month([DateField])+5)

and you'll be prompted each time.
Click to expand...
 
F

fredg

Okay, I think that'll solve it for now... but the chart will always
display monthly values for one full year, ending in the current month.
For instance the chart is supposed to display from Aug 05 to Jul 06 for
this month, next month it'll go from Sep-05 to Aug-06; if I'd generated
it last month, it would've gone from Jul-05 to Jun-06.

So how does your solution change then to reflect the changing month?
I have a chart in a report in Access 2002 that seems to be doing
something odd with the date values. The underlying query has results
which are aggregated by month for one full year's worth of information,
for example, Aug-05, Sep-05, Oct-05, Nov-05, Dec-05, Jan-06, Feb-06,
Mar-06, Apr-06, May-06, Jun-06, Jul-06.

The problem is the chart is displaying the information as followed:
Jan-06, Feb-06, Mar-06, Apr-06, May-06, Jun-06, Jul-06, Aug-06, Sep-06,
Oct-06, Dec-06. It's starting at the wrong point (January instead of
August), and it's assigning the wrong year to the months between August
and December. The values for the months are correct - e.g., Sep-06 on
the chart has the proper value for Sep-05.

The part of the underlying query that deals with the month is this:

MonthYear: Format$([shift_calc.Date],"mmm-yy")

so the information should still be in a form that it can manipulate.

How do I get Access to display the dates in the fashion I want?
Click to expand...

You wish to sort differently than Access sorts, therefore you must
include a field in the query (graph rowsource) that tells the graph
how you want the data sorted.
Add a new column to the query:

SortThis:IIf(Year([DateField]) =
2005,Month([DateField])-7),Month([DateField])+5)

Sort the query on this column.

You'll also want to limit the data to 8/1/2005 to 7/31/2006

8/2005 = 1
etc.
12/2005 = 5
1/2006 = 6
etc.
7/2006 = 12

You can make it more versatile for future years by using:

SortThis:IIf(Year([DateField]) = [Enter start year],
Month([DateField])-7,Month([DateField])+5)

and you'll be prompted each time.
Click to expand...
Click to expand...

Just change the expression to:

SortThis:
IIf(Year([ADate])=Year(Date())-1,Month([ADate])-7,IIf(Year([ADate])=Year(Date()),Month([ADate])+5,Month([ADate])+17))

Note: There is no need for the SortThis value to be between 1 to 12.

Change my [ADate] to the actual name of your date field.

Filter the records so that only 12 months appear.
 
J

Jon

Hmm... tried to implement your suggestion, but found myself seeing
unchanged results, so I think I need to spell out further what fool
things I'm doing here. <G>

Here's the underlying query from which the chart is formed:

PARAMETERS [forms]![frmTotalEfficiency]![cboProcess] IEEEDouble;
SELECT Format$([shift_calc.Date],"mmm-yy") AS MonthYear,
shift_calc.Shift_Num,
(Sum(Raw.est_setup)+Sum(Raw.est_run))/(Sum(Raw.setup_min)+Sum(Raw.run_min))
AS TotalEff
FROM Raw INNER JOIN shift_calc ON Raw.ID = shift_calc.ID
GROUP BY Format$([shift_calc.Date],"mmm-yy"), shift_calc.Shift_Num,
Raw.process_id
HAVING (((Raw.process_id)=[forms]![frmTotalEfficiency]![cboProcess])
AND
((Last(shift_calc.Date))>=DateSerial((Year(Now())-1),Month(Now())+1,1)))
ORDER BY Last(shift_calc.Date), shift_calc.Shift_Num;

This gives me a result like this:

MonthYear Shift_Num TotalEff
Aug-05 1 57.63%
Aug-05 2 56.89%
Aug-05 3 72.79%
Sep-05 1 52.66%
Sep-05 2 58.17%
Sep-05 3 73.67%
Oct-05 2 49.69%
Oct-05 1 59.77%
Oct-05 3 67.55%
Nov-05 2 45.17%
Nov-05 1 53.64%
Nov-05 3 76.68%
etc...

So the original data will always constitute 1 full year. That query
(called qryTotalEfficiencyByMonth) is used to draw a column chart, with
the months being the X axis and the percentages as the Y axis. Here's
the Row Source SQL for that chart:

TRANSFORM Sum(qryTotalEfficiencyByMonth.TotalEff) AS SumOfTotalEff
SELECT qryTotalEfficiencyByMonth.MonthYear
FROM qryTotalEfficiencyByMonth
GROUP BY qryTotalEfficiencyByMonth.MonthYear
PIVOT qryTotalEfficiencyByMonth.Shift_Num;

So can you tell me then where I should implement your change? At
qryTotalEfficiencyByMonth? Or at the Row Source SQL?
Okay, I think that'll solve it for now... but the chart will always
display monthly values for one full year, ending in the current month.
For instance the chart is supposed to display from Aug 05 to Jul 06 for
this month, next month it'll go from Sep-05 to Aug-06; if I'd generated
it last month, it would've gone from Jul-05 to Jun-06.

So how does your solution change then to reflect the changing month?
On 11 Jul 2006 09:01:13 -0700, Jon wrote:

I have a chart in a report in Access 2002 that seems to be doing
something odd with the date values. The underlying query has results
which are aggregated by month for one full year's worth of information,
for example, Aug-05, Sep-05, Oct-05, Nov-05, Dec-05, Jan-06, Feb-06,
Mar-06, Apr-06, May-06, Jun-06, Jul-06.

The problem is the chart is displaying the information as followed:
Jan-06, Feb-06, Mar-06, Apr-06, May-06, Jun-06, Jul-06, Aug-06, Sep-06,
Oct-06, Dec-06. It's starting at the wrong point (January instead of
August), and it's assigning the wrong year to the months between August
and December. The values for the months are correct - e.g., Sep-06 on
the chart has the proper value for Sep-05.

The part of the underlying query that deals with the month is this:

MonthYear: Format$([shift_calc.Date],"mmm-yy")

so the information should still be in a form that it can manipulate.

How do I get Access to display the dates in the fashion I want?

You wish to sort differently than Access sorts, therefore you must
include a field in the query (graph rowsource) that tells the graph
how you want the data sorted.
Add a new column to the query:

SortThis:IIf(Year([DateField]) =
2005,Month([DateField])-7),Month([DateField])+5)

Sort the query on this column.

You'll also want to limit the data to 8/1/2005 to 7/31/2006

8/2005 = 1
etc.
12/2005 = 5
1/2006 = 6
etc.
7/2006 = 12

You can make it more versatile for future years by using:

SortThis:IIf(Year([DateField]) = [Enter start year],
Month([DateField])-7,Month([DateField])+5)

and you'll be prompted each time.
Click to expand...
Click to expand...

Just change the expression to:

SortThis:
IIf(Year([ADate])=Year(Date())-1,Month([ADate])-7,IIf(Year([ADate])=Year(Date()),Month([ADate])+5,Month([ADate])+17))

Note: There is no need for the SortThis value to be between 1 to 12.

Change my [ADate] to the actual name of your date field.

Filter the records so that only 12 months appear.
Click to expand...
 
S

SA

Jon:

Add a new field to the underlying query (source query) that uses the
DatePart Function to only show the year in the resulting field., then in
your report and in the chart query, make this new field the primary sort
field and your current MonthYear as the secondary sort. That should get the
data in the sort you want.
--
SA
ACG Soft
http://www.groupacg.com

Jon said:
Hmm... tried to implement your suggestion, but found myself seeing
unchanged results, so I think I need to spell out further what fool
things I'm doing here. <G>

Here's the underlying query from which the chart is formed:

PARAMETERS [forms]![frmTotalEfficiency]![cboProcess] IEEEDouble;
SELECT Format$([shift_calc.Date],"mmm-yy") AS MonthYear,
shift_calc.Shift_Num,
(Sum(Raw.est_setup)+Sum(Raw.est_run))/(Sum(Raw.setup_min)+Sum(Raw.run_min))
AS TotalEff
FROM Raw INNER JOIN shift_calc ON Raw.ID = shift_calc.ID
GROUP BY Format$([shift_calc.Date],"mmm-yy"), shift_calc.Shift_Num,
Raw.process_id
HAVING (((Raw.process_id)=[forms]![frmTotalEfficiency]![cboProcess])
AND
((Last(shift_calc.Date))>=DateSerial((Year(Now())-1),Month(Now())+1,1)))
ORDER BY Last(shift_calc.Date), shift_calc.Shift_Num;

This gives me a result like this:

MonthYear Shift_Num TotalEff
Aug-05 1 57.63%
Aug-05 2 56.89%
Aug-05 3 72.79%
Sep-05 1 52.66%
Sep-05 2 58.17%
Sep-05 3 73.67%
Oct-05 2 49.69%
Oct-05 1 59.77%
Oct-05 3 67.55%
Nov-05 2 45.17%
Nov-05 1 53.64%
Nov-05 3 76.68%
etc...

So the original data will always constitute 1 full year. That query
(called qryTotalEfficiencyByMonth) is used to draw a column chart, with
the months being the X axis and the percentages as the Y axis. Here's
the Row Source SQL for that chart:

TRANSFORM Sum(qryTotalEfficiencyByMonth.TotalEff) AS SumOfTotalEff
SELECT qryTotalEfficiencyByMonth.MonthYear
FROM qryTotalEfficiencyByMonth
GROUP BY qryTotalEfficiencyByMonth.MonthYear
PIVOT qryTotalEfficiencyByMonth.Shift_Num;

So can you tell me then where I should implement your change? At
qryTotalEfficiencyByMonth? Or at the Row Source SQL?
Okay, I think that'll solve it for now... but the chart will always
display monthly values for one full year, ending in the current month.
For instance the chart is supposed to display from Aug 05 to Jul 06 for
this month, next month it'll go from Sep-05 to Aug-06; if I'd generated
it last month, it would've gone from Jul-05 to Jun-06.

So how does your solution change then to reflect the changing month?

fredg wrote:
On 11 Jul 2006 09:01:13 -0700, Jon wrote:

I have a chart in a report in Access 2002 that seems to be doing
something odd with the date values. The underlying query has results
which are aggregated by month for one full year's worth of
information,
for example, Aug-05, Sep-05, Oct-05, Nov-05, Dec-05, Jan-06, Feb-06,
Mar-06, Apr-06, May-06, Jun-06, Jul-06.

The problem is the chart is displaying the information as followed:
Jan-06, Feb-06, Mar-06, Apr-06, May-06, Jun-06, Jul-06, Aug-06,
Sep-06,
Oct-06, Dec-06. It's starting at the wrong point (January instead of
August), and it's assigning the wrong year to the months between
August
and December. The values for the months are correct - e.g., Sep-06 on
the chart has the proper value for Sep-05.

The part of the underlying query that deals with the month is this:

MonthYear: Format$([shift_calc.Date],"mmm-yy")

so the information should still be in a form that it can manipulate.

How do I get Access to display the dates in the fashion I want?

You wish to sort differently than Access sorts, therefore you must
include a field in the query (graph rowsource) that tells the graph
how you want the data sorted.
Add a new column to the query:

SortThis:IIf(Year([DateField]) =
2005,Month([DateField])-7),Month([DateField])+5)

Sort the query on this column.

You'll also want to limit the data to 8/1/2005 to 7/31/2006

8/2005 = 1
etc.
12/2005 = 5
1/2006 = 6
etc.
7/2006 = 12

You can make it more versatile for future years by using:

SortThis:IIf(Year([DateField]) = [Enter start year],
Month([DateField])-7,Month([DateField])+5)

and you'll be prompted each time.
Click to expand...

Just change the expression to:

SortThis:
IIf(Year([ADate])=Year(Date())-1,Month([ADate])-7,IIf(Year([ADate])=Year(Date()),Month([ADate])+5,Month([ADate])+17))

Note: There is no need for the SortThis value to be between 1 to 12.

Change my [ADate] to the actual name of your date field.

Filter the records so that only 12 months appear.
Click to expand...
Click to expand...
 
J

Jon

Just to let folks know, I decided on a slightly different course, based
on the kind advice & feedback I was getting. What I did was changed the
MonthYear function of the source query to this:

MonthYear: Year(shift_calc!Date) & "/" &
IIf(Month(shift_calc!Date)<10,0) & Month(shift_calc!Date)

No changes were needed in the chart query, and the chart now graphs
perfectly. :)
Jon:

Add a new field to the underlying query (source query) that uses the
DatePart Function to only show the year in the resulting field., then in
your report and in the chart query, make this new field the primary sort
field and your current MonthYear as the secondary sort. That should get the
data in the sort you want.
--
SA
ACG Soft
http://www.groupacg.com

Jon said:
Hmm... tried to implement your suggestion, but found myself seeing
unchanged results, so I think I need to spell out further what fool
things I'm doing here. <G>

Here's the underlying query from which the chart is formed:

PARAMETERS [forms]![frmTotalEfficiency]![cboProcess] IEEEDouble;
SELECT Format$([shift_calc.Date],"mmm-yy") AS MonthYear,
shift_calc.Shift_Num,
(Sum(Raw.est_setup)+Sum(Raw.est_run))/(Sum(Raw.setup_min)+Sum(Raw.run_min))
AS TotalEff
FROM Raw INNER JOIN shift_calc ON Raw.ID = shift_calc.ID
GROUP BY Format$([shift_calc.Date],"mmm-yy"), shift_calc.Shift_Num,
Raw.process_id
HAVING (((Raw.process_id)=[forms]![frmTotalEfficiency]![cboProcess])
AND
((Last(shift_calc.Date))>=DateSerial((Year(Now())-1),Month(Now())+1,1)))
ORDER BY Last(shift_calc.Date), shift_calc.Shift_Num;

This gives me a result like this:

MonthYear Shift_Num TotalEff
Aug-05 1 57.63%
Aug-05 2 56.89%
Aug-05 3 72.79%
Sep-05 1 52.66%
Sep-05 2 58.17%
Sep-05 3 73.67%
Oct-05 2 49.69%
Oct-05 1 59.77%
Oct-05 3 67.55%
Nov-05 2 45.17%
Nov-05 1 53.64%
Nov-05 3 76.68%
etc...

So the original data will always constitute 1 full year. That query
(called qryTotalEfficiencyByMonth) is used to draw a column chart, with
the months being the X axis and the percentages as the Y axis. Here's
the Row Source SQL for that chart:

TRANSFORM Sum(qryTotalEfficiencyByMonth.TotalEff) AS SumOfTotalEff
SELECT qryTotalEfficiencyByMonth.MonthYear
FROM qryTotalEfficiencyByMonth
GROUP BY qryTotalEfficiencyByMonth.MonthYear
PIVOT qryTotalEfficiencyByMonth.Shift_Num;

So can you tell me then where I should implement your change? At
qryTotalEfficiencyByMonth? Or at the Row Source SQL?
On 11 Jul 2006 09:57:59 -0700, Jon wrote:

Okay, I think that'll solve it for now... but the chart will always
display monthly values for one full year, ending in the current month.
For instance the chart is supposed to display from Aug 05 to Jul 06 for
this month, next month it'll go from Sep-05 to Aug-06; if I'd generated
it last month, it would've gone from Jul-05 to Jun-06.

So how does your solution change then to reflect the changing month?

fredg wrote:
On 11 Jul 2006 09:01:13 -0700, Jon wrote:

I have a chart in a report in Access 2002 that seems to be doing
something odd with the date values. The underlying query has results
which are aggregated by month for one full year's worth of
information,
for example, Aug-05, Sep-05, Oct-05, Nov-05, Dec-05, Jan-06, Feb-06,
Mar-06, Apr-06, May-06, Jun-06, Jul-06.

The problem is the chart is displaying the information as followed:
Jan-06, Feb-06, Mar-06, Apr-06, May-06, Jun-06, Jul-06, Aug-06,
Sep-06,
Oct-06, Dec-06. It's starting at the wrong point (January instead of
August), and it's assigning the wrong year to the months between
August
and December. The values for the months are correct - e.g., Sep-06 on
the chart has the proper value for Sep-05.

The part of the underlying query that deals with the month is this:

MonthYear: Format$([shift_calc.Date],"mmm-yy")

so the information should still be in a form that it can manipulate.

How do I get Access to display the dates in the fashion I want?

You wish to sort differently than Access sorts, therefore you must
include a field in the query (graph rowsource) that tells the graph
how you want the data sorted.
Add a new column to the query:

SortThis:IIf(Year([DateField]) =
2005,Month([DateField])-7),Month([DateField])+5)

Sort the query on this column.

You'll also want to limit the data to 8/1/2005 to 7/31/2006

8/2005 = 1
etc.
12/2005 = 5
1/2006 = 6
etc.
7/2006 = 12

You can make it more versatile for future years by using:

SortThis:IIf(Year([DateField]) = [Enter start year],
Month([DateField])-7,Month([DateField])+5)

and you'll be prompted each time.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Just change the expression to:

SortThis:
IIf(Year([ADate])=Year(Date())-1,Month([ADate])-7,IIf(Year([ADate])=Year(Date()),Month([ADate])+5,Month([ADate])+17))

Note: There is no need for the SortThis value to be between 1 to 12.

Change my [ADate] to the actual name of your date field.

Filter the records so that only 12 months appear.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combine 2 Charts 2007 Excel 1
rolling report 3
Find longest consecutive winning streaks 7
Converting CSV text 2
Sorting CSV Data By Date 8
Dont know Excel Function 1
12 month rolling report by date 3
Need help with SQL Statement in MS Access 2003 Chart 11

Top