Obtaining a single Access record from Excel input
 

The standard "Import External Data" function in Excel makes it easy
enough to obtain a single record in Excel from an Access database, but
I'm trying to figure out how to use a figure in the spreadsheet in the
MS Query.

For instance, if I want to put a person's name in a cell and I want a
particular person, I can easily get this with the Query Wizard,
PersonID "equals" and then pull down the number I want from the menu.
Easy.

But how do I use a number in an Excel cell to specify what I want
PersonID to equal. This way I can sprinkle queries throughout my
spreadsheet for various things I want (address, phone number, date of
birth etc etc), and I could put the PersonID that I want in some cell
in the Excel sheet. If I want to change the PersonID, I could
accomplish it by changing just one cell in Excel and all of the queries
will then look up the appropriate person's details.

From a bit of reading I think the answer has something to do with the
"parameters" button, but this is greyed out for some reason.

Any suggestions would be greatly appreciated!

Travis

Obtaining a single Access record from Excel input
 

Travis

Look here

http://www.nickhodge.co.uk/gui/datam...taexamples.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"travis" wrote in message
oups.com...
The standard "Import External Data" function in Excel makes it easy
enough to obtain a single record in Excel from an Access database, but
I'm trying to figure out how to use a figure in the spreadsheet in the
MS Query.

For instance, if I want to put a person's name in a cell and I want a
particular person, I can easily get this with the Query Wizard,
PersonID "equals" and then pull down the number I want from the menu.
Easy.

But how do I use a number in an Excel cell to specify what I want
PersonID to equal. This way I can sprinkle queries throughout my
spreadsheet for various things I want (address, phone number, date of
birth etc etc), and I could put the PersonID that I want in some cell
in the Excel sheet. If I want to change the PersonID, I could
accomplish it by changing just one cell in Excel and all of the queries
will then look up the appropriate person's details.

From a bit of reading I think the answer has something to do with the
"parameters" button, but this is greyed out for some reason.

Any suggestions would be greatly appreciated!

Travis

Obtaining a single Access record from Excel input
 

Thanks Nick.

I've discovered the problem, the square brackets are the key!

Travis

Obtaining a single Access record from Excel input
 

Nick,

Now that I've gotten that bit figured out, I've had another problem.

One query I want to run in my Excel spreadsheet is to create a list of
a person's children.

I'd prefer my table of children to be just the right size for the
number of children, so if there are three children I want it to have
just three rows (plus the header) and if there are more children the
query should insert the extra rows as required.

The most promising looking option in the "external data range
properties" is "insert entire rows for new data, clear unused cells".
I select the entire row of the table as the destination for the data,
expecting that if there are two or more children it will just add new
rows as required.

Unfortunately, it instead inserts new columns to the left of the table
and puts the data there.

A simple workaround has been to make the children table longer than
necessary and use "Overwrite existing cells with new data, clear unused
cells" and then the children data gets plugged into the table as
desired. But then I've got extra rows which just need to be deleted
from the final document after I've broken all the links and am sending
the document to the intended recipient.

Any suggestions about what I'm doing wrong?

And one other question..

When I was first playing around with querying databases I managed to
accidentally link the database I was using to my list of available
databases, so rather than selecting "Access Database" and then
navigating to the database, I can just pick that database stright off
the list.

How do I repeat this trick? I'd like to add the database to my list,
which would save ten seconds of clicking every time I set up a new
query.

Thanks for your help.

Travis

Obtaining a single Access record from Excel input
 

Travis

Excel does not 'shrink' it's grid unfortunately without deleting additional
rows and saving you can possibly run code using

Application.UsedRange

and then save the workbook. This *may* reset it depending on version

You must have saved the query or a system datasource via the wizard to have
had a ready made 'one-click' access to the data.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"travis" wrote in message
oups.com...
Nick,

Now that I've gotten that bit figured out, I've had another problem.

One query I want to run in my Excel spreadsheet is to create a list of
a person's children.

I'd prefer my table of children to be just the right size for the
number of children, so if there are three children I want it to have
just three rows (plus the header) and if there are more children the
query should insert the extra rows as required.

The most promising looking option in the "external data range
properties" is "insert entire rows for new data, clear unused cells".
I select the entire row of the table as the destination for the data,
expecting that if there are two or more children it will just add new
rows as required.

Unfortunately, it instead inserts new columns to the left of the table
and puts the data there.

A simple workaround has been to make the children table longer than
necessary and use "Overwrite existing cells with new data, clear unused
cells" and then the children data gets plugged into the table as
desired. But then I've got extra rows which just need to be deleted
from the final document after I've broken all the links and am sending
the document to the intended recipient.

Any suggestions about what I'm doing wrong?

And one other question..

When I was first playing around with querying databases I managed to
accidentally link the database I was using to my list of available
databases, so rather than selecting "Access Database" and then
navigating to the database, I can just pick that database stright off
the list.

How do I repeat this trick? I'd like to add the database to my list,
which would save ten seconds of clicking every time I set up a new
query.

Thanks for your help.

Travis

Obtaining a single Access record from Excel input
 

Thanks for your replies Nick, they're appreciated.

I've found out how I managed to get my database onto the menu, its just
a matter of selecting "New Data Source", from the Import External Data
| New Database query, giving it a name and selecting the Access driver,
then navigating to it.

From the next time you do a new query, the database will be on the menu
and there will be no need to navigate to it.

As for the other, I'd prefer not to have to delete cells at all. I'm
just wondering if there is a way to get it to insert rows.

I'd start with a table that just has a header and one line, if the
table needs new rows for extra kids then it should add them.

I'd like it to "insert entire rows for new data, clear unused cells",
but for some stupid reason Excel doesn't actually do that when the
option is selected. Instead of inserting new rows, it inserts new
columns. So if I'm taking four fields for five children, I'll pick up
four new columns and the records will run down five rows. I'd prefer
these just to be inserted in the range where I request them to go...

And for that matter, the exact same thing happens with "insert cells
for new data, clear unused cells".

Only "overwrite existing data" puts the data in without inserting any
new columns, but then it overwrites cells unless I make the table big
enough to take the maximum number of fields, so I make the table 10
rows long and hope that nobody comes along with 11 children.

Travis