Grouping dates in Graphs

[adamburton]

Hello, I hope somebody can help. Its a warm afternoon in the office and Im starting to get extremely flustered with this little problem.

I need to create a graph report which displays some data grouped into weeks along one axis. I have a date field which Im using to try and group on but Im really struggling to get it to work. Does anybody know how I can group information so it displays data in week by week chunks?

Normally I would try and use the reports 'Sorting & Grouping' function but as Im using a graph/chart I dont think this comes into play.

If anybody can help me, I will be extremely grateful!

Thanks,
Adam.

 

[Rich]

Use a totals query to get the grouping levels and then use a crosstab based on the totals

 

[adamburton]

still stuck

Ive taken your advice and tried creating a totals query and crosstab. However Im still no better off. I still cannot group the data into weeks - any ideas how?

I managed to group it into months and tried to create my graph using this info. However, because the crosstab seperates the months out into 12 fields, the graph wouldnt accept all these independant fields.

If someone can put me out of my misery, please do!
thanks,
Adam.

 

[KeithB13]

Have you tried using the "datepart" function? You may have to query a query to get what you need. First, set up a querry to use the "datepart" function to get the week number out your date feild into a week number feild. Do the same for your year. In the second query, group your week and year feilds and set up a second week feild to count. You should be able to graph this data.


DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week

I hope this all makes sence.

Keith

 

[adamburton]

Thank you

Thanks Keith, what you wrote did make sense and I learnt a few new things. Unfortunately I still couldnt get the thing I wanted to work, but Im tackling it from a different angle now (I think I was being a bit unrealistic in my expectations before!).

Thanks very much for your reply anyhow.

Adam.