Excel to Access export - query possible?

[Ellen Manning]

I'm trying to export an Excel2K spreadsheet to A2K. Here is a sample
of the Excel spreadsheet:

LastName FirstName Hours Location HoursPercent
Doe John 9 WMC 2.94
VA Med Ctr 265 VA 86.60
32 VA Res Clinic 10.45
<blank row>


This is how I'd like to export it to an A2K table

Doe John VA Med Ctr 9 WMC 2.94
Doe John VA Med Ctr 265 VA 86.60
Doe John VA Med Ctr 32 VA Res Clinic 10.45

Any way to do this with a query? I'm thinking I'm going to have to do
some coding but thought I'd check and see if anyone else has had this
situation.

Thanks for any help or advice.

[MacDermott]

Re: Excel to Access export - query possible?

For starters, I'd suggest you think about importing into Access, rather than
exporting from Excel.
That is, any "action" will take place in Access, rather than Excel.
Since your rows in Excel vary, it will be hard to put the data directly into
a table in the format you want.
You can use TransferSpreads heet to bring the spreadsheet contents into a
generic table.
I think then I'd write DAO code to step through the recordset and parse
things the way I wanted.

BTW, this might be a good time to consider creating a separate table for
Doctors. Chances are you'll want to store more information about Dr. Doe
than just his name (e.g. contact info); a relational table will mean you
won't have to repeat that information on every row of this table, and you
won't ever have to worry about one row getting out of synch with the others.
Relational tables are some of what Access does best.

I've kept this general, as an outline of what might be ahead. If you decide
to pursue this path, and need help with details, please post back.
OTOH, if you're handier with Excel, you might want to doctor your
spreadsheet in Excel to fill in those empty fields and make the data more
uniform.

HTH
"Ellen Manning" <manning_news@h otmail.com> wrote in message
news:da7d13b.04 11191404.1d0b9b b1@posting.goog le.com...[color=blue]
> I'm trying to export an Excel2K spreadsheet to A2K. Here is a sample
> of the Excel spreadsheet:
>
> LastName FirstName Hours Location HoursPercent
> Doe John 9 WMC 2.94
> VA Med Ctr 265 VA 86.60
> 32 VA Res Clinic 10.45
> <blank row>
>
>
> This is how I'd like to export it to an A2K table
>
> Doe John VA Med Ctr 9 WMC 2.94
> Doe John VA Med Ctr 265 VA 86.60
> Doe John VA Med Ctr 32 VA Res Clinic 10.45
>
> Any way to do this with a query? I'm thinking I'm going to have to do
> some coding but thought I'd check and see if anyone else has had this
> situation.
>
> Thanks for any help or advice.[/color]