Re: Excel 2000 to Access 2000

From: Nikos Yannacopoulos (nyannacoREMOVETHISBIT_at_in.gr)
Date: 03/17/04 

Date: Wed, 17 Mar 2004 11:24:32 +0200

Trish,

I'm not claiming there's no better solution, but I have one that's worth
giving a shot if your spread*** is big...
It takes a step in Excel first: assuming your data in Excel is already in
Columns (if colon delimited as below, then just do a Text to Columns); then
use an extra column to add a unique number for each group of data, by using
formula:
=INT((ROW(A1)-1)/3+1)
for data starting in row 1, or
=INT((ROW(A3)-ROW(A$2)-1)/3+1)
for data starting in row 3 (change the cell references accordingly for any
other starting row)
and copy down to the end of the data. Then save the spread*** and go to
Access.
Use menu item File > Get External Data > Link to make the spread***
available to Access as a linked table.
Make a crosstab query on it, using the column with the repeating field names
as Column headings, the one with names etc. as Values (Totals function >
First) and the one with the numbers as Row Headings. This should get you the
data in a table-like format. This done, you can make a new query on the
previous one, that can be either a make-table or append one, to get the data
in a table. The numbers inserted with the trick in Excel can well serve as a
PK if you don't want an autonumber, otherwise you don't need to put them in
the table.

HTH,
Nikos

"Trish" <anonymous@discussions.microsoft.com> wrote in message
news:474CF13E-EA7A-4A49-9299-A394F1047AC1@microsoft.com...
> I have received data in an Excel spread*** that I wish to import into
Access 2000. The data in the spread***, unfortunately, has the columns
at the side and the details across the page, which means that the headings
are constantly repeated, eg:
>
> Name: Jo Bloggs
> Company: Jo Bloggs & Co
> City: New York
> Name: Sue Smith
> Company: Sue Smith's boutique
> City: Los Angeles
> Name: John Brown
> Company: John Brown Seafood Delights
> City: Washington
>
> How can I bring this into Access properly? Grateful for any suggestions.
>