how to stack column data in Access?

[Guest]

I am trying to stack data listed in columns- specifically, combine data from
5 columns into 2. I have work hours data spread over 5 columns by quarter,
e.g. the first column is Q106, second column Q206, etc., through the last
column (Q107). This is the only way you can export/ copy timephased data
from Project. I want to transform the table so that I have one column
listing the quarter (e.g. Q106 or Q206, etc.) and one column listing the work
hours. I have stacked column data in JMP statistical software before, but
have not been able to perform this function using any Microsoft products yet
- haven't been able to do it in Excel either. I figure this must be doable
in Access, but cannot figure out how. Please help! I am using Access 97.

 

[Pieter Wijnen]

Import the data as is into a new table (ProjImport)
Create Your new table (Proj)
Create the following function

Public Function NormalizeProject(Optional Byval FTab AS
String="PROJIMPORT",Optional ByVal TTab AS String="PROJ") As long

Dim Db AS DAO.database
Dim Rs AS DAO.Recordset
Dim QDef AS DAO.QueryDef
Dim Parm AS DAO.Parameter
Dim Fld AS DAO.Field
Dim Cnt As long
Dim SQL AS String

Set Db = Access.CurrentDb()
SQL = "PARAMETERS pQ Text, pD Double;" & VBA.vbCRLF & _
"INSERT INTO " & TTab & "(QRTR,DATA) VALUES(pQ,pD);"
Set QDef = Db.CreateQueryDef(VBA.VbNullString,SQL)
Set Rs = Db.OpenRecordset("SELECT * FROM " & Ftab,DAO.DbOpenSnapshot)
While Not Rs.EOF
For Each Fld In Rs.Fields
Qdef.Parameters("pQ").Value = Fld.Name
Qdef.Parameters("pD").Value = Fld.Value
Qdef.Execute, DAO.DbSeeChanges
Cnt = Cnt+Qdef.RecordsAffected
Next 'Fld
Rs.MoveNext
Wend
Rs.Close : Set Rs = Nothing
Set Qdef = Nothing
Set Db = Nothing
NormalizeProject = Cnt
End Function

HTH

Pieter

 

[Ron2005]

I am sorry, but I can't visualize what your data looks like.

I think I can picture your output but with some questions. Do you end
up with a table with 5 rows and 2 columns or is there something else
going on also.

example
Q106 20.9
Q206 50.0
Q306 20.1
Q406 50.2
Q107 20.3

 

[Guest]

Yes, exactly. If I have a table with this:
Q106 Q206 Q306
20 hrs 20 hrs 10 hrs

I want to output this:
Quarter Work
Q106 20 hrs
Q206 20 hrs
Q306 10 hrs

Is there an easy way to transform the data in this way?

 

[Guest]

Are you limited to the number of quarters you are talking about? Is this
something that only needs to be run once? If so, sn easy way to accomplish
this would be to have different append queries. The first one would have
criteria on the Q106 field like >0. Then append the hours into an hours
field and have an expression field append to Quarter (like Quarter:"Q106").
Run the query and then change the criteria to Q206 >0 and Quarter:"Q206", etc.