Grouping Data by Week Number (1 Viewer)

Hi Guys,

Just a brief - i have courses in a table with a start date and end date. Each course has a loading, and a max capacity.

I am trying to create 2 seperate graphs and do not know how to do either:

Firstly - i am trying to make a graph that on the x axis is each week in the financial year. On the y axis is either the loading or capacity (doesn't matter which for this problem). BUT on some weeks in the year, there aren't any courses AND i DO want them in the graph (basically blank spaces) - this is because the user filters based on location and i want the graph size to be identical and the user to get a 'feel' for when the classroom will be empty. - ANy help on this is appreciated!!

Secondly - i want to be able to generate a histogram of course loadings. So instead of instantaneous information, i was hoping for the histogram to represent how many people are on the course at that date - and not just the 'start date'.

Any help on either of these is appreciated!!

Chris

Simple Software Solutions

Hi

My suggestion is that you create a crosstab query and the column heading are your week numbers (Group date by week number). I know that with crosstabs if there is no data present for a particular week number, in this instance, it will not display a column. However the trick is to go to the properties of the column and select column headings. In there enter 01,02,03,etc up to week 52 or whatever then when you run the crosstab query the column will appear even if there is no data for that period. You are infact forcing the column headings to appear.

CodeMaster: