If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculate working days between records
Hi, All,
I've been trying to interpret me needs from the following thread, but have not been able to do so. When I tried to apply it to my table and fields, I'm getting some funky results: http://groups.google.com/group/micro...d50ff9465684ad I do need to do something similar. I have a table containing records of comments associated to my master record using the KeyID. I need to calculate the working days (using the dhcountworkdaysa calculation found on the Access Web) between comments to determine if our staff is contacting customers at least every three days. Where I'm struggling is with the earliest comment. If it is the first comment, I would like it to return a zero value since that's that jumping off point (accept in the case of New records, I want to calculate the difference between the last status change and the current date). Any help would be greatly appreciated. I have pasted some sample data below. KeyID StatusDesc strCSRegion datStatusChange Comment_Date 10113161108 New CENTRAL 5/28/2010 12:07:24 PM 5/30/2010 9:36:02 PM 10168352464 Contacting Client CENTRAL 4/26/2010 10:56:45 AM 4/26/2010 2:25:54 PM 10168352464 Contacting Client CENTRAL 4/26/2010 10:56:45 AM 5/3/2010 4:43:02 PM 10424957372 Contacting Client CENTRAL 5/28/2010 10:22:58 AM 5/28/2010 9:08:29 AM 10424957372 Contacting Client CENTRAL 5/28/2010 10:22:58 AM 5/28/2010 9:08:29 AM 10424957372 Contacting Client CENTRAL 5/28/2010 10:22:58 AM 5/28/2010 10:40:21 AM 1047233653357 New WEST 5/28/2010 10:29:18 AM 5/30/2010 9:36:02 PM |
#2
|
|||
|
|||
Calculate working days between records
Try this, I know it works since I've been using it for 13 years:
http://www.mvps.org/access/datetime/date0006.htm -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access Co-author: "Access 2010 Solutions", published by Wiley "kidkosmo" wrote in message ... Hi, All, I've been trying to interpret me needs from the following thread, but have not been able to do so. When I tried to apply it to my table and fields, I'm getting some funky results: http://groups.google.com/group/micro...d50ff9465684ad I do need to do something similar. I have a table containing records of comments associated to my master record using the KeyID. I need to calculate the working days (using the dhcountworkdaysa calculation found on the Access Web) between comments to determine if our staff is contacting customers at least every three days. Where I'm struggling is with the earliest comment. If it is the first comment, I would like it to return a zero value since that's that jumping off point (accept in the case of New records, I want to calculate the difference between the last status change and the current date). Any help would be greatly appreciated. I have pasted some sample data below. KeyID StatusDesc strCSRegion datStatusChange Comment_Date 10113161108 New CENTRAL 5/28/2010 12:07:24 PM 5/30/2010 9:36:02 PM 10168352464 Contacting Client CENTRAL 4/26/2010 10:56:45 AM 4/26/2010 2:25:54 PM 10168352464 Contacting Client CENTRAL 4/26/2010 10:56:45 AM 5/3/2010 4:43:02 PM 10424957372 Contacting Client CENTRAL 5/28/2010 10:22:58 AM 5/28/2010 9:08:29 AM 10424957372 Contacting Client CENTRAL 5/28/2010 10:22:58 AM 5/28/2010 9:08:29 AM 10424957372 Contacting Client CENTRAL 5/28/2010 10:22:58 AM 5/28/2010 10:40:21 AM 1047233653357 New WEST 5/28/2010 10:29:18 AM 5/30/2010 9:36:02 PM |
#3
|
|||
|
|||
Calculate working days between records
On May 31, 1:08*pm, "Arvin Meyer [MVP]" wrote:
Try this, I know it works since I've been using it for 13 years: http://www.mvps.org/access/datetime/date0006.htm -- Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.accessmvp.comhttp://www.mvps.org/access Co-author: "Access 2010 Solutions", published by Wiley "kidkosmo" wrote in message ... Hi, All, I've been trying to interpret me needs from the following thread, but have not been able to do so. *When I tried to apply it to my table and fields, I'm getting some funky results: http://groups.google.com/group/micro...browse_thread/... I do need to do something similar. *I have a table containing records of comments associated to my master record using the KeyID. *I need to calculate the working days (using the dhcountworkdaysa calculation found on the Access Web) between comments to determine if our staff is contacting customers at least every three days. *Where I'm struggling is with the earliest comment. *If it is the first comment, I would like it to return a zero value since that's that jumping off point (accept in the case of New records, I want to calculate the difference between the last status change and the current date). Any help would be greatly appreciated. *I have pasted some sample data below. KeyID StatusDesc strCSRegion datStatusChange Comment_Date 10113161108 New CENTRAL 5/28/2010 12:07:24 PM 5/30/2010 9:36:02 PM 10168352464 Contacting Client CENTRAL 4/26/2010 10:56:45 AM 4/26/2010 2:25:54 PM 10168352464 Contacting Client CENTRAL 4/26/2010 10:56:45 AM 5/3/2010 4:43:02 PM 10424957372 Contacting Client CENTRAL 5/28/2010 10:22:58 AM 5/28/2010 9:08:29 AM 10424957372 Contacting Client CENTRAL 5/28/2010 10:22:58 AM 5/28/2010 9:08:29 AM 10424957372 Contacting Client CENTRAL 5/28/2010 10:22:58 AM 5/28/2010 10:40:21 AM 1047233653357 New WEST 5/28/2010 10:29:18 AM 5/30/2010 9:36:02 PM Thanks, Arvin. That does give me an alternative to using the dhcountworkdaysa function when calculating two dates within the same records. Where I'm getting stuck, though, is comparing two dates between consecutive records. Here's what I'm hoping to achieve: KeyID Date Entered Comment Date Work days since last comment 1234 1/24/2010 1/24/2010 0 (since it's a new record) 1234 1/24/2010 1/26/2010 2 1234 1/24/2010 1/28/2010 2 12345 5/30/2010 1 (since no comments were entered, use current date as default) I hope that helps clarify |
#4
|
|||
|
|||
Calculate working days between records
"kidkosmo" wrote in message ... Thanks, Arvin. That does give me an alternative to using the dhcountworkdaysa function when calculating two dates within the same records. Where I'm getting stuck, though, is comparing two dates between consecutive records. Here's what I'm hoping to achieve: KeyID Date Entered Comment Date Work days since last comment 1234 1/24/2010 1/24/2010 0 (since it's a new record) 1234 1/24/2010 1/26/2010 2 1234 1/24/2010 1/28/2010 2 12345 5/30/2010 1 (since no comments were entered, use current date as default) The sample above appears to be from the same record, but maybe it difficult to read with newsreader wrapping. It is possible to do consecutive records, but it will take some time to write and debug the code to do it. What you will need to do is to build a recordset of the records that you want to compare, sorted on the field that you are comparing on. Now start looping through the recordset and take the first value, and save it to a variable, then subtract the next row of data, and update or append the result to a table. Then go to the next row after saving the previous row to the same variable. Just keep looping until you've finished. When you get it (it will take a while to debug) please post it back to this thread. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access Co-author: "Access 2010 Solutions", published by Wiley |
Thread Tools | |
Display Modes | |
|
|