Access Query: Count number of records in each week
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.
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.
Hi JBredensteiner,
Just add a date field to the table and set its default value to Date()
Good luck!
~Jillyn
Just add a date field to the table and set its default value to Date()
Good luck!
~Jillyn
ASKER
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")
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.
https://www.experts-exchange.com/questions/21393779/By-Week-report-access.html?query=access+week+report&clearTAFilter=true
I have no idea of how to use the first suggestion. When I put format(somedatefield,"ww")
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.
https://www.experts-exchange.com/questions/21393779/By-Week-report-access.html?query=access+week+report&clearTAFilter=true
ASKER
There is also this post, but again, I don't know how to use it. Please help
https://www.experts-exchange.com/questions/21734617/Change-Start-of-Week-from-Sunday-to-Monday-in-Access-Report.html?query=access+week+report&clearTAFilter=true
https://www.experts-exchange.com/questions/21734617/Change-Start-of-Week-from-Sunday-to-Monday-in-Access-Report.html?query=access+week+report&clearTAFilter=true
ASKER
Please help
ASKER
Does anyone know how to accomplish this? Please help it is very important that I figure this out. Thanks again,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You are the man, or woman, whichever it may be :)
Thank you so much for your help :)
Thank you so much for your help :)
Thanks ... hope it helped.
Larry
Larry
eg
format(somedatefield,"ww")