Chart date format help needed

[Malfar]

I have a database and have a report which shows a chart, showing hours used(y-axis) per week (x-axis). I used the basic chart wizard to get it set-up, however the dates along the x-axis are formatted as ww and I'm unsure how to convert them to mmm dd, yyyy format. Everything I've tried so far has not worked. Here is the current (Default) SQL that was generated to create the graph :

SELECT (Format([Dates],"WW 'YY")) ,Sum([press500]) AS [500 tonne],Sum([press600]) AS [600 Tonne],Sum([press1000]) AS [1000 Tonne] FROM [Tmp] GROUP BY (Year([Dates])*CLng(54) + DatePart("ww",[Dates],0)-1), (Format([Dates],"WW 'YY"));

This displays the bar chart properly, however the dates along the x-axis are in format of week number. Such as Jan. 01, 2007 would show as 1 '07.

Any and all help would be appreciated, I have searched all over the internet and have found nothing of help.

Thanks
Mark

 

[Malfar]

I've included pictures of what the x-axis needs to look like (x-right.jpg) which was achieved with sql code of:

SELECT ( (Format([Dates]-(Weekday([Dates])-8),"Short Date"))),Sum([press500]) AS [500 Tonne],Sum([press600]) AS [600 Tonne],Sum([press1000]) AS [1000 Tonne] FROM [Tmp]
GROUP BY (Year([Dates])*CLng(54) + DatePart("ww",[Dates],0)-1),( (Format([Dates]-(Weekday([Dates])-8),"Short Date")));

However when using this the display only shows for the first day of the week making the bars too thin to read. I've also included a picture of the display correct (wider bars) however the x-axis is in the wrong format. To achieve this the SQL code is :

SELECT ( (Format([Dates]-(Weekday([Dates])-8),"Short Date"))),Sum([press500]) AS [500 Tonne],Sum([press600]) AS [600 Tonne],Sum([press1000]) AS [1000 Tonne] FROM [Tmp] GROUP BY (Year([Dates])*CLng(54) + DatePart("ww",[Dates],0)-1),( (Format([Dates]-(Weekday([Dates])-8),"Short Date")));

Any and all help would be appreciated

-Mark

 

[namliam]

Not much help I am afraid but...

The chart where you have the days in the x-axes simply has to much values (days) to show which is why the columns get "crushed" vs the weekly graph.

There is really not much you can do about that except widen the display area of the graph allowing for both issues to be resolved
1) The crushed columns
2) The missing days

I hope that helps (a little)

 

[Malfar]

Not much help I am afraid but...

The chart where you have the days in the x-axes simply has to much values (days) to show which is why the columns get "crushed" vs the weekly graph.

There is really not much you can do about that except widen the display area of the graph allowing for both issues to be resolved
1) The crushed columns
2) The missing days

I hope that helps (a little)

The problem is, what I want for a chart, is a weekly report, not a daily one, however I need it to show the dates in the format of mm/dd/yyyy and that is where my problem lays, it shows them as ww 'yy currently and I have tried everything I could think of to change the format.

 

[namliam]

Try doing something like:
[dates]-weekday([dates])

and do that in the group by as well. It will then only show the columns for the sundays....

 

[Malfar]

Try doing something like:
[dates]-weekday([dates])

and do that in the group by as well. It will then only show the columns for the sundays....

Ok what happens here is it only shows data for the sunday, I need it to show the total for the week, I just need the date to display the first of the week. As currently it displays it as the number of the week i.e The week of jan 1 2007 will show up as 1 '07. The data that is reflected in the chart is correct, just the display for the date along the x-axis is difficult to decipher for some. I.E. Its sometimes difficult to know which week is the 33 week of the year, they would rather it display the actuall first date of that week.

EDIT P.S. I appreciate all the help so far

 

[namliam]

That is exactly what it does??? It doesnt show the amounts for sundays but shows the totals of the week (sun-sat) with the date of sunday...

SELECT [dates]-weekday([dates]) Sunday
, Sum([press500]) AS [500 Tonne]
, Sum([press600]) AS [600 Tonne]
, Sum([press1000]) AS [1000 Tonne]
FROM [Tmp]
GROUP BY [dates]-weekday([dates]) ;

Note the increased readability of the query if you format it a little.

 

[Malfar]

That is exactly what it does??? It doesnt show the amounts for sundays but shows the totals of the week (sun-sat) with the date of sunday...

SELECT [dates]-weekday([dates]) Sunday
, Sum([press500]) AS [500 Tonne]
, Sum([press600]) AS [600 Tonne]
, Sum([press1000]) AS [1000 Tonne]
FROM [Tmp]
GROUP BY [dates]-weekday([dates]) ;

Note the increased readability of the query if you format it a little.

Ok I think we are getting somewhere now, however when I try putting in exactly as you've typed out I get this error:

syntax error (missing operator) in query expression '[dates] - weekday([dates]) Sunday'.

If I take the Sunday out of the Query it will run however, it will show every day in the chart, instead of weekly, also the format then becomes a long number, I have attached an image to show what I mean.

 

[namliam]

Sorry Oracle on the brain...

SELECT [dates]-weekday([dates]) AS Sunday
, Sum([press500]) AS [500 Tonne]
, Sum([press600]) AS [600 Tonne]
, Sum([press1000]) AS [1000 Tonne]
FROM [Tmp]
GROUP BY [dates]-weekday([dates]) ;

Seems logical huh?? The results are somewhat atypical.... !! Uhm...

SELECT format([dates]-weekday([dates]),"YYYY-MM-DD") AS Sunday
, Sum([press500]) AS [500 Tonne]
, Sum([press600]) AS [600 Tonne]
, Sum([press1000]) AS [1000 Tonne]
FROM [Tmp]
GROUP BY format([dates]-weekday([dates]),"YYYY-MM-DD") ;

Or try putting a date format to the column in the design...

 

[Malfar]

Sorry Oracle on the brain...

SELECT [dates]-weekday([dates]) AS Sunday
, Sum([press500]) AS [500 Tonne]
, Sum([press600]) AS [600 Tonne]
, Sum([press1000]) AS [1000 Tonne]
FROM [Tmp]
GROUP BY [dates]-weekday([dates]) ;

Seems logical huh?? The results are somewhat atypical.... !! Uhm...

SELECT format([dates]-weekday([dates]),"YYYY-MM-DD") AS Sunday
, Sum([press500]) AS [500 Tonne]
, Sum([press600]) AS [600 Tonne]
, Sum([press1000]) AS [1000 Tonne]
FROM [Tmp]
GROUP BY format([dates]-weekday([dates]),"YYYY-MM-DD") ;

Or try putting a date format to the column in the design...

Ok, almost have it. The dates are showing up properly now, had to use the x-axis format in the chart. My SQL now looks like this:

SELECT [dates]-weekday([dates])
, Sum([press500]) AS [500 Tonne]
, Sum([press600]) AS [600 Tonne]
, Sum([press1000]) AS [1000 Tonne]
FROM [Tmp]
GROUP BY [dates]-weekday([dates]) ;

The only problem now is it is showing every day along the x-axis instead of the first of every week, almost as if the chart isn't recongnizing the grouping. The data in the chart only shows on the first of every week and is correct, however it still shows the days in between and they are all blank. Once again I have included a jpeg to show what I mean.

Once again, thanks for the help, you have at least shown me there is light at the end of the tunnel

 

[namliam]

I havent got the foggiest idea why the graph would do that except if the query would show the values as well, which I think it doesnt....

What are the results if you run the query youself (i.e. create a normal query) does it show only the sundays?

 

[Malfar]

I havent got the foggiest idea why the graph would do that except if the query would show the values as well, which I think it doesnt....

What are the results if you run the query youself (i.e. create a normal query) does it show only the sundays?

Ok this is getting weird. I ran a query to see if it showed only the first days of the week and it did. I then had the chart table displayed at the same time as the chart, and the table itself only had the first day of the week, yet the chart seems to want to show every day of the week, even tho they are all blank and empty..... If you have any ideas I would love to hear them, I have tried going through a bunch of the formatting options but have yet to find one that works...

 

[namliam]

have you tried using the format??

SELECT format([dates]-weekday([dates]), "DD-MM-YYYY")
, Sum([press500]) AS [500 Tonne]
, Sum([press600]) AS [600 Tonne]
, Sum([press1000]) AS [1000 Tonne]
FROM [Tmp]
GROUP BY format([dates]-weekday([dates]), "DD-MM-YYYY");

This should make that date into a string thus eliminating the "smart" thinking of access.... => It is a date therefor there should be days inbetween <= which it for some reason seems to be thinking...