Amazon says it's official. My new book will be out on April 5th.
This book is written for all you Excel users who know that expanding your skill-set to include Access can make you more productive. I guide you through analytical and reporting benefits of Access, and show you how you can integrate Excel and Access to make your life easier.
To celebrate this new arrival, I've decided to post Chapter 1 of this book as today's blog entry. Enjoy!
It's been a busy week and I've been working hard. I think I'll reward myself by writing a blog post for people I've never met. Here we go.
Let's talk about getting Access Memo Fields into Excel.
If you move data from Excel to Access on a regular basis, you'll know that sometimes Access can't resolve the data being imported from Excel. In these cases Access automatically creates a new table called ImportErrors..
. » More: Clearing Access ImportError Tables
I'm in the process of writing my Pulitzer worthy book "An Excel Analyst's guide to Access". During the writing process, chapters get reviewed by a team of editors – one of which is a Technical Editor.
The Technical Editor on this book is Dick Kusleika (Daily Dose of Excel). Kusleika is one of the better technical editors I've worked with. I think his accountant's detail-oriented brain lends itself nicely to tech editing.
Quick Joke: Did you hear about extroverted accountant? He looks down at your shoes.
.
During his editing, Kusleika pointed out that that the serial dates in Access actually start at December 31, 1899 (not January 1, 1900 like in Excel).
Just the other day, I saw someone run an Access Query in Office 2003. Then he selected Office Tools ->Analyze with Excel. This essentially sends the results of the Access query to Excel.
.
This if fine and dandy, but there is a slightly easier way to get the results of an Access query to Excel.
Clients usually have this pesky need to have everything documented. As a generally lazy person, this offends my lackadaisical attitude. Nevertheless, I typically oblige, given the fact that I want them to take over the work at some point.
Over the years, I've rolled my own documentation tools to help in this endeavor. Today, I want to share a small procedure I use to document Access queries in Excel.
An Access parameter query is a kind of interactive query that prompts you for criteria before the query is run. Parameter queries are useful when you need to ask the query different questions using different criteria each time you run it.
Now we all know you can pull data from Access into Excel using MS Query. The problem is that MS Query doesn't let you pull in Parameter queries. There may be a technical reason for this, but I like to think it's just Microsoft's way of keeping things interesting. After all, technical roadblocks are the spice of life.
If you've had enough spice, I'll show you a VBA workaround that will enable you to run an Access parameter query from Excel.
Today, I 'd like to show you a nifty trick that will allow you to send Variable length tables from Access to a specific Excel Range without code.
That's right, I said without any VBA coding. I know you're as excited as I am, so here we go.
I often use MS Query to pull external data into Excel. I find it to be an extremely powerful way to quickly pull together interactive reporting without a lot of effort. If you're new to MS Query, you may want to check out Kusleika's intro to MS Query to get the gist.
.
Anyway, one of the more common requests I get is to allow a user to dynamically pass multiple criteria to a parameter. For example, a manager may want to pull in data for Tulsa, and Dallas, and Seattle at the same time.
» More: Passing Multiple Values to One Parameter
In my Pulitzer worthy post, Using Access to Combine Multiple Excel Files: Method 1, I showed you a simple way to leverage linked objects in Access to combine multiple Excel files.
This weekend, while checking out some of the newsgroups, I ran across a question from Ashish who asks:
"How can I specify the row number from where the data consolidation should start? I.e. I want to somehow exclude the rows (at the top of the 3 sheets) which I do not want to consolidate."
In other words, Ashish needs to specify a particular range from which to pull and consolidate the data.
» More: Using Access to Combine Multiple Excel Files: Method 2
