By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,583 Members | 617 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,583 IT Pros & Developers. It's quick & easy.

Excel to Access export - query possible?

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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 TransferSpreadsheet 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" <ma**********@hotmail.com> wrote in message
news:da*************************@posting.google.co m...
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.

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.