Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links,microsoft.public.access.importexportlink
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.links,microsoft.public.access.importexportlink
|
|||
|
|||
Obtaining a single Access record from Excel input
Thanks Nick.
I've discovered the problem, the square brackets are the key! Travis |
#4
Posted to microsoft.public.excel.links,microsoft.public.access.importexportlink
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.links,microsoft.public.access.importexportlink
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.links,microsoft.public.access.importexportlink
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel as Data Entry into Access Database | Excel Discussion (Misc queries) | |||
record an excel macro, save on network so that everyone can access | Excel Discussion (Misc queries) | |||
Excel aficionado wants to learn Access | Excel Discussion (Misc queries) | |||
Excel user desires to learn ABC of Access | Excel Discussion (Misc queries) | |||
How do I save a record from an excel template to a database in a . | Excel Discussion (Misc queries) |