|
|
Posted by Radu on 06/20/07 12:36
Hi. I have an 'Attendance' table like this:
PIN Year Category Days
1 2006 Authorized 1
1 2006 Available 2
1 2006 Personal 3
2 2006 Authorized 4
2 2006 Available 5
2 2006 Personal 6
3 2006 Authorized 7
3 2006 Available 8
3 2006 Personal 9
4 2006 Authorized 10
4 2006 Available 11
4 2006 Personal 12
1 2007 Authorized 13
1 2007 Available 14
1 2007 Personal 15
2 2007 Authorized 16
2 2007 Available 17
2 2007 Personal 18
3 2007 Authorized 19
3 2007 Available 20
3 2007 Personal 21
4 2007 Authorized 22
4 2007 Available 23
4 2007 Personal 24
I need to sum the days by PIN, Year and Category (that's easy...) AND
obtain a layout like this:
PIN Auth 2006 Avail 2006 Pers 2006 Auth
2007 Avail 2007 Pers 2007
1 1 2
3 13 14 15
2 4 5
6 16 17 18
3 7 8
9 19 20 21
4 10 11
12 22 23 24
How can I do this by queries without writing too many intermediate
steps ?
What I have done is this (5 queries, 2, 3, and 4 building on top of
1,
and 5 building on 2, 3, 4).
1 = Table1_Crosstab:
TRANSFORM Sum(Table1.Days) AS SumOfDays
SELECT Table1.PIN, Table1.Year
FROM Table1
GROUP BY Table1.PIN, Table1.Year
PIVOT Table1.Category;
Then, based on that,
2 = Authorized:
TRANSFORM First([1 = Table1_Crosstab].Authorized) AS
FirstOfAuthorized
SELECT [1 = Table1_Crosstab].PIN
FROM [1 = Table1_Crosstab]
GROUP BY [1 = Table1_Crosstab].PIN
PIVOT [1 = Table1_Crosstab].Year;
3 = Available:
TRANSFORM First([1 = Table1_Crosstab].Available) AS FirstOfAvailable
SELECT [1 = Table1_Crosstab].PIN
FROM [1 = Table1_Crosstab]
GROUP BY [1 = Table1_Crosstab].PIN
PIVOT [1 = Table1_Crosstab].Year;
and
4 = Personal:
TRANSFORM First([1 = Table1_Crosstab].Personal) AS FirstOfPersonal
SELECT [1 = Table1_Crosstab].PIN
FROM [1 = Table1_Crosstab]
GROUP BY [1 = Table1_Crosstab].PIN
PIVOT [1 = Table1_Crosstab].Year;
and finally
5 = All
SELECT [2 = Authorized].PIN, [2 = Authorized].[2006] AS [Auth 2006],
[3 = Available].[2006] AS [Avail 2006], [4 = Personal].[2006] AS
[Pers
2006], [2 = Authorized].[2007] AS [Auth 2007], [3 = Available].[2007]
AS [Avail 2007], [4 = Personal].[2007] AS [Pers 2007]
FROM ([2 = Authorized] INNER JOIN [3 = Available] ON [2 =
Authorized].PIN = [3 = Available].PIN) INNER JOIN [4 = Personal] ON
[3
= Available].PIN = [4 = Personal].PIN;
It works, but... I am sure that this is an awkward way of doing it.
Is
there any other, more elegant, way, please ? Besides, what if I had
not 3, but 15 categories, for example ????
Thanks a lot for your time reading this, Alex
[Back to original message]
|