Posted by Radu on 06/20/07 15:06

On Jun 20, 8:53 am, Jason Lepack <jlep...@gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -



Thank you VERY MUCH - both solutions work very well - Mark's is only
one step, and Jason's has the advantage of also working in Access...

Thanks a lot, and have a great day !

Alex

[Back to original message]