Inverse Crosstab Query

  • Thread starter Thread starter Justin
  • Start date Start date
J

Justin

I need to import data from an Excel file. I want to take the data stored in
cloumns and convert into records as shown below.

Excel Format
Employee Date Direct Indirect R&D Vacation Sick
Justin 5/21/09 0 0 8 0 0

I want
Employee Date Type Hours
Justin 5/21/09 R&D 8


There are many more columns so nested IIF wouldn't be ideal.
Thanks in advance for any assistance
 
One approach would be to create a series of queries, one for each category.

You'd import the raw data as is, then use those "normalizing" queries to
write a record to your more permanent table for each Employee X Date X
Category combination.

(by the way, I hope your fieldname isn't "Date" -- Microsoft Access treats
that as a reserved word and may not do what you expected...)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I need to import data from an Excel file. I want to take the data stored in
cloumns and convert into records as shown below.

Excel Format
Employee Date Direct Indirect R&D Vacation Sick
Justin 5/21/09 0 0 8 0 0

I want
Employee Date Type Hours
Justin 5/21/09 R&D 8


There are many more columns so nested IIF wouldn't be ideal.
Thanks in advance for any assistance
Click to expand...

A "Normalizing Union Query" is useful in cases like this. You need to go to
the SQL view of a query to do this, the query grid can't handle it. Try:

SELECT Employee, [Date], "Direct" AS [Type], [Direct] AS Hours
FROM spreadsheet
WHERE [Direct] > 0
UNION ALL
SELECT Employee, [Date], "Indirect" AS [Type], [Indirect] AS Hours
FROM spreadsheet
WHERE [Indirect] > 0
UNION ALL
SELECT Employee, [Date], "R&D" AS [Type], [R&D] AS Hours
FROM spreadsheet
WHERE [R&D] > 0
UNION ALL
SELECT Employee, [Date], "Vacation" AS [Type], [Vacation] AS Hours
FROM spreadsheet
WHERE [Vacation] > 0
UNION ALL
SELECT Employee, [Date], "Sick" AS [Type], [Sick] AS Hours
FROM spreadsheet
WHERE [Sick] > 0

<etc, you can probably manage up to 25 or 30 columns before the Query Too
Complex error hits>

Then base an Append query on this UNION query.
 
Or, to gild the lily...

You could create a form with a listbox and a command button... (if you
have a LOT of columns to process...) Otherwise, might not be worth
the trouble...

the listbox (mine is called 'lstFields':
On the DATA tab:
Row Source Type: Field List
Row Source: <the table you described>
On the OTHER tab:
Multi-Select: Extended

The button:
Private Sub cmdListSelected_Click()

'complete SQL, then use REPLACE() to fix...
Const cBASE_SQL As String = "INSERT INTO NormalizedTable
( Employee, ReportDate, Type, Hours ) SELECT NonNormal.Employee,
NonNormal.ReportDate, 'Direct' AS Type, NonNormal.[Direct] AS Hours
FROM NonNormal WHERE ((([Direct]) Is Not Null) AND ((NonNormal.Direct)
0));"
Click to expand...

Dim strSQL As String
Dim varItem As Variant

For Each varItem In Me.lstFields.ItemsSelected
'---SQL statement to normalize and insert into a new table...
strSQL = Replace(cBASE_SQL, "Direct", Me.lstFields.ItemData
(varItem))
DBEngine(0)(0).Execute strSQL, dbFailOnError

Next varItem
MsgBox "done!" '--- just stupid feedback, so you know everything
completed.
End Sub

Or you could just write the monster union query with that... just
tweak cBASE_SQL so it's a select statement and put UNION between each
one in your loop...

Then query away.
 
Back
Top