Inverse Crosstab Query?

[JHHPavel]

I imported an Excel table into Access. The rows were projects, the columns were week-ending dates, and the values were the hours spent on the projects each week. I would like to unscramble this table to the atomic data bits that make it up, one record for each project-weekending-hours combination of data. Is there any way to do this, other than writing a series of queries, one for each column?

For example, the table below

1/7 1/14
ProjA 8 35
ProjB 21 7

would give four three-field rows in a table: ProjA-1/7-8, ProjA-1/14-35, ProjB-1/7-21 and ProjB-1/14-7.

 

[Andrzejek]

Your table is not normalized.

Consider this format:[tt]
tblMyTable
Project MyDate MyHrs
ProjA 1/7 8
ProjA 1/14 35
ProjB 1/7 21
ProjB 1/14 7[/tt]

Have fun.

---- Andy

 

[lameid]

JHHPavel,

Not really. Like anything else you could use a recordset instead of a query but why would you want that overhead.

Your best bet is to use VBA to loop through the fields and concatenate a SQL statement to run...

Something to get you started... I did not bother making the SQL statement since I am missing most of those details.

Dim strSQL as string
dim strField as string

dim flds as DAO.Fields
dim fld as DAO.Field


Set flds = Currentdb.TableDefs("ImportedTableName")

For Each fld In flds
    If instr(1, fld.name, "/") > 0 Then
         strField = fld.name
         strSQL = "" 'Build your query here
         docmd.RunSQL strSQL
    End if
Next fld

 

[lameid]

As an aside, the data looks like it could be the result of an Excel Pivot table... If so it would be easier to just find the source data within Excel but that is a whole other forum.

 

[dhookom]

You can use a union query like:

SELECT ProjNum, [1/7] as Hours, #1/7/2012# as WeekEnding
FROM import
UNION ALL
SELECT ProjNum, [1/14], #1/14/2012#
FROM import

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

I see looking back that I did not make it clear I was giving you a solution to normalize the data. I intended strSQL to be an Append / Insert Into query.

Duane's solution of course does not involve a new table. Unions are slow as they are multiple queries so I generally avoid - Somehow this is what I thought was asked but I see that it was not that specific.

If you wanted to make a more generic solution that just selects data, you could start with my code to build a Union query like Duane demonstrates (although I would change the SQL property of a querydef object rather than trying to run a SQL statement).

You should decide whether the logical (query) or physical (in a table) result is what you are after.

I would say if you intend to keep the data you want the physical solution and if you want to use it once you should probably go for the logical rather than creating and deleting data all the time.