How do I import a cross table from Excel into access?

  • Thread starter Thread starter PAC
  • Start date Start date
P

PAC

I am trying to add a table to an existing DB from data imported from an excel
spreadsheet. The data is a more of a cross-table than anything else - There
are names down the first column, unique acct numbers along the first
row(which will tie to an existing table in the db), and then a yes/no in each
box that they cross.
Both the DB and excel are v2007.
I am at a loss as how best to import the data so that later I can run a
query showing (as an example) all the names that have a Yes for a particular
acct.
Any and all help would be appreciated.
Thanks-
 
I am trying to add a table to an existing DB from data imported from an excel
spreadsheet. The data is a more of a cross-table than anything else - There
are names down the first column, unique acct numbers along the first
row(which will tie to an existing table in the db), and then a yes/no in each
box that they cross.
Both the DB and excel are v2007.
I am at a loss as how best to import the data so that later I can run a
query showing (as an example) all the names that have a Yes for a particular
acct.
Any and all help would be appreciated.
Thanks-
Click to expand...

Twist it back out of crosstab format... then query. Crosstabulated
data is not normalized. It should not be stored that way.

You might have to do something like this:

Public Function ReverseCrosstabSQL(ByVal intColumns As Integer) As
String
'SELECT ID, 1 as Mth, M01 as MthValue
'FROM tblImportFromExcel
'UNION ALL
'SELECT ID, 2, M02
'FROM tblImportFromExcel
'UNION ALL

Dim intCounter As Integer
Dim strSQLLine As String
Dim strSQLFinal As String

For intCounter = 1 To intColumns
If intCounter > 1 Then
strSQLLine = "SELECT ID, " & intCounter & ", M" & Format
(intCounter, "00") & " FROM tblImportFromExcel UNION ALL " & vbCrLf
Else
strSQLLine = "SELECT ID, " & intCounter & " As Mth, M" &
Format(intCounter, "00") & " As MthValue FROM tblImportFromExcel
UNION ALL " & vbCrLf
End If

strSQLFinal = strSQLFinal & strSQLLine

Next intCounter

'remove the final " UNION ALL "
ReverseCrosstabSQL = Left$(strSQLFinal, Len(strSQLFinal) - 13) &
";"
End Function
 
Back
Top