anylizing events

[Mike]

Hi everyone,

In the last month or two I have been learning a great deal about Microsoft
Excel and Microsoft Access to do my job and making my life easier (in the
future). I've actually created some custom functions for both.

Now here is my new problem, I have to analyze data in a certain way where I
think I have to build an actual logic engine - which isn't so hard, however
I'm trying to decide if doing what I need to do will be easier in XLS or
Access...

here is some sample data:
CASE_NUMBER Severity FIELD_MODIFIED OLD_VALUE NEW_VALUE LocalTime
CAN003377417 3 SUBMIT SUBMIT 5/9/2006 09:32:44
CAN003377417 3 ASSIGNEE Person's Name 5/10/2006 09:05:45
CAN003377417 3 STATUS ASSIGNED WORKINPROGRESS 5/10/2006 09:55:22
CAN003377417 3 SUPPORTGROUP Workgroup Name Workgroup Name 5/11/2006 11:28:42
CAN003377417 3 STATUS WORKINPROGRESS PENDING DEPLOYMENT 5/11/2006 11:28:42
CAN003377417 3 STATUS PENDING DEPLOYMENT ASSIGNED 5/11/2006 13:50:28
CAN003377417 3 ASSIGNEE Person's Name 5/11/2006 13:50:28
CAN003377417 3 SUPPORTGROUP Workgroup Name Workgroup Name 5/11/2006 13:50:28
CAN003377417 3 PRODUCT OPERATING SYSTEM VMWARE 5/11/2006 13:57:35
CAN003377417 3 SUPPORTGROUP Workgroup Name Workgroup Name 5/11/2006 13:57:36
CAN003377417 3 STATUS ASSIGNED PENDING DEPLOYMENT 5/11/2006 13:57:36
CAN003377417 3 PRODUCTCATEGORY PC UNIX-APPLICATIONS 5/11/2006 13:57:36
CAN003377417 3 PRODUCT VMWARE OPERATING SYSTEM 5/11/2006 17:29:18
CAN003377417 3 STATUS PENDING DEPLOYMENT ASSIGNED 5/11/2006 17:29:20
CAN003377417 3 PRODUCTCATEGORY UNIX-APPLICATIONS PC 5/11/2006 17:29:20
CAN003377417 3 SUPPORTGROUP Workgroup Name Workgroup Name 5/11/2006 17:29:20
CAN003377417 3 ASSIGNEE Person's Name 5/12/2006 14:06:47
CAN003377417 3 STATUS ASSIGNED PENDING CUSTOMER 5/12/2006 14:30:51
CAN003377417 3 STATUS PENDING CUSTOMER READY FOR TEST 5/15/2006 12:24:54
CAN003377417 3 STATUS READY FOR TEST PENDING CUSTOMER 5/17/2006 16:12:55
CAN003377417 3 STATUS PENDING CUSTOMER READY FOR TEST 5/18/2006 11:12:44
CAN003377417 3 STATUS READY FOR TEST PENDING CUSTOMER 5/22/2006 12:28:49
CAN003377417 3 STATUS PENDING CUSTOMER WORKINPROGRESS 5/24/2006 15:23:08
CAN003377417 3 STATUS WORKINPROGRESS READY FOR TEST 5/26/2006 11:55:49
CAN003377417 3 ASSIGNEE Person's Name Person's Name 5/31/2006 15:23:37
CAN003377417 3 STATUS READY FOR TEST PENDING CUSTOMER 5/31/2006 15:25:38
CAN003377417 3 ASSIGNEE Person's Name Person's Name 5/31/2006 16:02:03
CAN003377417 3 STATUS PENDING CUSTOMER CLOSED 5/31/2006 16:02:03

As you see I have a case number (a work order) with multiple events, I have
replace people's acutal names with "Person's Name" and the actual support
groups with "Workgroup Name" in order to protect my client.

No, of course the events in each work order will not be the same. I need to
figure out how much time it took for the work order to be picked up the sent
off or closed out, every time it goes to a specific queue. I also have to
remove any pending time in between events I'm calculating.

I hope that's enough to explain what I'm trying to do. Has anyone here done
anything similar?

Mike

 

[John Vinson]

No, of course the events in each work order will not be the same. I need to
figure out how much time it took for the work order to be picked up the sent
off or closed out, every time it goes to a specific queue. I also have to
remove any pending time in between events I'm calculating.

Are you trying to store all this information in one table? That's
probably not the best approach! You need to have a separate table for
each kind of Entity of importance to your system. You should NOT have
the same person's name entered multiple times; there should be a
People table with one record per person (and a PersonID as a primary
key for linking to other tables). You'll apparently also need a
WorkOrder table (one record per workorder) and an Actions table (one
record for each action done on a workorder).

John W. Vinson[MVP]

 

[Mike]

That would definately make my life easier, I forgot to mention that I am
importing this information from a call tracking system which doesn't allow me
to do this easily. Also, I would be doing reports on a weekly and monthly
basis where the data would be imported as needed.

 

[John Vinson]

That would definately make my life easier, I forgot to mention that I am
importing this information from a call tracking system which doesn't allow me
to do this easily. Also, I would be doing reports on a weekly and monthly
basis where the data would be imported as needed.

In that case I'd suggest creating a set of properly normalized tables;
and running Append queries to split the data out from your imported
file to the normalized tables. Without knowing more about the nature
of your data I can't be much more specific than that.

Note that (unlike the case with some other software) you do NOT need
to create a new monthly Table in order to generate a monthly report -
instead you would store all the data in its own properly-structured
table, and then use a Query joining multiple tables, using search
criteria to limit which records are selected, and base your monthly
report on that Query.

John W. Vinson[MVP]