Converting database columns into associated row data

[LeslyP]

Hello everyone

I've got a simple problem. I want to switch my fields name into records. I already found a VBA code, I just do not know how to use it!
Ahhh, the newbies! ^^ "

It's probably not complicated when you understand VBA.
I think I need to create a module first... but then ?

http://stackoverflow.com/questions/8714353/converting-database-columns-into-associated-row-data

Sub UnionSQL(ArrayColCommonWord As String, TableName As String)
Dim db As Database

Set db = CurrentDb

For Each fld In db.TableDefs(TableName).Fields
    If Left(fld.Name, Len(ArrayColCommonWord)) <> ArrayColCommonWord Then
        sSQL1 = sSQL1 & ",[" & fld.Name & "]"
    End If
Next

sSQL1 = "SELECT " & Mid(sSQL1, 2)

For Each fld In db.TableDefs(TableName).Fields
    If Left(fld.Name, Len(ArrayColCommonWord)) = ArrayColCommonWord Then
        sSQL = sSQL & vbCrLf & "UNION ALL" & vbCrLf
        sSQL = sSQL & sSQL1 & ",'" & fld.Name & "' As " & ArrayColCommonWord _
            & ",[" & fld.Name & "] As " & ArrayColCommonWord & "Val"
        sSQL = sSQL & vbCrLf & "FROM [" & TableName & "]"
    End If
Next

Debug.Print Mid(sSQL, 14)
[COLOR="SeaGreen"]''This will fail if there is an existing query[/COLOR]
db.CreateQueryDef ArrayColCommonWord, Mid(sSQL, 14)

DoCmd.OpenQuery ArrayColCommonWord, acViewDesign

End Sub

 

[MajP]

Any chance you could provide some notional input and desired output? Maybe you can just do a simple normalizing query.

 

[LeslyP]

I want something like that.

 

[Uncle Gizmo]

I discuss this issue in depth on my website Here:-

http://www.niftyaccess.com/excel-in-access/

I provide a tool to help with the process which is available for $1.99 Here:-

https://gum.co/NiftyTransposeTool

Limited offer! You can download it for free with this Coupon Code:-t0cpajo

Don't forget to subscribe to my YouTube Channel!

https://www.youtube.com/user/UncleGizmo?sub_confirmation=1

 

[MajP]

Is it just what you show, mass and length or are there more columns? If it is just what you show then you could do a simple union query and then modify your table from that.

But I am not sure what happens after the ... If there is more, you will need to show that. Another easy way is to export to excel and there are some strategies for normalizing.

 

[MajP]

if it is simple you can do it in a Union query. Basically just keep chaining it and repeat the pattern. But if it is a lot of other fields than do it in excel.

[CODE]SELECT
  ID,
  "Mass" AS Parameter,
  Mass AS [Value],
  Masse_Unit AS Unit 
FROM
  Data 

Union

SELECT
  ID,
  "Length" AS Parameter,
  [Length] AS [Value],
  Length_Unit AS Unit 
FROM
  Data 
ORder by
  1,
  2

[/CODE]

 

[LeslyP]

Thank you everyone for your answers !

MajP:
I've 300 fields....for the moment. And maybe almost 900 later. It's all the time the same thing (weight, weight_unit, conductivity, conductitvity_unit...).
So I think doing it one by one is not an option. What about Excel. Do I have to do it everytime a new record is made or I can use macro to do it automatically ?

Uncle Gizmo:
I saw some of your videos ! Thank you for your tutorials and for your coupon code. I'll sure try this as soon as I'm back to work on Access. Continue your good work !

 

[MajP]

If there is consistency or some pattern or rule you could apply then maybe you could do it. But your example is pretty bad. You have a parameter called "Mass" but then a unit called "Masse_unit". How would any code know that these are related, since the naming is not consistent. They are spelled different. If they were all in order then maybe: mass, mass_unit, length, length_unit, height, height_unit. I have no idea. You show two fields and then their units. There is no logical pattern that you could code. If there was logic you could easily do this in code, but since there is no logic to your fields I think you would have to hand jam this in excel. If you can give me a hard set of rules you could easily code it.

In other words if every field was Parameter Name then the next field was Parameter_Unit you could code a solution. Or if the naming convention was exact like "Mass" and "Mass_Unit" you could do it. Or if there was a Pattern Parameter1, Parameter1_unit; Parameter2, Parameter2_unit. But your example has no pattern. Names do not match and you present 2 parameter and then 2 parameter units.

Although it is trivial for a human to know that Mass and Masse_unit are related, it is nearly impossible to write code that associates these two things for all cases.

 

[June7]

How do you have 300 fields? Access table is limited to 255. This appears to be a very non-normalized structure.

 

[Galaxiom]

How do you have 300 fields? Access table is limited to 255. This appears to be a very non-normalized structure.

I wonder about how they had 300 fields too. I expect they have multiple tables.

My guess is they inherited this database and are in the process of restructuring.

 

[June7]

UNION query has a limit of 50 SELECT lines.

MajP is right that names shown cannot be depended on for associating parameters and units, unless a table is built to establish those associations.

Using example tables in post 3, in table design view, I rearranged fields of the original data so the unit field sits next to corresponding value field. Following code rotates data without UNION query.

Sub FixData()
Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
Dim x As Integer
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM RawData;")
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM RotatedData WHERE 1=1")
While Not rs1.EOF
    For x = 1 To rs1.Fields.Count - 1 Step 2
        rs2.AddNew
        rs2!ID = rs1!ID
        rs2!Parameter = rs1.Fields(x).Name
        rs2!ParaValue = rs1.Fields(x)
        rs2!Unit = rs1.Fields(x + 1)
        rs2.Update
    Next
    rs1.MoveNext
Wend
End Sub

If any other data than parameters and units are in the table, code will have to be modified.

Place code in a general module, position cursor anywhere in the procedure, click Run from menu.

 

[LeslyP]

Hello everyone. As usual, what I thought would be simple end up being a headache. I hope we can find a solution to this problem.

MajP:
I tought about that and I end up with the same solution. Parameter 1, Parameter 1 Unit... I just didn't think about it before I did my exemple, because it is really simple to move the fields.

June7 and Galaxiom:
There is around 150 fields by table and yes, there is many tables. ^^"

June7:
Oh wow ! I didn't exepct the need to create a new code. Thank you very much, it is really kind of you. And as I didn't expect it, I omited to put everything in my exemple. In reality, it is more like:
Parameter1, Parameter1_unit, Parameter1_Method, Parameter1_Base.
Like you said, the code will need to be modify. However, I want to make it work with only two colums first. Then, I would like to understand how to modify it so I can get better un VBA !

Thank you for your support !

 

[June7]

Following shows modified code:

    For x = 1 To rs1.Fields.Count - 1 Step 4
        rs2.AddNew
        rs2!ID = rs1!ID
        rs2!Parameter = rs1.Fields(x).Name
        rs2!ParaValue = rs1.Fields(x)
        rs2!Unit = rs1.Fields(x + 1)
        rs2!Method = rs1.Fields(x + 2)
        rs2!Base = rs1.Fields(x + 3)
        rs2.Update
    Next

 

[LeslyP]

June7:
Oh my god !! You're awesome ! It works number one
I just need to add a "delete all records" before I can update new ones . Thank you sooo much !!

Uncle Gizmo:
Thank you for you help as well. It didn't work for me this time, but you already helped me a lot with you videos.