Illustrating process steps thru vba or query

[vurna]

Dear all,

I have a problem that I'm not sure how I should tackle.

I'm trying to find a way to illustrate process steps or escalations based on a table containing records for each change in escalation.

My table is setup in the following way.
CaseIDTimeStampOldValueNewValue1562010-02-16 11:37Tier 1Tier 21562010-02-16 11:38Tier 2Tier 31622010-02-11 12:39Tier 1Tier 21622010-02-16 13:48Tier 2Tier 31782010-02-11 12:40Tier 1Tier 21782010-02-16 13:47Tier 2Tier 31812010-02-11 12:41Tier 1Tier 21812010-02-16 13:47Tier 2Tier 31852010-02-10 15:18Tier 1Tier 21852010-02-16 13:46Tier 2Tier 31912010-02-15 13:46Tier 1Tier 3

As you can see each Case ID can be escalated to a different work tier. I want to be able to present this table in a better way.


I'm aiming at getting the end result to look something like this:
CaseIDOriginal TierEscalation 1Escalation 2Escalation 3Escalation 4156Tier 1Tier 2Tier 3162Tier 1Tier 2Tier 3178Tier 1Tier 2Tier 3181Tier 1Tier 2Tier 3185Tier 1Tier 2Tier 3191Teir 1Tier 3

With the above presentation it's easier to analyze the figures at the front end.

The problem is I can't seem to be able to achive this. I first started using a normal query then tried crosstab query which didnt work out. Then I looked at Union but it was very hard to understand. I'm thinking something more elaborate is required to reach this.

Any advice on how to proceed would be greatly appriciated!

Erik

[vurna]

EDIT:
I see the format is not captured when copy pasing from Excel. Correction for this below:

Diagram1:
CaseID | TimeStamp | OldValue | NewValue
156 2010-02-16 11:37 Tier 1 Tier 2
156 2010-02-16 11:38 Tier 2 Tier 3
162 2010-02-11 12:39 Tier 1 Tier 2
162 2010-02-16 13:48 Tier 2 Tier 3
178 2010-02-11 12:40 Tier 1 Tier 2
178 2010-02-16 13:47 Tier 2 Tier 3
181 2010-02-11 12:41 Tier 1 Tier 2
181 2010-02-16 13:47 Tier 2 Tier 3
185 2010-02-10 15:18 Tier 1 Tier 2
185 2010-02-16 13:46 Tier 2 Tier 3
191 2010-02-15 13:46 Tier 1 Tier 3


Diagram2:
CaseID | Original Tier | Escalation 1| Escalation 2 | Escalation 3 | Escalation 4
156 Tier 1 Tier 2 Tier 3
162 Tier 1 Tier 2 Tier 3
178 Tier 1 Tier 2 Tier 3
181 Tier 1 Tier 2 Tier 3
185 Tier 1 Tier 2 Tier 3
191 Teir 1 Tier 3


MS Access 2003
Regards,

[Imdabaum]

If the case can be escalated infinite number of times, then I would break out your Escalation1, Escalation2, Escalation3, Escalation4 into its own table and track Escalations as a whole.

You could then view all escalations for a CaseID ordered by date of modification. If diagram2 is your desired end result, it might make the query a little more simple if you can break it down like this.

EscalationDiagram:
EscalationID
CaseID
PrevTier
CurTier
DateModified

[vurna]

Imdabaum, I think I see where your going with this. I haven't quite got the whole picture infront of me just yet but it's starting to clear up a little. At least theoreichally, practically is a different issue though. : )

So if I where to re-arrange the records in my original table in a new table (escalation) with the fields you have suggested I would then have the means to read this table and present the records in the way I'm aiming at right?

I see how I can populate a new table with:
CaseID, PrevTier, CurTier and DateModefied.
However I'm not sure how I can populate the field "EscalationID".

EscalationID could somehow be derived by explaining to ms access that it should somehow rank each escalation within each CaseID?

[Imdabaum]

EscalationID would just be a reference to track the Escalation event. So if CaseID = 12, then you might escalate that from tier1 to tier2. EscalationID would be an autonumber or some other primary key, CaseID=12(still), prevTier=Tier1, newTier=Tier2, DateModified=Date().

Assuming that the caseID doesn't change with escalation, you would then query Case table joining the Escalation table on CaseID for a list of all escalations that each case has experienced.

[vurna]

Hey again,

So I've been trying to figure this out the two past days, I haven't succeded though. I thought sometimes I was getting close to a solution but I haven't been able to reach all the way.

Imdabaun, I tried what you suggested to create a table with the following information:
EscalationID (uniqe index counter)
CaseID
PrevTier
NewTier
ModifiedDate

However I was not able to make anything of this afterwards it just seemed to be a replica of my original table but with an idex column.


I have however tried a few things and one solution is getting me closer but there's a few things I cant solve that I could use some help with.

I tried something called Dcount wich was suggested to me on another place.

Anyhow I started by re-arranging the first table manually to look like this: "_Table2"
ID, CaseID, PrevTier, NewTier, ModifiedDate
1, 156, Tier 1, Tier 2, 2010-02-16 11:37:00
2, 156, Tier 2, Tier 3, 2010-02-16 11:38:00
3, 162, Tier 1, Tier 2, 2010-02-16 13:46:00
4, 162, Tier 2, Tier 3, 2010-02-16 13:47:00
5, 165, Tier 1, Tier 2, 2010-02-20 13:00:00
6, 165, Tier 2, Tier 1, 2010-02-21 14:00:00



I then tried to put the Previous tier and the new tier in the same column this way: "Query4"

Select DCount("CaseID","_table2","CaseID <= " & [CaseID]) AS Counter, CaseID, PrevTier AS Tier, ModifiedDate from _table2
UNION Select DCount("CaseID","_table2","CaseID <= " & [CaseID])+1 AS Counter, CaseID, NewTier AS Tier, ModifiedDate from _table2
ORDER BY [_table2].CaseID, ModifiedDate;

I used the dcount thing in order to get the records in the correct order because without it the result was in the wrong order.

Result when executing the above code:
Counter, CaseID, Tier, ModifiedDate
2, 156, Tier 1, 2010-02-16 11:37:00
3, 156, Tier 2, 2010-02-16 11:37:00
2, 156, Tier 2, 2010-02-16 11:38:00
3, 156, Tier 3, 2010-02-16 11:38:00
4, 162, Tier 1, 2010-02-16 13:46:00
5, 162, Tier 2, 2010-02-16 13:46:00
4, 162, Tier 2, 2010-02-16 13:47:00
5, 162, Tier 3, 2010-02-16 13:47:00
6, 165, Tier 1, 2010-02-20 13:00:00
7, 165, Tier 2, 2010-02-20 13:00:00
6, 165, Tier 2, 2010-02-21 14:00:00
7, 165, Tier 1, 2010-02-21 14:00:00
As you can see the excalations now are in 1 column and in the correct order. However I'd like the Counter column to show internal count for ech excalation in each CaseID but this was the best I could come up with and still get the excalations in the correct order.

Next I tried to transform it to a crosstab that someone mentioned could work in a new query: "Query5"

TRANSFORM Max(Tier) AS ColID
SELECT CaseID
FROM Query4
GROUP BY CaseID
PIVOT "C" & (DCount("[Tier]","Query4","[CaseID]=" & [CaseID] & " AND [Tier] <='" & [Tier] & "'")+1);


The result when executing the above query this is the following (I manually typed "null" here, it was just an empty cell in ms access)
CaseID, C2, C3, C4, C5
156, Tier 1, null, Tier 2, Tier 3
162, Tier 1, null, Tier 2, Tier 3
165, null, Tier 1, null, Tier 2

I'm closer to what I'm trying to get in the output but still there's some things I quite dont understand why they come out the way they do.

The ultimate output result for me of the above example would be:

CaseID, C2, C3, C4,
156, Tier 1, Tier 2, Tier 3
162, Tier 1, Tier 2, Tier 3
165, Tier 1, Tier 2, Tier 1


Oh and I notice that CaseID has to be a Number in order for the last query to even work, unfortunatly my CaseID's cant be stored as numbers as the real CaseID's contain a "-" like "1-2567816" for example.

I'm not sure if I'm on the right path at here or if there's a much simpler solution. Unfortunatly I'm to much of a biginner to figure it out on my own.

[Imdabaum]

Having an index isn't the only thing that changed in your table. Now it is also a bit more normalized. You don't have to add new fields to the table anytime an escalation occurs beyond the scope of your table.

If an escalation event happens, you simply create a new log. I'm not sure what the DCount accomplishes, if you group by CaseID and order by EscalationID ascending, then you should get the same ordered result. As EscalationID =1 would theoretically be created before EscalationID =2, 3, 4 etc. With the modified date, you could also sort it by the modified date: oldest to newest.

You're on the right track with the crosstab query as that's going to give you the best result for veiwing a history of all escalations for a specific CaseID. As for CaseID needing to be a number, perhaps I should clarify.

If you're Case table has CaseID as a number, then the escalation table should have CaseID as a number. If you are creating a dynamic ID through VBA or a macro to get a custom ID like 1-2567816, what format are you storing it as? Your escalation table should correspond to that so you have matching datatypes, unless you want to mess with type masking which isn't neccessary.

If CaseID is a string, then perhaps you should try



TRANSFORM Max(Tier) AS ColID
SELECT CaseID
FROM Query4
GROUP BY CaseID
PIVOT "C" & (DCount("[Tier]","Query4","[CaseID]='" & [CaseID] & "' AND [Tier] <='" & [Tier] & "'")+1);