Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Query: Count number of records in each week

Posted on 2006-11-27
10
Medium Priority
?
1,157 Views
Last Modified: 2012-06-21
Good afternoon,
     I would like to create a query that counts the number of records in another query grouping them what week the records were entered.  I know how to do the grouping and counting, but I don't know how to tell what week the record was entered.  Please tell me how to add a field to my current query that will tell me what week the record was entered.  I would like the weeks to start on Thursdays, and end on Wednesdays, and I would like the field to be named [Week].  The name of the query is [qry_master], the date field in my query is [DateAcpt], and the table the query is pulling from is named [tbl_Main].  Thank you in advance for your help, and please be aware that I am somewhat of a novice user.  I need this ASAP, and I will be very grateful to the person that helps me out.  Thanks, Jon.
0
Comment
Question by:Jon Bredensteiner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 18022328
u can use the format command on a date to return the week number
eg

format(somedatefield,"ww")

0
 
LVL 8

Expert Comment

by:Jillyn_D
ID: 18022351
Hi JBredensteiner,

Just add a date field to the table and set its default value to Date()

Good luck!
~Jillyn
0
 
LVL 4

Expert Comment

by:Clothahump
ID: 18022402
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Jon Bredensteiner
ID: 18022552
Did I mention that I was a novice user?

I have no idea of how to use the first suggestion.  When I put format(somedatefield,"ww") in the format field it changes to "for"m"at("s\om\ed"atefiel"d",ww)" Simply formating the date is not going to help as it won't help me to start the week on Thursday, and end on Wednesday, plus I would like it to return the week range i.e. 11/30/2006 - 12/06/2006, so I actually know what week it is talking about.  Returning the week number will not help at this point.  Thank you for your help though.

I don't believe the second suggestion will help me, or at least I don't see how it would.  Thank you too for your help.

As for the third suggestion, it looks like it could help, but I don't know how to call the module into my query once I create.

Here is some helpfull information I found, but I'm not sure how to use it, or if they are talking about putting the code into a report or a query.

http://www.experts-exchange.com/Databases/MS_Access/Q_21393779.html?query=access+week+report&clearTAFilter=true
0
 

Author Comment

by:Jon Bredensteiner
ID: 18022675
There is also this post, but again, I don't know how to use it.  Please help
http://www.experts-exchange.com/Databases/MS_Access/Q_21734617.html?query=access+week+report&clearTAFilter=true
0
 

Author Comment

by:Jon Bredensteiner
ID: 18023723
Please help
0
 

Author Comment

by:Jon Bredensteiner
ID: 18030552
Does anyone know how to accomplish this?  Please help it is very important that I figure this out.  Thanks again,
0
 
LVL 9

Accepted Solution

by:
Volibrawl earned 2000 total points
ID: 18031680
Enter this formula into  a new column in your queries.  This will give you the ENDING Date of that week (Wednesday).  

Weekfinish: IIf(Weekday([Date_acpt])<5,DateAdd('d',4-Weekday([Date_Acpt]),[Date_Acpt]),DateAdd('d',11-Weekday([Date_Acpt]),[Date_Acpt]))

To get the start, just add another column WeekStart:[weekfinish]-6


You can then group by either of these fields to get your totals for the week.
0
 

Author Comment

by:Jon Bredensteiner
ID: 18033106
You are the man, or woman, whichever it may be :)
Thank you so much for your help :)
0
 
LVL 9

Expert Comment

by:Volibrawl
ID: 18039208
Thanks ... hope it helped.

Larry
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question