Re: Transposing columns into rows in access
- From: "GMForsythe" <u26679@uwe>
- Date: Fri, 15 Sep 2006 16:31:29 GMT
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
.
- References:
- Transposing columns into rows in access
- From: Classic-Car-World Ltd
- Transposing columns into rows in access
- Prev by Date: Copy value from subform1 to subform two when all are open
- Next by Date: Updating unrelated tables
- Previous by thread: Re: Transposing columns into rows in access
- Next by thread: Filling unbound form with for-next loop
- Index(es):
Relevant Pages
|
