|
|
Posted by Jason Lepack on 06/20/07 12:53
I did this with two queries, I firest created combine_header:
SELECT
PIN,
[year] & " " & Left([category],5) AS Combined,
Days
FROM
Attendance
Then I created a crosstab query:
TRANSFORM First(Days) AS FirstOfDays
SELECT PIN
FROM combine_header
GROUP BY PIN
PIVOT Combined;
Cheers,
Jason Lepack
On Jun 20, 8:36 am, Radu <cuca_macaii2...@yahoo.com> wrote:
> 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]
|