Importing Crosstabs into Access

[twaccess]

Does anyone know of an easy way to import a excel based crosstab into Access such that the column headers are easily cross matched with the row headers to create a single line of data.

I'm hoping that the above question is clear to someone who is able to answer. If not, I'll bump the thread up again with a more detailed question of what I am trying to do.

Thanks


Terry

 

[twaccess]

I decided to spell things out a little bit more anyway...

This is the table I have...

Area 1 Area 2 Area 3 Area 4
Item 1 21 25 86 22
Item 2 11 50 97 77
Item 3 29 66 60 80
Item 4 68 54 61 85
Item 5 1 80 44 64
Item 6 66 59 59 62
Item 7 62 25 56 32
Item 8 44 19 36 53
Item 9 64 83 3 40
Item 10 19 41 37 17
Item 11 86 11 22 3

And I want to create data looking like this...

Area 1 Item 1 21
Area 1 Item 2 11
Area 1 Item 3 29
Area 1 Item 4 68
Area 1 Item 5 1
Area 1 Item 6 66
Area 1 Item 7 62
Area 1 Item 8 44
Area 1 Item 9 64
Area 1 Item 10 19
Area 1 Item 11 86
Area 2 Item 1 25
Area 2 Item 2 50
Area 2 Item 3 66
Area 2 Item 4 54
and so on and on...

Hope this explains my question better...

Thanks


Terry

 

[twaccess]

Thanks Pat

I remembered overnight that a colleague did some ExcelVB code which works specifically on a huge set of cross tabs that I had to work with last year. It works really well, but my problem is that I have no experience of ExcelVB to be able to tweak this and use it for future crosstabs that I need to normalise. (I like that word btw..)

Here's the code if you're interested.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/01/2002 by Terry White
'
' Keyboard Shortcut: Ctrl+a
'
Dim nname, sname, c, r, i, j, tcount As Integer
sname = ActiveSheet.Name
Sheets.Add
nname = InputBox("Please type the name for new sheet.")
ActiveSheet.Name = nname
Sheets("" & nname).Cells(1, 1) = "Outline description"
Sheets("" & nname).Cells(1, 2) = "Floor"
Sheets("" & nname).Cells(1, 3) = "Quantity"
tcount = 2
Sheets("" & sname).Activate
Select Case Cells(6, 4)
Case Is = "Outline description"
Case Else
MsgBox "The sheet is not the correct format. "
End
End Select
c = Cells(6, 4).End(xlToRight).Column - 2
r = Cells(6, 4).End(xlDown).Row - 1
For j = 7 To r
For i = 5 To c
Select Case Cells(j, i)
Case Is = ""
Case Else
Sheets("" & nname).Cells(tcount, 1) = Cells(j, 4)
Sheets("" & nname).Cells(tcount, 2) = Cells(6, i)
Sheets("" & nname).Cells(tcount, 3) = Cells(j, i)
tcount = tcount + 1
End Select
Next i
Next j


End Sub

Regards


Terry