Posted by Jason Lepack on 06/20/07 15:12

I would like to point out that each time you add a Category or a year
you need to modify Mark's query (while it works perfectly for the
given data set). Mine will work with whatever data you load into that
table.

On Jun 20, 11:06 am, Radu <cuca_macaii2...@yahoo.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

[Back to original message]