473,320 Members | 1,991 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Transposing columns into rows in access

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: 01522 888178
FAX: 0870 705 9115
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.
Sep 14 '06 #1
9 10633
On Thu, 14 Sep 2006 14:10:26 GMT, "Classic-Car-World Ltd"
<en*******@classic-car-world.co.ukwrote:

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
Sep 14 '06 #2
The cross tab query wizard should be able to do it.

"Classic-Car-World Ltd" <en*******@classic-car-world.co.ukwrote in message
news:mp*****************@newsfe3-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: 01522 888178
FAX: 0870 705 9115
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.


Sep 14 '06 #3
Thanks Ron, tried the cross tab query but it didn't work.

--
Kindest Regards

Tom

"paii, Ron" <pa**@packairinc.comwrote in message
news:1O******************************@athenet.net. ..
The cross tab query wizard should be able to do it.

"Classic-Car-World Ltd" <en*******@classic-car-world.co.ukwrote in
message
news:mp*****************@newsfe3-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: 01522 888178
FAX: 0870 705 9115
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.



Sep 15 '06 #4
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.com...
On Thu, 14 Sep 2006 14:10:26 GMT, "Classic-Car-World Ltd"
<en*******@classic-car-world.co.ukwrote:

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

Sep 15 '06 #5
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
Sep 15 '06 #6

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.Col) AS FirstOfCol
SELECT zQTest.ProdNo
FROM zQTest
GROUP BY zQTest.ProdNo
PIVOT zQTest.Order;
"Classic-Car-World Ltd" <en*******@classic-car-world.co.ukwrote in message
news:XM*****************@newsfe7-win.ntli.net...
Thanks Ron, tried the cross tab query but it didn't work.

--
Kindest Regards

Tom

"paii, Ron" <pa**@packairinc.comwrote in message
news:1O******************************@athenet.net. ..
The cross tab query wizard should be able to do it.

"Classic-Car-World Ltd" <en*******@classic-car-world.co.ukwrote in
message
news:mp*****************@newsfe3-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: 01522 888178
FAX: 0870 705 9115
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.



Sep 15 '06 #7
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@uwewrote in message news:6655e98775ea0@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

Sep 15 '06 #8
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.append
recordset2!ProductNumber = recordset1!ProductNumber
recordset2!Description = recordset1!Description & " " &
recordset1!value
recordset1.movenext
recordset2!size = recordset1!value
recordset1.movenext
recordset2!length = recordset1.Value
recordset2 update
recordset1.movenext
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*******@classic-car-world.co.ukwrote in message
news:mp*****************@newsfe3-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


Sep 15 '06 #9
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@uwewrote in message news:6655e98775ea0@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

Sep 15 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: shank | last post by:
Is the CASE statement allowed when querying an Access database? Assuming so, what is the correct syntax? thanks SELECT OrderNo, Description, Type, Label FROM Stock WHERE Manuf = 'M' ORDER BY...
3
by: John Spiegel | last post by:
Hey all, I can't seem to find a way to determine how many columns or rows are in a multi-dimensional array. The Length and Rank properties would seem to help, but I can't seem to find that...
3
by: jeffgeorge | last post by:
Trying to split a column in Access with all fields having the format: COLUMN: I see in Excel they have a Text to Columns function which creates two columns which seperate the NUMBER and NAME. ...
1
by: Richie | last post by:
I am working with an Access table that contains dollar amounts in two columns, one for January 2004 (CP_Payment_01 ) and the other for February 2004 (CP_Payment_02). My original goal was to take...
2
by: Dscar | last post by:
Hi, I am a beginner in ACCESS, I've imported data into access, and then realized that I need to split the information in one of my columns into 2 columns. the information looks like this:...
1
by: omnittha | last post by:
Hi! Here I need help from you all - I would like to add values in 2 columns and write them to a third column and get the avg value of the third column. For Example: ColumnA ColumnB ColumnC ...
1
by: brionesl | last post by:
Hi, I have seen other threads with the same question but I'm not too familiar with Access and their solutions often include writing VB code or very complex SQL statements. What I want to do is...
1
by: ragulan84 | last post by:
I have to compare columns in MS acces. Please see below the sample LAST_NEWSLETTER_OPENED_DATE LAST_LOGIN_DATE 07/21/09 06/26/09 07/21/09 12/29/09 ...
5
by: nedryerson | last post by:
Hello, I'm working on a form in Access 2007, and specifically on a combo-box field that displays two columns in the drop-down menu. The problem is that once I make a selection, the field only...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.