Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.access
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.access
|
|||
|
|||
Beginner question about importing data from Access into Excel
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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.access,microsoft.public.excel
|
|||
|
|||
Beginner question about importing data from Access into Excel
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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.access
|
|||
|
|||
Beginner question about importing data from Access into Excel
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" wrote in message oups.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing data from Excel to Access | Excel Discussion (Misc queries) | |||
Importing Data from Access into Excel | Excel Discussion (Misc queries) | |||
Importing Access record into Excel using MSQUERY question | Excel Programming | |||
problem with importing data from access to excel | Excel Programming | |||
Importing Access data to Excel | Excel Programming |