Importing excel to access

  • Thread starter Thread starter Wendy
  • Start date Start date
W

Wendy

I have a spreadsheet that I have imported into Access as
one table. I then use append queries to import data into
appropriate tables.

I have one column in my spreadsheat that has multiple
names in each cell.
I have a table for the one side of the relationship.
I also have a table that has the primary key from the one
side of the relationship and the column that has multiple
names in each cell.
Is there a way to split these so that each will be a
record in access?
 
It's not clear which things from the EXCEL spreadsheet are to be split into
separate records; from what I read, your spreadsheet contains a single
column whose cells contain multiple information. Can you give us a bit more
detail?
 
Hi Wendy,

One way is to use a VBA function something like the air code below, to
return individual names from the field. Use it in an Append query whose
SELECT clause would be like this
SELECT PrimaryKey, GetName([MultiNameField], [Enter Item Number])
AS TheName
FROM MyTable
WHERE GetName([MultiNameField], [Enter Item Number]) IS NOT NULL
and run the query multiple times using item numbers of 0,1,2... until
it's not returning any more records.


Public Function GetName(V as Variant, _
N As Long) As Variant
Dim strSep As String
Dim varItems As Variant

If IsNull(V) Then
GetName = Null
Exit Function
End If

strSep = Chr(10) 'or whatever else separates the items

varItems = Split(cStr(V), strSep)
If N > UBound(varItems) Then
'not enough names
GetName = Null
Else
GetName = varItems(N)
End IF
End Function
 
Back
Top