"Reshaping" datasets in Access

[mousenib]

Hi,

I have a dataset that's in a "wide" format:

State, Year, Variable1, Variable2, Variable3
AL, 1990, 40, 50, 60
AR, 1980, 41, 51, 61
AK, 1970, 33, 55, 66
...

And I want it in a "long" format:

AL, 1990, Variable1, 40
AL, 1990, Variable2, 50
AL, 1990, Variable3, 60
AR, 1980, Variable1, 41
AR, 1980, Variable2, 51
AR, 1980, Variable3, 61
AK, 1970, Variable1, 33
etc...

The data is easier to read into a Pivot Table that way. Currently, I'm exporting the "wide" data from Access into Stata, reshaping it, exporting it to .csv, and then using the .csv as a basis for a Pivot Table (too many rows to import data into Excel). Ideally, I'd like to skip the Stata step altogether, but don't know if it's possible to reshape data in Access. Does anybody know a) whether this is possible and b) how to do it?

Any pointers would be appreciated. Thanks!

 

[xenou]

What is qry_HPF_Transfer supposed to do?

Here you seem to be doing some kind of append or make table thing:

With CurrentDb.QueryDefs("qry[COLOR="#FF0000"]_HPF_Transfer[/COLOR]")
.Parameters("[Start Date]") = Inpt
.Execute
End With

Here you seem to be doing some of select thing:

Set rstWide = db.OpenRecordset("[COLOR="#FF0000"]qry_HPF_transfer[/COLOR]")
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

 

[Teckstein]

What is qry_HPF_Transfer supposed to do?

Here you seem to be doing some kind of append or make table thing:

With CurrentDb.QueryDefs("qry[COLOR=#FF0000]_HPF_Transfer[/COLOR]")
.Parameters("[Start Date]") = Inpt
.Execute
End With

Here you seem to be doing some of select thing:

Set rstWide = db.OpenRecordset("[COLOR=#FF0000]qry_HPF_transfer[/COLOR]")
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

I am cobbling together segments of code from other programs and examples, so I probably have it messed up.

My intent is to use the query "qry_HPF_transfer" to select records from a data table that are after a specific date - [Start Date] - specified by the user. These records are in a wide data structure.

I then want to take the records selected by the query and reshape them into a long format using the segment of code from earlier in this chain. The first four fields in the long structure identify the record from the wide structure and the last two fields are the actual data item and its label.

Make any sense?

 

[xenou]

You can probably delete these lines. They don't do anything.

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

It sounds like you intend (rewritten sub):

[COLOR="Navy"]Sub[/COLOR] HPF_Long_File()
[COLOR="Navy"]Dim[/COLOR] rstWide [COLOR="Navy"]As[/COLOR] DAO.Recordset
[COLOR="Navy"]Dim[/COLOR] rstFlat [COLOR="Navy"]As[/COLOR] DAO.Recordset
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Input_Start_Date [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Date[/COLOR]
[COLOR="Navy"]Dim[/COLOR] qdf [COLOR="Navy"]As[/COLOR] QueryDef
        
    CurrentDb.Execute "DELETE from HPF_Long_File"
    
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    Input_Start_Date = InputBox("Enter a start date")
    [COLOR="Navy"]If[/COLOR] Err [COLOR="Navy"]Then[/COLOR]
        MsgBox Err.Description
        [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] 0
    
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] ErrHandler:
    [COLOR="Navy"]Set[/COLOR] qdf = CurrentDb.QueryDefs("qry_HPF_transfer")
    qdf.Parameters("[Start Date]").Value = Input_Start_Date
    [COLOR="Navy"]Set[/COLOR] rstWide = qdf.OpenRecordset()
    [COLOR="Navy"]Set[/COLOR] rstFlat = CurrentDb.OpenRecordset("HPF_Long_File")

    [COLOR="SeaGreen"]' moves records from query results to long file format[/COLOR]
    [COLOR="Navy"]While[/COLOR] [COLOR="Navy"]Not[/COLOR] (rstWide.EOF)
        [COLOR="Navy"]With[/COLOR] rstFlat
            [COLOR="Navy"]For[/COLOR] i = 4 [COLOR="Navy"]To[/COLOR] 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
            [COLOR="Navy"]Next[/COLOR] i
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
        rstWide.MoveNext
    [COLOR="Navy"]Wend[/COLOR]
    rstWide.Close
    rstFlat.Close

ErrHandler:
MsgBox "An Error Occurred: " & Err.Description

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

 

[Teckstein]

Thanks for your coding. I will try it tomorrow when I get back to the office and let you know the results.

 

[Teckstein]

Works!

Thanks.

 

[xenou]

No problem. And to Norie.
ξ