"Reshaping" datasets in Access

I think the only way to do it would be through code.

If you want that I'll try and knock something up and post back.

If it's not too much trouble, that would be great. Thanks!

Right, here you are.

I created your original table in Access and called it Wide.

I created another table called Flat with 4 fields - State, Year, VariableValue and VariableNo

Thanks so much!

No problem.

Does it work, it's been a while since I've coded for Access?

It worked on your sample.

By the way it's using DAO which has actually been superseded by ADO.

There might be another method for doing this in ADO.

It does work. One more question though - how could I modify the code to work on a query instead of a table?

Thanks, Norie!

Is the query the source?

You can use Recordset for queries and tables.

It works! Thanks so much!

Norie said:

Right, here you are.

I created your original table in Access and called it Wide.

I created another table called Flat with 4 fields - State, Year, VariableValue and VariableNo

Code:

Sub AddToFlat()
Dim db As DAO.Database
Dim fldWide As DAO.Field
Dim fldFlat As DAO.Field
Dim rstWide As DAO.Recordset
Dim rstFlat As DAO.Recordset
Dim I As Long

    Set db = CurrentDb

    Set rstWide = db.OpenRecordset("Wide")
    Set rstFlat = db.OpenRecordset("Flat")

    rstWide.MoveFirst
    
    While Not (rstWide.EOF)
        With rstFlat
            For I = 2 To rstWide.Fields.Count - 1
                .AddNew
                .Fields(0) = rstWide.Fields(0)
                .Fields(1) = rstWide.Fields(1)
                .Fields(2) = rstWide.Fields(I)
                .Fields(3) = rstWide.Fields(I).Name
                .Update
            Next I
        End With
        rstWide.MoveNext
    Wend
End Sub

Click to expand...

----------------------

I have used your code as is and it works great - but I want to pass a parameter to the query that supplies the "wide" data.

Here is my code:

Sub HPF_Long_File()
Dim db As DAO.Database
Dim fldWide As DAO.Field
Dim fldFlat As DAO.Field
Dim rstWide As DAO.Recordset
Dim rstFlat As DAO.Recordset
Dim I As Long
Dim Inpt As Date


Set db = CurrentDb
' gets input data

Inpt = InputBox("Enter a start date")


' run query with parameter - doesn't work

With CurrentDb.QueryDefs("qry_HPF_Transfer")
.Parameters("[Start Date]") = Inpt
.Execute
End With


' deletes all records from table HPF_Long_File

CurrentDb.Execute "DELETE from HPF_Long_File"

' opens record sets
Set rstWide = db.OpenRecordset("qry_HPF_transfer")
Set rstFlat = db.OpenRecordset("HPF_Long_File")

rstWide.MoveFirst

' moves records from query results to long file format
While Not (rstWide.EOF)
With rstFlat
For I = 4 To rstWide.Fields.Count - 1
.AddNew
.Fields(0) = rstWide.Fields(0)
.Fields(1) = rstWide.Fields(1)
.Fields(2) = rstWide.Fields(2)
.Fields(3) = rstWide.Fields(3)
.Fields(4) = rstWide.Fields(I)
.Fields(5) = rstWide.Fields(I).Name
.Update
Next I
End With
rstWide.MoveNext
Wend
End Sub


You will see that I added an input box and then try to pass that to the query. But the query doesn't work - it fails at the execute command.

And, with my limited experience, I doubt that the Set rstWide line won't work either.

Any suggestions??