SQL Query to transpose data from rows into columns

[Haas C]

Hi all,

I have a table with two columns, labeled Year and Loss. In the Year
field, I have the numbers 1 to 10,000, each which can or cannot
repeat. In the Loss column, i have numbers corresponding to the
Years...for example:

Year, Loss
1, 568
1, 621
1, 358
1, 7888
2, 2689
2, 6563
2, 15
3, 983
3, 146
3, 258
3, 852
4, 96
5, 87
5, 32

So, you see, Year 1 can have four losses, Year 2 can have three
losses, etc.

Now, here's where I need help as I'm not sure what to do given that I
am a beginner at SQl and learning: I want to have just one row for
each Year. So, for Year 1, I would like to have four extra columns,
and for Year 2, I would like to have three columns created to hold
loss numbers so they display in a row - please see below for example:

Year, Loss1, Loss2, Loss3, Loss4, Loss5, etc.
1, 568, 621, 358, 7888
2, 2689, 6563, 15
3, 983, 146, 258, 852
4, 96
5, 87, 32

I hope that the examples helped in clarifying what kind of Query I
need. I believe I can have up to 30 losses for each year. Please help
if you can - I truly appreciate it and thank you in advance!

[Steve]

Hello Haas,

A crosstab query will do what you want. Go to queries and click New. One of
your choices will be a crosstab query. Choose it and follow the wizard.

Steve
"Haas C" <ha*****@yahoo.comwrote in message
news:a3**********************************@26g2000h sk.googlegroups.com...

Hi all,

I have a table with two columns, labeled Year and Loss. In the Year
field, I have the numbers 1 to 10,000, each which can or cannot
repeat. In the Loss column, i have numbers corresponding to the
Years...for example:

Year, Loss
1, 568
1, 621
1, 358
1, 7888
2, 2689
2, 6563
2, 15
3, 983
3, 146
3, 258
3, 852
4, 96
5, 87
5, 32

So, you see, Year 1 can have four losses, Year 2 can have three
losses, etc.

Now, here's where I need help as I'm not sure what to do given that I
am a beginner at SQl and learning: I want to have just one row for
each Year. So, for Year 1, I would like to have four extra columns,
and for Year 2, I would like to have three columns created to hold
loss numbers so they display in a row - please see below for example:

Year, Loss1, Loss2, Loss3, Loss4, Loss5, etc.
1, 568, 621, 358, 7888
2, 2689, 6563, 15
3, 983, 146, 258, 852
4, 96
5, 87, 32

I hope that the examples helped in clarifying what kind of Query I
need. I believe I can have up to 30 losses for each year. Please help
if you can - I truly appreciate it and thank you in advance!

[Haas C]

On Aug 28, 2:57*pm, "Steve" <[e-mail]:

Hello Haas,

A crosstab query will do what you want. Go to queries and click New. One of
your choices will be a crosstab query. Choose it and follow the wizard.

Steve

"Haas C" <[e-mail] in message

news:a3**********************************@26g2000h sk.googlegroups.com...

Hi all,

I have a table with two columns, labeled Year and Loss. In the Year
field, I have the numbers 1 to 10,000, each which can or cannot
repeat. In the Loss column, i have numbers corresponding to the
Years...for example:

Year, Loss
1, 568
1, 621
1, 358
1, 7888
2, 2689
2, 6563
2, 15
3, 983
3, 146
3, 258
3, 852
4, 96
5, 87
5, 32

So, you see, Year 1 can have four losses, Year 2 can have three
losses, etc.

Now, here's where I need help as I'm not sure what to do given that I
am a beginner at SQl and learning: I want to have just one row for
each Year. So, for Year 1, I would like to have four extra columns,
and for Year 2, I would like to have three columns created to hold
loss numbers so they display in a row - please see below for example:

Year, Loss1, Loss2, Loss3, Loss4, Loss5, etc.
1, 568, 621, 358, 7888
2, 2689, 6563, 15
3, 983, 146, 258, 852
4, 96
5, 87, 32

I hope that the examples helped in clarifying what kind of Query I
need. I believe I can have up to 30 losses for each year. Please help
if you can - I truly appreciate it and thank you in advance!- Hide quoted text -

- Show quoted text -

That actually doesn't work as I get a "too many headers" error. Can
someone please actually show me how to do this rather than tell me to
use a wizard? I appreciate the help Steve but am against a time crunch
and can't figure this out.

Thanks!

[Steve]

Haas,

I created a table named TblLossYer with fields MyYear, Loss and ColTitle. I
put your data in the MyYear and Loss fields. I manually put Loss 1, Loss
2,etc in ColTitle field. Here is the crosstab SQL:

TRANSFORM First(TblYearLoss.Loss) AS FirstOfLoss
SELECT TblYearLoss.MyYear
FROM TblYearLoss
GROUP BY TblYearLoss.MyYear
PIVOT TblYearLoss.ColTitle;

You will need to figure out a function to automatically create the value in
ColTitle and then use a calculated field in a query to automatically set the
value for ColTitle. When you do this, base your crosstab query on that query
and delete the ColTitle field in your table.

Steve
rl****@penn.com


"Haas C" <ha*****@yahoo.comwrote in message
news:9f**********************************@34g2000h sh.googlegroups.com...
On Aug 28, 2:57 pm, "Steve" <[e-mail]:

Hello Haas,

A crosstab query will do what you want. Go to queries and click New. One
of
your choices will be a crosstab query. Choose it and follow the wizard.

Steve

"Haas C" <[e-mail] in message

news:a3**********************************@26g2000h sk.googlegroups.com...

Hi all,

I have a table with two columns, labeled Year and Loss. In the Year
field, I have the numbers 1 to 10,000, each which can or cannot
repeat. In the Loss column, i have numbers corresponding to the
Years...for example:

Year, Loss
1, 568
1, 621
1, 358
1, 7888
2, 2689
2, 6563
2, 15
3, 983
3, 146
3, 258
3, 852
4, 96
5, 87
5, 32

So, you see, Year 1 can have four losses, Year 2 can have three
losses, etc.

Now, here's where I need help as I'm not sure what to do given that I
am a beginner at SQl and learning: I want to have just one row for
each Year. So, for Year 1, I would like to have four extra columns,
and for Year 2, I would like to have three columns created to hold
loss numbers so they display in a row - please see below for example:

Year, Loss1, Loss2, Loss3, Loss4, Loss5, etc.
1, 568, 621, 358, 7888
2, 2689, 6563, 15
3, 983, 146, 258, 852
4, 96
5, 87, 32

I hope that the examples helped in clarifying what kind of Query I
need. I believe I can have up to 30 losses for each year. Please help
if you can - I truly appreciate it and thank you in advance!- Hide
quoted text -

- Show quoted text -

That actually doesn't work as I get a "too many headers" error. Can
someone please actually show me how to do this rather than tell me to
use a wizard? I appreciate the help Steve but am against a time crunch
and can't figure this out.

Thanks!

[Arch]

On Thu, 28 Aug 2008 13:44:05 -0700 (PDT), Haas C <ha*****@yahoo.com>
wrote:

>On Aug 28, 2:57*pm, "Steve" <[e-mail]:

>Hello Haas,

A crosstab query will do what you want. Go to queries and click New. One of
your choices will be a crosstab query. Choose it and follow the wizard.

Steve

"Haas C" <[e-mail] in message

news:a3**********************************@26g2000 hsk.googlegroups.com...

Hi all,

I have a table with two columns, labeled Year and Loss. In the Year
field, I have the numbers 1 to 10,000, each which can or cannot
repeat. In the Loss column, i have numbers corresponding to the
Years...for example:

Year, Loss
1, 568
1, 621
1, 358
1, 7888
2, 2689
2, 6563
2, 15
3, 983
3, 146
3, 258
3, 852
4, 96
5, 87
5, 32

So, you see, Year 1 can have four losses, Year 2 can have three
losses, etc.

Now, here's where I need help as I'm not sure what to do given that I
am a beginner at SQl and learning: I want to have just one row for
each Year. So, for Year 1, I would like to have four extra columns,
and for Year 2, I would like to have three columns created to hold
loss numbers so they display in a row - please see below for example:

Year, Loss1, Loss2, Loss3, Loss4, Loss5, etc.
1, 568, 621, 358, 7888
2, 2689, 6563, 15
3, 983, 146, 258, 852
4, 96
5, 87, 32

I hope that the examples helped in clarifying what kind of Query I
need. I believe I can have up to 30 losses for each year. Please help
if you can - I truly appreciate it and thank you in advance!- Hide quoted text -

- Show quoted text -

That actually doesn't work as I get a "too many headers" error. Can
someone please actually show me how to do this rather than tell me to
use a wizard? I appreciate the help Steve but am against a time crunch
and can't figure this out.

Thanks!

Like so many tasks in this area, there are lots of approaches. I
suspect someone will provide a solution that is substantially
"slicker" than this one, but in case not - this one works exactly as
you've requested.

It uses a small function and then calls that function from a query.
We're assuming the table is named 'years' and the attributes are
'yearnum' and 'loss'.

' -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
Public Function Cram(Y As Long)
Dim SQL As String
Dim L

SQL = "Select Loss from years where Yearnum=" & Y
' watch out for line wrap here-
L = CurrentProject.Connection.Execute(SQL).GetString(a dClipString, ,
, ",", "")
Cram = Left(L, Len(L) - 1)

End Function
' -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
Then use the function in a query:

' -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
SELECT [yearnum] & ", " & Cram([yearnum]) AS Haas
FROM years
GROUP BY [yearnum] & ", " & Cram([yearnum]);
' -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

Sidenote - Year is a reserved word in Access, it shouldn't be used for
an attribute name.

HTH,
Arch