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

[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-

 

[Piet Linden]

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-

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

 

[ryguy7272]

This should answer most of your importing (and exporting) questions:
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

This will probably do what you want:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpBrowseFile

HTH,
Ryan---