[deepak23]

Hi,
Have a requirement to convert data for an ERP application. Currently the data is in the form as below

Fieldname Field1 - Field2 - Field3 - Field 4
----------------------------------------
Record1 Dat1a - Dat2a - Dat3a - Dat4a
Record2 Dat1b - Dat2b - Dat3b - Dat4b

The data is now required in the form below
Fieldname Fielda Fieldb Fieldc
---------------------------------
Record 1 Dat1a - Field2 - Dat2a
Record 2 Dat1a - Field3 - Dat3a
Record 3 Dat1a - Field4 - Dat4a
Record 4 Dat1b - Field2 - Dat2b
Record 5 Dat1b - Field3 - Dat3b
And so on..

Any easy way to do this?
My access programming knowledge is beginner - intermediate at best.

Thanks

Deepak

[OBP]

Deepak, welcome to the Forum.
How many fields do you need to convert in this way?
What Field names will the new data using the old field names have?

[deepak23]

Thanks OBP for the welcome
I have a table of 20 fields of which 14 fields needs to be converted. Table has 4500 odd records. Post convertion the table would have 8 fields and 63000 records.
I have attached a sample excel file that shows how the pre & post data needs to look like...

Deepak

[deepak23]

Hello again,
I wrote a pretty ugly code to do this in excel. (In this case the total no. of fields are 9 and 7 of the fields need to be repositioned across 4 fields as explained in the first post.) The code is basically a few macros merged together in a loop and a condition to stop once all rows have been done. Though it did the job, am sure there is a better and faster way to do it. Looking forward to your help.

The code:

Sub Runprog()
Dim intx As Integer
Dim checka2 As String
intx = 1
Do Until intx = 0
Sheets("Sheet2").Select
Rows("2:8").Select
Selection.Insert Shift:=xlDown
Sheets("Sheet1").Select
Range("A2:B2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2:A8").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("C1:I2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _False, Transpose:=True
Sheets("Sheet1").Select
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
checka2 = Range("A2").Value
If checka2 = "" Then
intx = 0
End If
Loop
End Sub

Thanks

Deepak

[OBP]

deepak, I understand what you want to do, but not why you want to do it.
However it goes completely against Relational Database Design Rules as it will produce one very large Table with lots of Duplicated Text Data.
The data should be split it in to multiple tables to prevent this duplication, it is called Normalising the data.
So I would recommend the following Tables

Tag Table
KeyField - TagID - an Autonumber Field
Field - Tag - a Text Field to hold the Tag data.

Characteristic Table
KeyField - CharacteristicID - an Autonumber Field
Field - Characteristic - a Text Field to hold the Characteristic from the current Field Names.

Parameter Table
KeyField - ParameterID - an Autonumber Field
Field - Parameter - a Text Field to hold the Parameter from the Records of current Field


Finally a table to bring all the data together

Usage Table
KeyField - usageID - an Autonumber Field
Field - TagID - a Number type Long Field to hold the TagID.
Field - CharacteristicID - a Number type Long Field to hold the Characteristic ID.
Field - ParameterID - a Number type Long Field to hold the Parameter ID.

[OBP]

Sorry, I didn't see your post I was busy typing.
If what you have created works, don't bother trying to improve it as you only need it for a one off Conversion.

[deepak23]

Thank you OBP.

Fact is that's exactly how my data is organized presently! But as I said earlier, my co is migrating to an ERP (SAP), and for some obscure reason the data is stored in the desired form there.

Requirement is oneoff, so life goes on with this bit of code. But am sure there are better ways to do this...

Thanks again for your help...

Deepak

[OBP]

Whoever is in control of the ERP does not appear to understand Relational database Design, so good luck to your co.