Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Data entry validation every two weeks?!?

Status
Not open for further replies.

michelemm

IS-IT--Management
Nov 9, 2001
7
US
Hi all! I'm trying to write a report that verifies that users are updating records every 2 weeks and measure this by a %. In other words, we want to know that Bob updates his sales records every 2 wks "90% of the time." We want this measured during the time the deal is active which can be measured from the start date to the close date or from the first note entry to the last(whichever is easier). Hope someone can help because I'm stuck!

Below is an example:

Record: ABC Company
Salesperson: Frank
Sale Startdate: Jan 1, 2002
Sale Closedate: March 15, 2002
History:
1/08/02 sales notes.....
1/10/02 sales notes.....
1/20/02 sales notes.....
1/24/02 sales notes.....
2/15/02 sales notes.....
2/17/02 sales notes.....
2/20/02 sales notes.....
3/14/02 sales notes.....

So starting on Jan 1, 2002 we want to check to see that there was a history sales note entered at least once every two weeks (14 days) unti the closedate (if it's easier to measure from the first note entered to the last then that's fine too).
Jan 1-15 -- note
Jan 16-29-- note
Jan 30-Feb 12--no note
Feb 13-26--note
Feb 27- March 12--no note
March 13-March 26-- note
So, out of the 6 2-wk intervals there were 4 out of the 6 wks that were updated which means the salesperson updated records as they should 66.66% of the time. I hope this is clear and that someone can help me with this.

TIA!!!!
Michele
 
Use the next() function on the date field to determine that historically there were records done within 2 weeks:

Group by Salesperson and by Company

Create the following formula for use in the detail band:

@Check2WeeksUpdate
If next((Salesperson}) = (Salesperson}
and
next((Company}) = (Company}
and
next({historydate}) > {historydate}+14
then
"Over 2 weeks"
else
"OK"

-k kai@informeddatadecisions.com
 
Thanks!
I will try that out today. I'm assuming for the percentage I would just do a count of "over 2 weeks" and "Ok" for the group and then get a percentage. Does that sound right?
Thanks again and I'll let you know how it works!
my best,
michele
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top