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

select PIN,
max(case when Category='Authorized' and Year=2006 then Days
end) as [Auth 2006],
max(case when Category='Available' and Year=2006 then Days end)
as [Avail 2006],
max(case when Category='Personal' and Year=2006 then Days end)
as [Pers 2006],
max(case when Category='Authorized' and Year=2007 then Days
end) as [Auth 2007],
max(case when Category='Available' and Year=2007 then Days end)
as [Avail 2007],
max(case when Category='Personal' and Year=2007 then Days end)
as [Pers 2006]
from Attendance
group by PIN
order by PIN

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... (AT) yahoo (DOT) com> wrote:

On Jun 20, 8:53 am, Jason Lepack <jlep... (AT) gmail (DOT) com> wrote:


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

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... (AT) yahoo (DOT) com> wrote: