How many per week

[Lanason]

I have a table with a number of records. Each record has a date in it.

I wish to display a graph showing how many records were created each week.

What is the easiest way to do this ??

 

[Rich]

Use a totals query and base the graph on that

 

[Lanason]

and.....

Use a totals query and base the graph on that

Is there a calculation that totals by week ?? If so whatis it and where does it go in the query??

 

[ColinEssex]

Another method is to use the Week Number.

In the query grid put -

Format([YourDateField],"ww")

then it'll tell you the week number that a your date belongs to (calendar year not Financial year) then you can count on that.

Col

 

[Lanason]

Closer

Thanks that gets a week number but how does it tell which year it is ?? and how to get a zero reading for any weeks with no records.

and on the graph I need to show a "date" ie. a Monday.

Getting closer

 

[ColinEssex]

You never mentioned the year or the day of the week

Format([YourDateHere],"dddd" & " " & "yy")

will tell you the day of the week and the year (last 2 digits)

Col

 

[Lanason]

but

errr oopps

the year bit helps but

The original question was to try and get the graph to show how many records per week. Which suggests that the "period" on the graph could be any length and that there may be weeks with no records.

Anyway I've now got a list of how many in a week when there are records in that week. Any idea how to coveer the missing weeks.