Beginner question about importing data from Access into Excel

For my work I have to write a lot of Word documents which are linked to
Excel spreadsheets for calculations and tables. I've got it all
figured out between Word and Excel, everything works. I have a series
of Word template documents extensively linked to a single Excel
worksheet which is where I do most of my work, the document really just
requiring formatting and a little bit of typing once my work in Excel
is done.

The next step for me, which would be enormously beneficial in terms of
time savings, would be to be able to link the Excel documents to an
Access database. This would save me a lot of typing things into Excel,
especially when I go back to repeat a job done earlier where much of
the data has already been entered once.

(Why can't I just re-use the old spreadsheet which presumably I have
archived from the first time I entered the data? Its because I keep
making improvements to both the spreadsheet and the Word documents and
the spreadsheet I used for a job a few months ago, which has much of
the information I require, is no longer compatible. Having a database
which retains the data and can be maintained as compatible with the
spreadsheet would eliminate this problem.)

I want to be able to pull basic information from the database, like
names and dates of birth etc, to go into specific cells in the
spreadsheet which then get manipulated and eventually find their way
into the Word document.

It seems to be easy enough using the Import external data function in
Excel to connect to the database and pull a particular piece of
information, but I want to switch between clients easily and quickly
without having to rewrite the SQL statements.

i.e. I know how to structure a query so I can pull a piece of data, a
date of birth for example, from the database, and I filter on "date of
birth" where CustomerID equals value SMITHJ, for example. I can then
go through setting up these queries for every piece of information I
want to pull from the database.

The problem with that is that if I want to switch over to customerID
JONESC and pull all of their data out, I've got to change all the
queries to JONESC by editing them all in MS Query. That's not very
efficient, I might as well retype the data.

I'd rather be able to put a desired customerID into a particular cell
in the spreadsheet and for the queries all to use that customerID
rather than a hard coded one. So after I'm done with SMITHJ I just
type JONESC into the clientID cell and Excel then imports all of Mr
Jones' data from the database. Better yet, I'd like to have a drop box
which I can use to select a client ID from a list, and once selected
the queries would all being in the selected client's data.

So how do I set this up?

Thanks in advance.

Travis

By the way, my reading has me convinced the answer lies in a "parameter
query", however for some reason the parameter query button on the
external data toolbar is greyed out.

Any ideas why this may be the case?

(I'm using Office XP if that's any help).

Travis

This is a pretty good site for a basic MS Query tutorial. Check out the
parameter query section and how to use a drop-down list to drive the query:

http://www.rdg.ac.uk/ITS/info/traini...s/excel/query/

Does that help?

••••••••••
Regards,
Ron


"travis" wrote:

> For my work I have to write a lot of Word documents which are linked to
> Excel spreadsheets for calculations and tables. I've got it all
> figured out between Word and Excel, everything works. I have a series
> of Word template documents extensively linked to a single Excel
> worksheet which is where I do most of my work, the document really just
> requiring formatting and a little bit of typing once my work in Excel
> is done.
>
> The next step for me, which would be enormously beneficial in terms of
> time savings, would be to be able to link the Excel documents to an
> Access database. This would save me a lot of typing things into Excel,
> especially when I go back to repeat a job done earlier where much of
> the data has already been entered once.
>
> (Why can't I just re-use the old spreadsheet which presumably I have
> archived from the first time I entered the data? Its because I keep
> making improvements to both the spreadsheet and the Word documents and
> the spreadsheet I used for a job a few months ago, which has much of
> the information I require, is no longer compatible. Having a database
> which retains the data and can be maintained as compatible with the
> spreadsheet would eliminate this problem.)
>
> I want to be able to pull basic information from the database, like
> names and dates of birth etc, to go into specific cells in the
> spreadsheet which then get manipulated and eventually find their way
> into the Word document.
>
> It seems to be easy enough using the Import external data function in
> Excel to connect to the database and pull a particular piece of
> information, but I want to switch between clients easily and quickly
> without having to rewrite the SQL statements.
>
> i.e. I know how to structure a query so I can pull a piece of data, a
> date of birth for example, from the database, and I filter on "date of
> birth" where CustomerID equals value SMITHJ, for example. I can then
> go through setting up these queries for every piece of information I
> want to pull from the database.
>
> The problem with that is that if I want to switch over to customerID
> JONESC and pull all of their data out, I've got to change all the
> queries to JONESC by editing them all in MS Query. That's not very
> efficient, I might as well retype the data.
>
> I'd rather be able to put a desired customerID into a particular cell
> in the spreadsheet and for the queries all to use that customerID
> rather than a hard coded one. So after I'm done with SMITHJ I just
> type JONESC into the clientID cell and Excel then imports all of Mr
> Jones' data from the database. Better yet, I'd like to have a drop box
> which I can use to select a client ID from a list, and once selected
> the queries would all being in the selected client's data.
>
> So how do I set this up?
>
> Thanks in advance.
>
> Travis
>
>

Hi Travis,

With all the dancing around you still haven't communicated enough of a
design or a complete enough scenario for us to and give meaningful
advice.

I suspect that you've kind of evolved your way to the point where you
now find yourself and are looking for that next great leap forward. I
suggest that you first take three giant steps away from the busyness
of your doing things on the computer. Get paper and pencil and start
over. You're getting ready to design so do a good job of it. :-)
For the moment, forget all about Access, Excel and Word.

Start with a one paragraph problem statement. This is a summary of
the REASONs you do what you are doing. You may have taken a bunch of
notes but the paragraph is what it's all about. This has nothing to
do with the tools you use.

Follow that with a Goal or Product statement of just a paragraph or
two describing the solution to the problem as a "black box". Make no
reference to the various Microsoft tools. At this point you could be
talking about an all paper system or some other kind of solution
platform, with or without computers. Problems, data, stimuli, etc.
come into your "black box" on the left. Some invisible, magical
transformation occurs and the solutions exit on the right. Again,
this has nothing to do with the tools you use. Give this "black box"
a name. That's the application you're going to build.

Now comes the time to sit back and reflect on how you got to where you
are - evaluating your current or "old" system.

What is the source of your input data? How does it come to you" Why
are you using Excel? Is your application compute intensive or is that
just the way things evolved? Excel can be pretty seductive. You can
be productive with it almost immediately and you can get a real sense
of achievement when you get beyond simple list management and into
writing meaningful formulas. Then you can get into writing macros and
you're really flying! I've delivered a couple of professional
applications based on Excel and I've used it to create some very
powerful pre-processing tools. Most home grown Excel tools aren't
finished to a professional level. In them the author is also the
"program" and provides the intelligence interactively to produce the
correct results.

For that matter, how great is your need for Word in what you do? Are
you doing lots of pretty printing such as complex formatting, ultra
justifying, mixed fonts and the like? Are you using bookmarks
extensively or do you do mostly mail merges?

Are you using Automation between Excel and Word or are you running
each platform independently?

Probably the biggest question of all regards Access. What's your
experience level in designing with Access? I agree that a move toward
Access would probably be hugely beneficial to you. The risk is that
the cost of getting that benefit may be too high. Access has a
steeper learning curve than Excel and Word combined. You have to
learn a lot before you can do much that's useful because you have to
learn the concepts of relational databases as well as the Access
object model. And if you don't get it right the first time you'll
have to keep doing it over. If you write macros in Excel or Word then
you already have some familiarity with VBA. Here are a couple of
quickies for your own enlightenment: what do the terms "data
normalization" and "third normal form" mean to you? If you had to
think about it or if you don't know at all then you'd probably be
unable to come up with a clean data design.

It's up to you to do the analysis and to determine if you want to
undertake a new design with Access in the mix. If you do, keep
posting back. Nothing I've written is intended to discourage you in
any way. Far from it. Better that you know what you're facing.

HTH
--
-Larry-
--

"travis" <travismorien@yahoo.com> wrote in message
news:1132420249.258247.176640@g47g2000cwa.googlegroups.com...
> For my work I have to write a lot of Word documents which are linked
to
> Excel spreadsheets for calculations and tables. I've got it all
> figured out between Word and Excel, everything works. I have a
series
> of Word template documents extensively linked to a single Excel
> worksheet which is where I do most of my work, the document really
just
> requiring formatting and a little bit of typing once my work in
Excel
> is done.
>
> The next step for me, which would be enormously beneficial in terms
of
> time savings, would be to be able to link the Excel documents to an
> Access database. This would save me a lot of typing things into
Excel,
> especially when I go back to repeat a job done earlier where much of
> the data has already been entered once.
>
> (Why can't I just re-use the old spreadsheet which presumably I have
> archived from the first time I entered the data? Its because I
keep
> making improvements to both the spreadsheet and the Word documents
and
> the spreadsheet I used for a job a few months ago, which has much of
> the information I require, is no longer compatible. Having a
database
> which retains the data and can be maintained as compatible with the
> spreadsheet would eliminate this problem.)
>
> I want to be able to pull basic information from the database, like
> names and dates of birth etc, to go into specific cells in the
> spreadsheet which then get manipulated and eventually find their way
> into the Word document.
>
> It seems to be easy enough using the Import external data function
in
> Excel to connect to the database and pull a particular piece of
> information, but I want to switch between clients easily and quickly
> without having to rewrite the SQL statements.
>
> i.e. I know how to structure a query so I can pull a piece of data,
a
> date of birth for example, from the database, and I filter on "date
of
> birth" where CustomerID equals value SMITHJ, for example. I can
then
> go through setting up these queries for every piece of information I
> want to pull from the database.
>
> The problem with that is that if I want to switch over to customerID
> JONESC and pull all of their data out, I've got to change all the
> queries to JONESC by editing them all in MS Query. That's not very
> efficient, I might as well retype the data.
>
> I'd rather be able to put a desired customerID into a particular
cell
> in the spreadsheet and for the queries all to use that customerID
> rather than a hard coded one. So after I'm done with SMITHJ I just
> type JONESC into the clientID cell and Excel then imports all of Mr
> Jones' data from the database. Better yet, I'd like to have a drop
box
> which I can use to select a client ID from a list, and once selected
> the queries would all being in the selected client's data.
>
> So how do I set this up?
>
> Thanks in advance.
>
> Travis
>