SQL Query Question - how to - minimum # of steps (Access)

[Radu]

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

[helenwheelss]

On Jun 20, 10:45 am, Radu <[e-mail]:

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

In Access SQL, this ought to do it, but it will fail if you have so
many categories and/or years in the data that you run up against the
limit of 255 columns:

TRANSFORM Sum(attendance.Days) AS SumOfDays
SELECT attendance.PIN
FROM attendance
GROUP BY attendance.PIN
PIVOT [Category] & " " & [year];

[Radu]

On Jun 21, 3:36 am, [e-mail] wrote:

On Jun 20, 10:45 am, Radu <[e-mail]:

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

In Access SQL, this ought to do it, but it will fail if you have so
many categories and/or years in the data that you run up against the
limit of 255 columns:

TRANSFORM Sum(attendance.Days) AS SumOfDays
SELECT attendance.PIN
FROM attendance
GROUP BY attendance.PIN
PIVOT [Category] & " " & [year];- Hide quoted text -

- Show quoted text -

Thank you for your time..... I had a really difficult problem to solve
by queries, and this transposition thing was only part of it. It's
solved now, and your help was honestly appreciated.

Again, thanks a lot :-))
Alex