How to group by week

[accessmatt]

I have a list of rows in a table with a date field in every row. How can I pull out that data in a way that counts the number of rows in a certain week.



So basically, I want to report on entries grouped by week.

Thanks,

Matt
new to access

[alansidman]

In a new field in your query, create an expression using the DatePart Function

Week: DatePart("ww",DateFieldName)

Then create a new query using the query with your Week Field and create an aggregate query Group on the week and count the date field.

Here is the SQL for that

SELECT Query1.Week, Count(Query1.Date1) AS CountOfDate1
FROM Query1
GROUP BY Query1.Week;

Alan

[accessmatt]

Hi Alan, thanks for trying to help but I'm not totally clear on what to do from your answer.

I understand that I'm creating another field using the DatePart function in my current query but why am I doing that?

Second, why do I need to create another query??

Please try to break this down a bit more.

thanks!

[alansidman]

Access won't let you combine easily an aggregate query and a calculation query. Therefore, you create the first one to get the week and use it as a basis to aggregate the information you want. When you run the queries, you will only have to run the second one as it will run from the first one. Look at the example I have attached.

Forgot to attach. See next thread.

[alansidman]

See attachment. One table and two queries.

[boblarson]

The other way, if this is truly in a report, is to just pull the data without grouping and then use the report's SORTING AND GROUPING to do the work for you.

[alansidman]

Bob;
Excellent point. I do so few "reports" that I tend to think of how to do it in Queries and not in reports. Great thing about forums is there is always someone that has a different approach from what you think.

Alan

[accessmatt]

Alan,

Thank you for that very helpful example. I was able to replicate it in my db.

However 2 other issues have come up: 1.) I have multiple years in my table and 2.) I really want to see weeks by date (starting on Sunday). So for example, this week is, 9/18/11. I think they can both be solved by printing week like 9/18/11 instead of by the number of the week but hHow can I adjust my query to see report that way?

THANKS!
Matt

PS: Bob, Thanks for your comment about making a report. That's what I was originally thinking but this way works nicely too and I can make a report from it to make it look better after I've done!

[alansidman]

Added a year to the week notation. Look at the db attached.

[accessmatt]

Thanks Alan but how can I show the weeks so that they're they're actually in day/month/year format?

[alansidman]

An Expression is required, Put this in a new field:

=iif(DatePart("w",Date1)=1,DateAdd("d",6,Date1),ii f(DatePart("w",Date1)=2,DateAdd("d",5,Date1),iif(D atePart("w",Date1)=3,DateAdd("d",4,Date1),iif(Date Part("w",Date1)=4,DateAdd("d",3,Date1),iif(DatePar t("w",Date1)=5,DateAdd("d",2,Date1),iif(DatePart(" w",Date1)=6,DateAdd("d",6,Date1),Date1))))))

See Attached.

[boblarson]

PS: Bob, Thanks for your comment about making a report. That's what I was originally thinking but this way works nicely too and I can make a report from it to make it look better after I've done!

Believe it or not, it would have been extremely easy to use the report to do not only your grouping but to display your dates by week in Date format without all of the hassle. But, that's the way it goes sometimes. People sometimes take the harder route because it sounds easier than the easy route.

[accessmatt]

Bob,

How can I do this with a report? I'm able to do the grouping but I need to know how to show the Dates in Date format and also roll up all of the entries so that I only see a count of number of entries grouped by week with full date.

Matt

[boblarson]

I don't have time to try to write all of that out. Can you upload a copy of your database so I can take a look at it? I think it may be easier, for me at least, to be able to go do it and then you can see what I did.

[accessmatt]

Ok, thanks Bob, here it is. I couldn't get it less then 500kb so I'm giving you a link to it where I put it on the web: http://goo.gl/KqQD4

I need to see the amount of jobs that I applied to grouped by week. With the week spelled out (i.e. week of 9/25/2011, starting every Sunday)