Re-arranging records thru a query

[vurna]

Hi!

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

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

My table is setup in the following way:
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

As you can see each Case ID can be escalated to a different work tier one or several times. 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:
CaseID OriginalTier Escalation1 Escalation2
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

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.

I've also started to think that it might be possible to somehow rank each record within the same CaseID to be able to use the rank to then re-arrange the data but with my limited knowledge I've failed so far.

Any advice on how to proceed would be greatly appriciated!

 

[Galaxiom]

Your data is not quite normalized. The Original tier should be held as an opening record. OldValue field becomes obsolete in this structure.

Fix this then check out stopher's solution in this thread. I think it will work for you.

http://www.access-programmers.co.uk/forums/showthread.php?t=198163

 

[vurna]

Hi Galaxiom,

I'm not very expecienced with databases but I've seen normalization mentioned before. I'm using ms access right now as excel wasnt able to help me achive what I needed. I hope I can get away with not following all database best practice rules on this one. : )

Anyhow I'm looking into re-arranging my original table as you suggested I start with, to try to normalize.
First off I should start the original table with OriginalTier(aka OldValue) or do you mean in the second table I create based on data from the first table?

My First table today consist of this design:
CaseID
Timestamp
OldValue
NewValue

I haven't put any unique index on it but I guess it would be CaseID + TimeStamp that would define a uniqe record.

I also read the thread you suggested to me, I dont quite understand the function (tramsform & pivot) but maybe if I get my first table setup correctly it easier to know which field to put where in the function or query?

 

[vurna]

Update:
I tried the solution you proposed right off the bat, I didnt know exactly if I needed to change anything with my original table or not.

Anyhow starting with my original table: "Table1"
CaseID, TimeStamp, OldTier, NewTier
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

I then go and create a query: "Query1"
-----------------------------------------------------------------------
Transform Max(NewTier) AS ColID
Select CaseID
from Table1
group by CaseID
pivot "C" & (DCount("[NewTier]","Table1","[CaseID]=" & [CaseID] & " AND [NewTier] <='" & [NewTier] & "'")+1)
-----------------------------------------------------------------------

Executing this query gives me the following result:
CaseID, C1
156, Tier 3
162, Tier 3
178, Tier 3
181, Tier 3
185, Tier 3
191, Tier 3

As you can see it doesn't come out exactly as I need it. I only get 1 escalation and its he highest escalation. My first table can have excalations, de-escalations and there can be one or many escalations per Case.

I'm probably doing something wrong, I dont really understand all the code so mostly just guessing where to put the different fields in the sql code.

Anyhow will gladly take advice.

 

[vurna]

I've been using my day to try to understand the Dcount thing. I think I'm starting to get a tiny tiny grasp of it but still got a long way to go.

I think the problem in the last post is that Transform only works for the data in one column and not for two columns at once. So I tried to re-arrange my data a little before doing the Dcount thing again.


Step1:
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

Step2:
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

Step3:
Now I tried to create the columization by using the the solution you proposed 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.