I am rather a newbie in Access and any help would be greatly appreciated. I import a weekly aged trial balance report, add a comment field and follow up date field. I would like to know as I input a follow up date, how many accounts that I have dated for that particular day so I can limit my follow up calls to no more than 20 calls on any given day. I don't necessarily want a restriction, I just want to see a running total. Is there a formula that I can use that would count each day as it is entered? Thanks for your help.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Counting Dates
- Thread starter abbigale
- Start date
Andrew Fergus
MrExcel MVP
- Joined
- Sep 9, 2004
- Messages
- 5,462
- Office Version
- 365
- 2021
- 2016
- Platform
- Windows
Hi abbigale,
you can do this by creating a new query, add the table that you are using, Click Query -> View Totals, add the "date" field to the query, add the account name, under the date make sure it says "Group By", sort it in ascending (or descending) order and under the account name make sure "Group By" is changed to "Count".
Save and run the query - this will provide a count by day. You may also want to put in some criteria under the date if you have a long list that includes old data, e.g. set the criteria to >=Now() if you want.
HTH, Andrew.
you can do this by creating a new query, add the table that you are using, Click Query -> View Totals, add the "date" field to the query, add the account name, under the date make sure it says "Group By", sort it in ascending (or descending) order and under the account name make sure "Group By" is changed to "Count".
Save and run the query - this will provide a count by day. You may also want to put in some criteria under the date if you have a long list that includes old data, e.g. set the criteria to >=Now() if you want.
HTH, Andrew.
Upvote
0
Thank you for your response. However, I would rather not run two separate queries and see the totals after the fact. I would like to see the number of calls caculated in a separate column in the query that I am using at the time, as the dates are input. Would there be a formula that I could insert that would actually count each date as it is input into the follow up date fields? Again.. thanks for your help.
Upvote
0
Andrew Fergus
MrExcel MVP
- Joined
- Sep 9, 2004
- Messages
- 5,462
- Office Version
- 365
- 2021
- 2016
- Platform
- Windows
How are you entering the dates? Are you entering them when viewing the data in a query? Or are you using a form based on a query?
I don't think you can have the detailed records and the count function in the same query (someone please correct me if I am wrong) given one requires a "group by" function. However, someone more familiar with coding may be able to write something that can do this for you on a form.
Upvote
0
Hi
I would very strongly suggest that you start using forms for entering data. That is what they are for, query datasheet views are not designed for this purpose.
However, check out the access help files on domain aggregate funtions, the one you would want to look at would be the DCOUNT function
to count the number of records in MyTable on a field called MyField where the date was yesterday:
=DCOUNT("[MyField]","MyTable","[DateField] > " Date()-1)
However if you use a system of forms and subforms, this would be easier to manage.
HTH
Jim
I would very strongly suggest that you start using forms for entering data. That is what they are for, query datasheet views are not designed for this purpose.
However, check out the access help files on domain aggregate funtions, the one you would want to look at would be the DCOUNT function
to count the number of records in MyTable on a field called MyField where the date was yesterday:
=DCOUNT("[MyField]","MyTable","[DateField] > " Date()-1)
However if you use a system of forms and subforms, this would be easier to manage.
HTH
Jim
Upvote
0
Similar threads
- Solved
