Transposing columns into rows in access

[Classic-Car-World Ltd]

Hi, I have some data in a table in columns which I need to convert into
rows. This is specification data for some tools etc. The data is currently
in the following format:

Product No, order, description, value

1000, 1, model No, Ak272
1000, 2, Size, 10mm
1000, 3, Length, 20mm
1001, 1, Model No, Ak273
1001, 2, Size, 25mm
1001, 3, length, 50mm
etc
The format I require the data in is as follows:

Product No, Col1, Col2, Col3
1000, Model No AK272, Size 10mm, length 20mm
1001, model No AK273, Size 25mm, length 50mm
etc

I have approximately 5,000 records to sort in this way.

Thanks in advance for any help
--
Kindest Regards

Tom
Tom McCay (Director)
Classic-Car-World Ltd
Tel: [phone]
FAX: [phone]
E-Mail: to*@classic-car-world.co.uk
URL: www.classic-car-world.co.uk

Now offering quality Sealey & Draper tools at discount prices, see
www.ccw-tools.com for further details.

[Tom van Stiphout]

On Thu, 14 Sep 2006 14:10:26 GMT, "Classic-Car-World Ltd"
<en*******@clas sic-car-world.co.ukwrot e:

Use the often-quoted fConcatChild from here:
http://www.mvps.org/access/modules/mdl0004.htm

-Tom.

>Hi, I have some data in a table in columns which I need to convert into
rows. This is specification data for some tools etc. The data is currently
in the following format:

Product No, order, description, value

1000, 1, model No, Ak272
1000, 2, Size, 10mm
1000, 3, Length, 20mm
1001, 1, Model No, Ak273
1001, 2, Size, 25mm
1001, 3, length, 50mm
etc
The format I require the data in is as follows:

Product No, Col1, Col2, Col3
1000, Model No AK272, Size 10mm, length 20mm
1001, model No AK273, Size 25mm, length 50mm
etc

I have approximately 5,000 records to sort in this way.

Thanks in advance for any help

[paii, Ron]

The cross tab query wizard should be able to do it.

"Classic-Car-World Ltd" <en*******@clas sic-car-world.co.ukwrot e in message
news:mp******** *********@newsf e3-gui.ntli.net...

Hi, I have some data in a table in columns which I need to convert into
rows. This is specification data for some tools etc. The data is currently
in the following format:

Product No, order, description, value

1000, 1, model No, Ak272
1000, 2, Size, 10mm
1000, 3, Length, 20mm
1001, 1, Model No, Ak273
1001, 2, Size, 25mm
1001, 3, length, 50mm
etc
The format I require the data in is as follows:

Product No, Col1, Col2, Col3
1000, Model No AK272, Size 10mm, length 20mm
1001, model No AK273, Size 25mm, length 50mm
etc

I have approximately 5,000 records to sort in this way.

Thanks in advance for any help
--
Kindest Regards

Tom
Tom McCay (Director)
Classic-Car-World Ltd
Tel: [phone]
FAX: [phone]
E-Mail: to*@classic-car-world.co.uk
URL: www.classic-car-world.co.uk

Now offering quality Sealey & Draper tools at discount prices, see
www.ccw-tools.com for further details.

[Classic-Car-World Ltd]

Thanks Ron, tried the cross tab query but it didn't work.

--
Kindest Regards

Tom

"paii, Ron" <pa**@packairin c.comwrote in message
news:1O******** *************** *******@athenet .net...

The cross tab query wizard should be able to do it.

"Classic-Car-World Ltd" <en*******@clas sic-car-world.co.ukwrot e in
message
news:mp******** *********@newsf e3-gui.ntli.net...

>Hi, I have some data in a table in columns which I need to convert into
rows. This is specification data for some tools etc. The data is
currently
in the following format:

Product No, order, description, value

1000, 1, model No, Ak272
1000, 2, Size, 10mm
1000, 3, Length, 20mm
1001, 1, Model No, Ak273
1001, 2, Size, 25mm
1001, 3, length, 50mm
etc
The format I require the data in is as follows:

Product No, Col1, Col2, Col3
1000, Model No AK272, Size 10mm, length 20mm
1001, model No AK273, Size 25mm, length 50mm
etc

I have approximately 5,000 records to sort in this way.

Thanks in advance for any help
--
Kindest Regards

Tom
Tom McCay (Director)
Classic-Car-World Ltd
Tel: [phone]
FAX: [phone]
E-Mail: to*@classic-car-world.co.uk
URL: www.classic-car-world.co.uk

Now offering quality Sealey & Draper tools at discount prices, see
www.ccw-tools.com for further details.

[Classic-Car-World Ltd]

Hi Tom, I wish I understood what this was. I'm sure it would work but
unfortunately I don't understand the finer art of coding. Is it a simple
matter of running the code as a query against the one table and what
modifications would I need to make to link it to my table. Is it just a
matter of changing the field names?

--
Kindest Regards

Tom

"Tom van Stiphout" <no************ *@cox.netwrote in message
news:85******** *************** *********@4ax.c om...

On Thu, 14 Sep 2006 14:10:26 GMT, "Classic-Car-World Ltd"
<en*******@clas sic-car-world.co.ukwrot e:

Use the often-quoted fConcatChild from here:
http://www.mvps.org/access/modules/mdl0004.htm

-Tom.

>>Hi, I have some data in a table in columns which I need to convert into
rows. This is specification data for some tools etc. The data is currently
in the following format:

Product No, order, description, value

1000, 1, model No, Ak272
1000, 2, Size, 10mm
1000, 3, Length, 20mm
1001, 1, Model No, Ak273
1001, 2, Size, 25mm
1001, 3, length, 50mm
etc
The format I require the data in is as follows:

Product No, Col1, Col2, Col3
1000, Model No AK272, Size 10mm, length 20mm
1001, model No AK273, Size 25mm, length 50mm
etc

I have approximately 5,000 records to sort in this way.

Thanks in advance for any help

[GMForsythe]

This may be a clumsy way to do it, but why not try this....

Set up your new table, making the Product No the Primary Key. Name the other
Columns: Order, Description, Model_No, Size, and Length. Import the existing
table. Of course, it will only import the first of each Product No. along
with its Order, Description, and Model_No. The last two will be null. When
you get the warning message that not all of your data could be imported,
ignore it.

Go back into table design mode and delete the fields Order and Description.

Next, set up an update query with a relationship between the old table and
the new one based on the Product Key. The criteria for selecting records from
the old table would be [order] = 2. Then just have it update the Size field
of the new table with the Value field from the old table. Execute the query.
Go back to Design mode and replace the search value in the order field of the
old table to be [order] = 3. Remove the Size field from the query and add the
Length field. Have it updated with the value of the Value field.

Not elegant, but it should work, I think.

Classic-Car-World Ltd wrote:

>Hi, I have some data in a table in columns which I need to convert into
rows. This is specification data for some tools etc. The data is currently
in the following format:

Product No, order, description, value

1000, 1, model No, Ak272
1000, 2, Size, 10mm
1000, 3, Length, 20mm
1001, 1, Model No, Ak273
1001, 2, Size, 25mm
1001, 3, length, 50mm
etc
The format I require the data in is as follows:

Product No, Col1, Col2, Col3
1000, Model No AK272, Size 10mm, length 20mm
1001, model No AK273, Size 25mm, length 50mm
etc

I have approximately 5,000 records to sort in this way.

Thanks in advance for any help

[paii, Ron]

Create 2 queries, the 1st named zQtest conditions and sorts the data in a
table named zTest.

SELECT zTest.ProdNo, zTest.Order, [Desc] & ", " & [Value] AS Col
FROM zTest
ORDER BY zTest.ProdNo, zTest.Order;

Use this for the cross tab

TRANSFORM First(zQTest.Co l) AS FirstOfCol
SELECT zQTest.ProdNo
FROM zQTest
GROUP BY zQTest.ProdNo
PIVOT zQTest.Order;
"Classic-Car-World Ltd" <en*******@clas sic-car-world.co.ukwrot e in message
news:XM******** *********@newsf e7-win.ntli.net...

Thanks Ron, tried the cross tab query but it didn't work.

--
Kindest Regards

Tom

"paii, Ron" <pa**@packairin c.comwrote in message
news:1O******** *************** *******@athenet .net...

The cross tab query wizard should be able to do it.

"Classic-Car-World Ltd" <en*******@clas sic-car-world.co.ukwrot e in
message
news:mp******** *********@newsf e3-gui.ntli.net...

Hi, I have some data in a table in columns which I need to convert into
rows. This is specification data for some tools etc. The data is
currently
in the following format:

Product No, order, description, value

1000, 1, model No, Ak272
1000, 2, Size, 10mm
1000, 3, Length, 20mm
1001, 1, Model No, Ak273
1001, 2, Size, 25mm
1001, 3, length, 50mm
etc
The format I require the data in is as follows:

Product No, Col1, Col2, Col3
1000, Model No AK272, Size 10mm, length 20mm
1001, model No AK273, Size 25mm, length 50mm
etc

I have approximately 5,000 records to sort in this way.

Thanks in advance for any help
--
Kindest Regards

Tom
Tom McCay (Director)
Classic-Car-World Ltd
Tel: [phone]
FAX: [phone]
E-Mail: to*@classic-car-world.co.uk
URL: www.classic-car-world.co.uk

Now offering quality Sealey & Draper tools at discount prices, see
www.ccw-tools.com for further details.

[Classic-Car-World Ltd]

Hi I think I understand what you are trying to do here but can you tell me
the expression I have to enter in the "Update To" field that will allow me
to specify the appropriate field in the old table.

I've completed the first part but can't see how to set up the final update
in the update query.

Sorry about this, but I don't really have any access skills.

--
Kindest Regards
Tom

"GMForsythe " <u26679@uwewrot e in message news:6655e98775 ea0@uwe...

This may be a clumsy way to do it, but why not try this....

Set up your new table, making the Product No the Primary Key. Name the
other
Columns: Order, Description, Model_No, Size, and Length. Import the
existing
table. Of course, it will only import the first of each Product No. along
with its Order, Description, and Model_No. The last two will be null. When
you get the warning message that not all of your data could be imported,
ignore it.

Go back into table design mode and delete the fields Order and
Description.

Next, set up an update query with a relationship between the old table and
the new one based on the Product Key. The criteria for selecting records
from
the old table would be [order] = 2. Then just have it update the Size
field
of the new table with the Value field from the old table. Execute the
query.
Go back to Design mode and replace the search value in the order field of
the
old table to be [order] = 3. Remove the Size field from the query and add
the
Length field. Have it updated with the value of the Value field.

Not elegant, but it should work, I think.

Classic-Car-World Ltd wrote:

>>Hi, I have some data in a table in columns which I need to convert into
rows. This is specification data for some tools etc. The data is currently
in the following format:

Product No, order, description, value

1000, 1, model No, Ak272
1000, 2, Size, 10mm
1000, 3, Length, 20mm
1001, 1, Model No, Ak273
1001, 2, Size, 25mm
1001, 3, length, 50mm
etc
The format I require the data in is as follows:

Product No, Col1, Col2, Col3
1000, Model No AK272, Size 10mm, length 20mm
1001, model No AK273, Size 25mm, length 50mm
etc

I have approximately 5,000 records to sort in this way.

Thanks in advance for any help

[Kc-Mass]

What if you wrote a simple routine to unstack your data?
Something like:

Create a second table with fields of ProductNumber, Description, size and
length

Open the old table as recordset1
Open the new file as recordset2

do while not recordset1.EOF
recordset2.appe nd
recordset2!Prod uctNumber = recordset1!Prod uctNumber
recordset2!Desc ription = recordset1!Desc ription & " " &
recordset1!valu e
recordset1.move next
recordset2!size = recordset1!valu e
recordset1.move next
recordset2!leng th = recordset1.Valu e
recordset2 update
recordset1.move next
Loop

This assumes that the data for each item is limited to exactly three records
and that the data is
sorted by partno and the order just as you depict it.

"Classic-Car-World Ltd" <en*******@clas sic-car-world.co.ukwrot e in message
news:mp******** *********@newsf e3-gui.ntli.net...

Hi, I have some data in a table in columns which I need to convert into
rows. This is specification data for some tools etc. The data is currently
in the following format:

Product No, order, description, value

1000, 1, model No, Ak272
1000, 2, Size, 10mm
1000, 3, Length, 20mm
1001, 1, Model No, Ak273
1001, 2, Size, 25mm
1001, 3, length, 50mm
etc
The format I require the data in is as follows:

Product No, Col1, Col2, Col3
1000, Model No AK272, Size 10mm, length 20mm
1001, model No AK273, Size 25mm, length 50mm
etc

[Classic-Car-World Ltd]

Hi Just to say thank you to those who responded to my plea for help. I
managed to achieve the desired results by running the update query as
described below.

Thanks again

--
Kindest Regards
Tom

"GMForsythe " <u26679@uwewrot e in message news:6655e98775 ea0@uwe...

This may be a clumsy way to do it, but why not try this....

Set up your new table, making the Product No the Primary Key. Name the
other
Columns: Order, Description, Model_No, Size, and Length. Import the
existing
table. Of course, it will only import the first of each Product No. along
with its Order, Description, and Model_No. The last two will be null. When
you get the warning message that not all of your data could be imported,
ignore it.

Go back into table design mode and delete the fields Order and
Description.

Next, set up an update query with a relationship between the old table and
the new one based on the Product Key. The criteria for selecting records
from
the old table would be [order] = 2. Then just have it update the Size
field
of the new table with the Value field from the old table. Execute the
query.
Go back to Design mode and replace the search value in the order field of
the
old table to be [order] = 3. Remove the Size field from the query and add
the
Length field. Have it updated with the value of the Value field.

Not elegant, but it should work, I think.

Classic-Car-World Ltd wrote:

>>Hi, I have some data in a table in columns which I need to convert into
rows. This is specification data for some tools etc. The data is currently
in the following format:

Product No, order, description, value

1000, 1, model No, Ak272
1000, 2, Size, 10mm
1000, 3, Length, 20mm
1001, 1, Model No, Ak273
1001, 2, Size, 25mm
1001, 3, length, 50mm
etc
The format I require the data in is as follows:

Product No, Col1, Col2, Col3
1000, Model No AK272, Size 10mm, length 20mm
1001, model No AK273, Size 25mm, length 50mm
etc

I have approximately 5,000 records to sort in this way.

Thanks in advance for any help