Driven to Distraction

Are your business problems making you insane? In his debut column, 37signals co-founder Jason Fried argues that one of the keys to success is to let your lazy side guide you.

I think of myself as wildly ambitious and unapologetically lazy. Though we've all heard about the good things that come from ambition, laziness gets a bad rap. That's unfortunate. I can attribute a healthy chunk of my success to the positive returns of laziness. Laziness has the best ROI in the business.

Let's start at the beginning. I launched my first real company, a Web design company called Spinfree, in 1996. It was a solo show: just me, a desk in my apartment, and some self-taught mediocre Web design skills. But it was all I needed. The jobs rolled in, and my clients were happy. I could pay the bills, stash away some savings, and work when and where I wanted.

But I wasn't happy. Rather than building confidence, I was accumulating doubt. As my business expanded, I grew nervous and self-conscious. I began to feel as if my accomplishments weren't enough, that I had to take things to "the next level." I thought if I didn't get there fast enough, I'd be bowled over by the competition.

When I bid on projects against larger design firms, I started saying "we" instead of "I" in an attempt to sound bigger. The proposals submitted by my rivals were long and shiny, so mine had to be longer and shinier. I even began badmouthing the competition -- people I'd never met. That's ugly.

The thing is, I didn't need to do any of these things. I thought I did, but I didn't. I was inventing problems. I was making things hard on myself.

How did I figure this out? Laziness. I got tired and let down my guard and wound up learning something important about myself: I love work, just not hard work. I think hard work is overrated. My goal is to do less hard work. And what's hard? Acting like someone else, writing elaborate proposals I don't believe in, and flinging mud at the competition. That's hard and horrible work.

So I put my laziness to work for me. Instead of long proposals, I wrote short ones. Instead of worrying about competitors, I ignored them. And here's what happened: My company got more work. I found better clients. I slept better. I woke up better. I was happier. And, most of all, running a business became a lot easier.

Fifteen years later, this continues to be the most important lesson I've learned as an entrepreneur: Most of the stuff you agonize about just doesn't matter. Truth is, things are pretty easy and straightforward -- until you make them hard and complicated.

This is the ethos that drives what we do at 37signals, the company I co-founded in 1999. We make simple Web-based collaboration software for small businesses and groups. We have millions of users -- and millions in profits -- but we're just 16 people. We don't act any bigger or smaller. We don't put on airs. We just are who we are.

We don't worry much about what the competition is doing. We don't worry about growing pains we don't have yet. We don't spend time on five-year plans and forecasts, because in my experience, they just don't matter.

We invent software, not problems. Real problems will find you; you don't need to invite fake ones to dinner.

Yet that's precisely what many business owners do. I spend a lot of my time speaking with entrepreneurs and entrepreneurs-to-be. They e-mail me, call me at the office, hit me up on Twitter, or introduce themselves at conferences and events. And for the most part, they have one thing in common: They're scared. Worried. Insecure. Just like I was.

It's easy to see why. Conventional business wisdom breeds paranoia. If you don't get big fast, you lose. If you don't obsess about the competition, you will be crushed. If you don't make long-term plans, you'll be staggering in the dark.

Come on. Conventional wisdom is tired, upset, groggy, scared, and a pain in the ass to work with. It doesn't have to be like this.

Instead of spending your time worrying about what could, might, or may happen, spend your time on what matters now. Are your customers thrilled with your service today? Is your inbox flooded with word-of-mouth referrals today? Do your employees love their jobs today? Can people find what they're looking for on your website today? Be honest with yourself. If the answers aren't satisfactory, then I'd suggest that you truly have something to worry about -- no matter how beautiful and comprehensive your business plan is.

Tomorrow. Eventually. Next quarter. Next year. Five years from now. Exit strategy. Throw these words away. They don't matter. Today is all you have in business. Tomorrow is just today again. Next week? Seven todays in a row. A month isn't 30 days. It's 30 todays.

I'm not suggesting you stop thinking about the future. I'm telling you to stop stressing about it. Go on, get lazy.

Jason Fried is co-founder of 37signals, a Chicago-based software firm, and co-author of the book Rework, which was published in March. This is his first column for Inc.

http://www.inc.com/magazine/20100401/driven-to-distraction.html

5 Secrets of Self-Made Millionaires

They’re just like you. But with lots of money.

When you think “millionaire,” what image comes to mind? For many of us, it’s a flashy Wall Street banker type who flies a private jet, collects cars and lives the kind of decadent lifestyle that would make Donald Trump proud.

But many modern millionaires live in middle-class neighborhoods, work full-time and shop in discount stores like the rest of us. What motivates them isn’t material possessions but the choices that money can bring: “For the rich, it’s not about getting more stuff. It’s about having the freedom to make almost any decision you want,” says T. Harv Eker, author of Secrets of the Millionaire Mind. Wealth means you can send your child to any school or quit a job you don’t like.

According to the Spectrem Wealth Study, an annual survey of America’s wealthy, there are more people living the good life than ever before—the number of millionaires nearly doubled in the last decade. And the rich are getting richer. To make it onto the Forbes 400 list of the richest Americans, a mere billionaire no longer makes the cut. This year you needed a net worth of at least $1.3 billion.

If more people are getting richer than ever, why shouldn’t you be one of them? Here, five people who have at least a million dollars in liquid assets share the secrets that helped them get there.

PLUS: 13 Things Your Financial Adviser Won't Tell You

1. Set your sights on where you’re going
Twenty years ago, Jeff Harris hardly seemed on the road to wealth. He was a college dropout who struggled to support his wife, DeAnn, and three kids, working as a grocery store clerk and at a junkyard where he melted scrap metal alongside convicts. “At times we were so broke that we washed our clothes in the bathtub because we couldn’t afford the Laundromat.” Now he’s a 49-year-old investment advisor and multimillionaire in York, South Carolina.

There was one big reason Jeff pulled ahead of the pack: He always knew he’d be rich. The reality is that 80 percent of Americans worth at least $5 million grew up in middle-class or lesser households, just like Jeff.

Wanting to be wealthy is a crucial first step. Says Eker, “The biggest obstacle to wealth is fear. People are afraid to think big, but if you think small, you’ll only achieve small things.”

PLUS: 17 Things Your Mother Wants You to Know

It all started for Jeff when he met a stockbroker at a Christmas party. “Talking to him, it felt like discovering fire,” he says. “I started reading books about investing during my breaks at the grocery store, and I began putting $25 a month in a mutual fund.” Next he taught a class at a local community college on investing. His students became his first clients, which led to his investment practice. “There were lots of struggles,” says Jeff, “but what got me through it was believing with all my heart that I would succeed.”

2. Educate yourself
When Steve Maxwell graduated from college, he had an engineering degree and a high-tech job—but he couldn’t balance his checkbook. “I took one finance class in college but dropped it to go on a ski trip,” says the 45-year-old father of three, who lives in Windsor, Colorado. “I actually had to go to my bank and ask them to teach me how to read my statement.”

One of the biggest obstacles to making money is not understanding it: Thousands of us avoid investing because we just don’t get it. But to make money, you must be financially literate. “It bothered me that I didn’t understand this stuff,” says Steve, “so I read books and magazines about money management and investing, and I asked every financial whiz I knew to explain things to me.”

PLUS: 6 Moneymaking Tips

He and his wife started applying the lessons: They made a point to live below their means. They never bought on impulse, always negotiated better deals (on their cars, cable bills, furniture) and stayed in their home long after they could afford a more expensive one. They also put 20 percent of their annual salary into investments.

Within ten years, they were millionaires, and people were coming to Steve for advice. “Someone would say, ‘I need to refinance my house—what should I do?’ A lot of times, I wouldn’t know the answer, but I’d go find it and learn something in the process,” he says.

In 2003, Steve quit his job to become part owner of a company that holds personal finance seminars for employees of corporations like Wal-Mart. He also started going to real estate investment seminars, and it’s paid off: He now owns $30 million worth of investment properties, including apartment complexes, a shopping mall and a quarry.

“I was an engineer who never thought this life was possible, but all it truly takes is a little self-education,” says Steve. “You can do anything once you understand the basics.”

PLUS: 17 French Restaurant Words You Need to Know

3. Passion pays off
In 1995, Jill Blashack Strahan and her husband were barely making ends meet. Like so many of us, Jill was eager to discover her purpose, so she splurged on a session with a life coach. “When I told her my goal was to make $30,000 a year, she said I was setting the bar too low. I needed to focus on my passion, not on the paycheck.”

Jill, who lives with her son in Alexandria, Minnesota, owned a gift basket company and earned just $15,000 a year. She noticed when she let potential buyers taste the food items, the baskets sold like crazy. Jill thought, Why not sell the food directly to customers in a fun setting?

PLUS: 15 Foods You Should Never Buy Again

With $6,000 in savings, a bank loan and a friend’s investment, Jill started packaging gourmet foods in a backyard shed and selling them at taste-testing parties. It wasn’t easy. “I remember sitting outside one day, thinking we were three months behind on our house payment, I had two employees I couldn’t pay, and I ought to get a real job. But then I thought, No, this is your dream. Recommit and get to work.”

She stuck with it, even after her husband died three years later. “I live by the law of abundance, meaning that even when there are challenges in life, I look for the win-win,” she says.

PLUS: 20 Secrets Your Waiter Won't Tell You

The positive attitude worked: Jill’s backyard company, Tastefully Simple, is now a direct-sales business, with $120 million in sales last year. And Jill was named one of the top 25 female business owners in North America by Fast Company magazine.

According to research by Thomas J. Stanley, author of The Millionaire Mind, over 80 percent of millionaires say they never would have been successful if their vocation wasn’t something they cared about.

Enhance your IQ with our new Word Power game!

4. Grow your money
Most of us know the never-ending cycle of living paycheck to paycheck. “The fastest way to get out of that pattern is to make extra money for the specific purpose of reinvesting in yourself,” says Loral Langemeier, author of The Millionaire Maker. In other words, earmark some money for the sole purpose of investing it in a place where it will grow dramatically—like a business or real estate.

There are endless ways to make extra money for investing—you just have to be willing to do the work. “Everyone has a marketable skill,” says Langemeier. “When I started out, I had a tutoring business, seeing clients in the morning before work and on my lunch break.”

A little moonlighting cash really can grow into a million. Twenty-five years ago, Rick Sikorski dreamed of owning a personal training business. “I rented a tiny studio where I charged $15 an hour,” he says. When money started trickling in, he squirreled it away instead of spending it, putting it all back into the business. Rick’s 400-square-foot studio is now Fitness Together, a franchise based in Highlands Ranch, Colorado, with more than 360 locations worldwide. And he’s worth over $40 million.

PLUS: 10 Smart Money Moves to Make Now

When extra money rolls in, it’s easy to think, Now I can buy that new TV. But if you want to get rich, you need to pay yourself first, by putting money where it will work hard for you—whether that’s in your retirement fund, a side business or investments like real estate.

5. No guts, no glory
Last summer, Dave Lindahl footed the bill for 18 relatives at a fancy mansion in the Adirondacks. One night, his dad looked out at the scenery and joked, “I can’t believe we used to call you the black sheep!”

At 29, Dave was broke, living in a small apartment near Boston and wondering what to do after ten years in a local rock band. “I looked around and thought, If I don’t do something, I’ll be stuck here forever.”

He started a landscape company, buying his equipment on credit. When business literally froze over that winter, a banker friend asked if he’d like to renovate a foreclosed home. “I’m a terrible carpenter, but I needed the money, so I went to some free seminars at Home Depot and figured it out as I went,” he says.

Download our Life IQ trivia game for your iPhone.

After a few more renovations, it occurred to him: Why not buy the homes and sell them for profit? He took a risk and bought his first property. Using the proceeds, he bought another, and another. Twelve years later, he owns apartment buildings, worth $143 million, in eight states.

The Biggest Secret? Stop spending.
Every millionaire we spoke to has one thing in common: Not a single one spends needlessly. Real estate investor Dave Lindahl drives a Ford Explorer and says his middle-class neighbors would be shocked to learn how much he’s worth. Fitness mogul Rick Sikorski can’t fathom why anyone would buy bottled water. Steve Maxwell, the finance teacher, looked at a $1.5 million home but decided to buy one for half the price because “a house with double the cost wouldn’t give me double the enjoyment.”

http://shine.yahoo.com/event/financiallyfit/5-secrets-of-self-made-millionaires-1370279/

Examples for Exporting to EXCEL Workbook Files

Create and Export a Parameter Query to EXCEL file via TransferSpreadsheet (VBA)

Create a Query and Export multiple "filtered" versions of a Query (based on data in another table) to separate EXCEL files via TransferSpreadsheet (VBA)

Create a Query and Export multiple "filtered" versions of a Query (based on data in another table) to separate Worksheets within one EXCEL file via TransferSpreadsheet (VBA)

Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA)

Write Data From a Recordset into an EXCEL Worksheet using EXCEL's CopyFromRecordset (VBA)

Browse to a single EXCEL File and Export Data to that EXCEL File via TransferSpreadsheet (VBA)

Browse to a single Folder and Export Data to a New EXCEL File in that Folder via TransferSpreadsheet (VBA)

Using the Range Argument of TransferSpreadsheet when Exporting Data to an EXCEL File (VBA)

Create and Export a Parameter Query to EXCEL file via TransferSpreadsheet (VBA)

Generic code to generate "on the fly" a query that uses one or more controls on an open form as parameters, and then export that query to an EXCEL file. This example concatenates the parameter values into the generated SQL statement and then saves the query so that it can be exported. The query then is deleted after the export is completed.

Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb

' Replace NameOfTableOrQuery with the real name of the table or query,
' replace NameOfForm with the real name of the form, and replace
' ADateControlOnForm and AnotherDateControlOnForm with the real names
' of the controls on that form
strSQL = "SELECT NameOfTableOrQuery.* FROM NameOfTableOrQuery " & _
"WHERE NameOfTableOrQuery.FieldName >= " & _
Format(Forms!NameOfForm!ADateControlOnForm.Value,"\#mm\/dd\/yyyy\#") & _
" And NameOfTableOrQuery.FieldName <=" & _
Format(Forms!NameOfForm!AnotherDateControlOnForm.Value,"\#mm\/dd\/yyyy\#") & "';"

strQDF = "_TempQuery_"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing

' Replace C:\MyFolderName\MyFileName.xls with the real path and filename for the
' EXCEL file that is to contain the exported data
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQDF,"C:\MyFolderName\MyFileName.xls"

dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing

Return to Top of Page

Return to EXCEL Main Page

Return to Home

Create a Query and Export multiple "filtered" versions of a Query (based on data in another table) to separate EXCEL files via TransferSpreadsheet (VBA)

Generic code to create a temporary query, get list of filtering values, and then loop through the list to filter various data and export each filtered query to separate EXCEL files. In this sample code, the employees assigned to each manager are exported to separate EXCEL files, one file for each manager.

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and field names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for each ManagerID
' so that the data can be exported -- the code assumes that the actual names
' of the managers are in a lookup table -- again, replace generic names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False

' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strMgr & Format(Now(), _
"ddMMMyyy_hhnn") & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

Return to Top of Page

Return to EXCEL Main Page

Return to Home

Create a Query and Export multiple "filtered" versions of a Query (based on data in another table) to separate Worksheets within one EXCEL file via TransferSpreadsheet (VBA)

Generic code to create a temporary query, get list of filtering values, and then loop through the list to filter various data and export each filtered query to separate EXCEL files. In this sample code, the employees assigned to each manager are exported to separate worksheets within the same EXCEL file, one worksheet for each manager.

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

' Replace PutEXCELFileNameHereWithoutdotxls with actual EXCEL
' filename without the .xls extension
' (for example, MyEXCELFileName, BUT NOT MyEXCELFileName.xls)
Const strFileName As String = "PutEXCELFileNameHereWithoutdotxls"

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and field names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of ManagerID values and create a query for each ManagerID
' so that the data can be exported -- the code assumes that the actual names
' of the managers are in a lookup table -- again, replace generic names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False

' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID, EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM EmployeesTable WHERE " & _
"ManagerID = " & rstMgr!ManagerID.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

' Replace C:\FolderName\ with actual path
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "C:\FolderName\" & strFileName & ".xls"
rstMgr.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

Return to Top of Page

Return to EXCEL Main Page

Return to Home

Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA)

Generic code to open a recordset for the data that are to be written into a worksheet in an EXCEL file (for this example, the EXCEL file must already exist, and the worksheet must already exist in the EXCEL file), and then to loop through the recordset and write each field's value into a cell in the worksheet, with each record being written into a separate row in the worksheet. The starting cell for the EXCEL worksheet is specified in the code; after that, the data are written into contiguous cells and rows. This code example uses "late binding" for the EXCEL automation.

Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean

blnEXCEL = False

' Replace True with False if you do not want the first row of
' the worksheet to be a header row (the names of the fields
' from the recordset)
blnHeaderRow = True

' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = True

' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file into which you will write the data
Set xlw = xlx.Workbooks.Open("C:\Filename.xls")

' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
' (note that the worksheet must already be in the EXCEL file)
Set xls = xlw.Worksheets("WorksheetName")

' Replace A1 with the cell reference into which the first data value
' is to be written
Set xlc = xls.Range("A1") ' this is the first cell into which data go

Set dbs = CurrentDb()

' Replace QueryOrTableName with the real name of the table or query
' whose data are to be written into the worksheet
Set rst = dbs.OpenRecordset("QueryOrTableName", dbOpenDynaset, dbReadOnly)

If rst.EOF = False And rst.BOF = False Then

rst.MoveFirst

If blnHeaderRow = True Then
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
Next lngColumn
Set xlc = xlc.Offset(1,0)
End If

' write data to worksheet
Do While rst.EOF = False
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Value
Next lngColumn
rst.MoveNext
Set xlc = xlc.Offset(1,0)
Loop

End If

rst.Close
Set rst = Nothing

dbs.Close
Set dbs = Nothing

' Close the EXCEL file while saving the file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.Close True ' close the EXCEL file and save the new data
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing

Return to Top of Page

Return to EXCEL Main Page

Return to Home

Write Data From a Recordset into an EXCEL Worksheet using EXCEL's CopyFromRecordset (VBA)

Generic code to open a recordset for the data that are to be written into a worksheet in an EXCEL file (for this example, the EXCEL file does not already exist), and then to use EXCEL's CopyFromRecordset method to copy the data from the recordset into the first worksheet in that EXCEL file, with each record being written into a separate row in the worksheet. The code allows for a header row to be created in the worksheet if this is desired. This code example uses "late binding" for the EXCEL automation.

Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strPathFileName As String, strWorksheetName As String
Dim strRecordsetDataSource As String
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean

blnEXCEL = False

' Replace C:\Filename.xls with the actual path and filename
' that will be used to save the new EXCEL file into which you
' will write the data
strPathFileName = "C:\Filename.xls"

' Replace QueryOrTableName with the real name of the table or query
' whose data are to be written into the worksheet
strRecordsetDataSource = "QueryOrTableName"

' Replace True with False if you do not want the first row of
' the worksheet to be a header row (the names of the fields
' from the recordset)
blnHeaderRow = True

' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = True

' Create a new EXCEL workbook
Set xlw = xlx.Workbooks.Add

' Rename the first worksheet in the EXCEL file to be the first 31
' characters of the string in the strRecordsetDataSource variable
Set xls = xlw.Worksheets(1)
xls.Name = Trim(Left(strRecordsetDataSource, 31))

' Replace A1 with the cell reference of the first cell into which the
' headers will be written (blnHeaderRow = True), or into which the data
' values will be written (blnHeaderRow = False)
Set xlc = xls.Range("A1")

Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset(strRecordsetDataSource, dbOpenDynaset, dbReadOnly)

If rst.EOF = False And rst.BOF = False Then
' Write the header row to worksheet
If blnHeaderRow = True Then
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
Next lngColumn
Set xlc = xlc.Offset(1,0)
End If

' copy the recordset's data to worksheet
xlc.CopyFromRecordset rst
End If

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

' Save and close the EXCEL file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.SaveAs strPathFileName
xlw.Close False
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing

Return to Top of Page

Return to EXCEL Main Page

Return to Home

Browse to a single EXCEL File and Export Data to that EXCEL File via TransferSpreadsheet (VBA)

Generic code to browse to a single EXCEL file, and then to export the data to that EXCEL file. This generic method uses the Windows API to browse to a single file the code for this API (which was written by Ken Getz) is located at The ACCESS Web ( www.mvps.org/access ).

First step is to paste all the Getz code (from http://www.mvps.org/access/api/api0001.htm ) into a new, regular module in your database. Be sure to give the module a unique name (i.e., it cannot have the same name as any other module, any other function, or any other subroutine in the database). Then use this generic code to allow the user to select the EXCEL file to which the data are to be exported.

Dim strPathFile As String
Dim strTable As String, strBrowseMsg As String
Dim strFilter As String, strInitialDirectory As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the EXCEL file:"

' Change C:\MyFolder\ to the path for the folder where the Browse
' window is to start (the initial directory). If you want to start in
' ACCESS' default folder, delete C:\MyFolder\ from the code line,
' leaving an empty string as the value being set as the initial
' directory
strInitialDirectory = "C:\MyFolder\"

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")

strPathFile = ahtCommonFileOpenSave(InitialDir:=strInitialDirectory, _
Filter:=strFilter, OpenFile:=False, _
DialogTitle:=strBrowseMsg, _
Flags:=ahtOFN_HIDEREADONLY)

If strPathFile = "" Then
MsgBox "No file was selected.", vbOK, "No Selection"
Exit Sub
End If

' Replace tablename with the real name of the table from which
' the data are to be exported
strTable = "tablename"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile

Return to Top of Page

Return to EXCEL Main Page

Return to Home

Browse to a single Folder and Export Data to a New EXCEL File in that Folder via TransferSpreadsheet (VBA)

Generic code to browse to a single folder, and then to import the data from the first (or only) worksheet in all EXCEL files that are located within that folder. All of the EXCEL files' worksheets must have the data in the same layout and format. This generic method uses the Windows API to browse to a single folder; the code for this API (which was written by Terry Kreft) is located at The ACCESS Web ( www.mvps.org/access ).

First step is to paste all the Kreft code (from http://www.mvps.org/access/api/api0002.htm ) into a new, regular module in your database. Be sure to give the module a unique name (i.e., it cannot have the same name as any other module, any other function, or any other subroutine in the database). Then use this generic code to allow the user to select the folder in which the EXCEL files are located.

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Select the folder where the new EXCEL file will be created:"

strPath = BrowseFolder(strBrowseMsg)

If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

' Replace filename.xls with the real name of the EXCEL file
' that is to be created and into which the data are to be
' exported
strFile = "filename.xls"

' Replace tablename with the real name of the table from which
' the data are to be exported
strTable = "tablename"

strPathFile = strPath & "\" & strFile

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile

Return to Top of Page

Return to EXCEL Main Page

Return to Home

Using the Range Argument of TransferSpreadsheet when Exporting Data to an EXCEL File (VBA)

After I read many posts in the newgroups about using the Range argument for exporting queries/tables to EXCEL file, I decided to do some testing to figure out what actually works and what doesn't work when using this argument (NOTE that the use of the Range argument for exports is an UNDOCUMENTED feature in ACCESS).

Here are the results of my tests for your information and entertainment. My tests were done with ACCESS 2003 and EXCEL 2003.

EXCEL FILE DOES NOT ALREADY EXIST
---------------------------------------------------------------

If the EXCEL file will be created by TransferSpreadsheet, the Range argument
can be used to create a range in the new file that describes the cells that
contain the exported data on the worksheet. This Range argument also is used
to name the worksheet onto which the exported data are written. This
overrides the normal operation of TransferSpreadsheet, which is to name the
worksheet using the name of the table or query being exported. For example,
this action:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"QueryName","C:\Test.xls",, "MyName"

will create the file "C:\Test.xls" and the data will be written onto the
first worksheet, which will be named MyName (the Worksheet.Name property,
not the Worksheet.CodeName property); and the cells into which the data are
written will be a Range named MyName in the new file. This range will
include the field names that are exported as the first row of data, and the
range will begin in cell A1.



EXCEL FILE ALREADY EXISTS
------------------------------------------------

The Range argument can be used to identify the actual Range into which the
exported data are written. TransferSpreadsheet ignores worksheet names when
looking for the Range in the workbook file. It looks specifically for a
defined Range of cells.

However, it is in this situation where I believe many posters have run into
problems with this undocumented feature.

1) If the Range exists (cell range, that is) AND if that range encompasses
more than a single cell (at least two cells), the data are exported to that
range of cells. If the number of records and/or fields are more or fewer
than the "size" of the range (number of rows and columns), the data are
correctly exported and the Range is redefined to match the size of the
exported data in terms of width and depth of the range (number of rows and
number of columns). Note that any formatting in the cells within this range
is retained (e.g., Bold, Highlight color, font color, etc.).


2) If the Range does not exist in the workbook file, TransferSpreadsheet
creates a new worksheet, names it with the Range argument value, writes the
data onto that worksheet, and creates a new Range (also named with the Range
argument value) to define the cells that contain the exported data. If a
worksheet with the same name as what is in the Range argument already exists
in the workbook file, the new worksheet that is created is named using
standard EXCEL process, namely, the Range argument name followed by a 1.
Thus, if I use MyName as the Range argument and export to an existing file,
I can get one of the following results:

a) File already contains a worksheet named MyName but does not
contain a Range named MyName: A new worksheet named MyName1 is created, the
data are written onto that worksheet, and a new Range named MyName is
defined for the cells that received those exported data.

b) File does not contain a worksheet named MyName and does not
contain a Range named MyName: A new worksheet named MyName is created, the
data are written onto that worksheet, and a new Range named MyName is
defined for the cells that received those exported data.


3) If the Range exists (cell range, that is) AND if the Range consists of
a single cell (e.g., A1), then strange things happen -- note that it doesn't
matter if the Range starts in cell A1 or not. And because of these strange
things, this is where the feature is unusable for exporting. I haven't
defined exact "rules" to describe what happens (although it appears that how
far the range is moved appears to be "the original row number plus 93"
columns (if the Range was originally in column A), but here are my
observations in this situation (I won't guarantee that you won't see
different behaviors):

a) If the worksheet name is the same name as the Range name, and
the Range begins in cell A1, the exported data are written to the worksheet
that contains the Range specified in the TransferSpreadsheet action, and
these data begin at cell A1 (with the field names row) -- BUT the existing
range is moved to cell CQ1 (94 columns to the right), and there is no Range
created for the cells that contain the exported data. Any further attempt to
export to this worksheet using the same Range argument generates an error
because the "move" of the range will extend beyond the column limit of the
worksheet.

b) If the worksheet name is the same name as the Range name, and
the Range begins in cell A5, the exported data are written to the worksheet
that contains the Range specified in the TransferSpreadsheet action, and
these data begin at cell E5 (with the field names row) -- BUT the existing
range is moved to cell CU5 (98 columns to the right), and there is no Range
created for the cells that contain the exported data. Any further attempt to
export to this worksheet using the same Range argument generates an error
because the "move" of the range will extend beyond the column limit of the
worksheet.

c) If the worksheet name is not the same as the Range name, and
the Range begins in cell A1, the exported data are written to a new
worksheet that is named the same as the Range argument value, and the
existing Range is then moved to cell IV1 (the last column in the sheet) on
that new worksheet, and there is no Range created for the cells that contain
the exported data.

http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

Posting Levels in Dynamics GP – A Review

I have been getting questions from many of my clients and network on the various levels of postings to the General Ledger accounts that are available in Dynamics GP. Hence I decided to provide some information on the same to all the folks in this community.

The level of posting to the GL accounts is determined from couple of setups working hand in hand in Dynamics GP which I will elaborate below. The initial setup is to define the level of postings for various accounts in the Account Maintenance window from Cards >> Financials >> Accounts as illustrated below.

We can specify the levels of posting from the various series into the General Ledger module. The various options available for posting levels are

• Detail
• Summary

Once this has been setup, we need to define the level of posting for various transactions in various modules in the Posting Setup window from Microsoft Dynamics GP >> Tools >> Setup >> Posting >> Posting as illustrated below.

So for the purpose of this case study, I created a couple of receivable invoices with the distributions explained below and saved them into a batch called RECVINV.

Invoice #1

Invoice Number	Account Number	Type	Debit (DR)	Credit (CR)
INV0001	000-1200-00	RECV	$500	-
INV0001	000-4100-00	SALES	-	$500

Invoice #2

Invoice Number	Account Number	Type	Debit (DR)	Credit (CR)
INV0001	000-1200-00	RECV	$700	-
INV0001	000-4100-00	SALES	-	$700

And I have setup the account 000-1200-00 to post at a Summary Level in the Sales Series in the Account Maintenance window, whereas I have setup the account 000-4100-00 to post in detail in the Sales Series in the Account Maintenance window.

Now, in the Posting Setup window, if we select the option to create one journal entry per transaction, posting will always be done at a detailed level (irrespective of the setting specified in the Account Maintenance window) (i.e.) There will be a one-to-one match between the distribution lines in the journal entry and the distribution that we had noted in the transaction posted in the sub ledgers.

So when the above batch RECVINV is posted in the Receivables module, there will be 2 journal entries created with the distributions explained below.

Journal Entry #1

Journal Entry	Account Number	Debit (DR)	Credit (CR)
12345	000-1200-00	$500	-
12345	000-4100-00	-	$500

Journal Entry #2

Journal Entry	Account Number	Debit (DR)	Credit (CR)
12346	000-1200-00	$700	-
12346	000-4100-00	-	$700

However in the above window, if we specify the option to create a journal entry per batch, we have two levels of roll ups that are available when journal entries are created when the sub ledger transactions are posted.

If the “Use Account Settings” option is unchecked, then when a batch of transactions is posted from the sub ledger module, the system creates one journal entry for all transactions posted in the sub ledger batch. However, there is no roll-up done at the account level, even though the accounts have been setup to post at summary level in the Account Maintenance window.

So in the same case study example above, if the batch was posted in the Receivables module, there will be one journal entry created with the distributions illustrated below.

Journal Entry #1

Journal Entry	Account Number	Debit (DR)	Credit (CR)
12345	000-1200-00	$500	-
12345	000-4100-00	-	$500
12345	000-1200-00	$700	-
12345	000-4100-00	-	$700

If the “Use Account Settings” option is checked, then when a batch of transactions is posted from the sub ledger module, the system creates one journal entry for all transactions posted in the sub ledger batch and the distribution amounts are rolled based on the posting levels for the accounts that are defined in the Account Maintenance window.

So in the same case study example above, if the batch was posted in the Receivables module, there will be one journal entry created with the distributions illustrated below.

Journal Entry #1

Journal Entry	Account Number	Debit (DR)	Credit (CR)
12345	000-1200-00	$1200	-
12345	000-4100-00	-	$500
12345	000-4100-00	-	$700

Note: Keep in mind that the various levels of postings will also be in effect only when we perform a batch posting in the sub ledger. If the posting is done at a transaction level, the system will always post in detail to the General Ledger (irrespective if the settings in the Account Maintenance window and the Posting Setup window for the specific sub ledger transaction).

Hope this article provides some insight into the General Ledger posting levels that are available in Dynamics GP.

http://cvakumar.com/msdynamics/2010/05/16/posting-levels-in-dynamics-gp-a-review/

Maintaining Data Integrity between Sub Ledgers and General Ledger

I have been involved in the process of reconciliation between the sub ledgers and the general ledger at various clients and there has been various scenarios in which there has been a break between the sub ledgers and the general ledger balances. A few key scenarios are quoted below

1. A transaction posted in the sub ledgers do not have a corresponding transaction in the general ledger.
2. A transaction posted in the sub ledger is backed out/corrected at the general ledger level.
3. Manual posting to the sub ledger control accounts in the General Ledger.

In this article, I am going to provide some tips to avoid any of the above situations and ensure that there is data integrity between the sub ledgers and the general ledger. This will ensure that the periodic audits done in the system proceeds in a smooth manner to a great extent.

The first two errors listed above can be prevented by disabling the following options in the General Ledger setup window from Microsoft Dynamics GP >> Tools >> Setup >> Financials >> General Ledger.

By unmarking the option “Deletion of Saved Transactions”, a journal entry batch that is created when a sub ledger transaction is posted, cannot be deleted at the General Ledger level. This prevents any deletion of a journal entry that was created from a sub ledger transaction. Note that if we have enabled “Post Through General Ledger” in the posting setup, this would not make a big difference, since whenever a transaction is posted in the sub ledger, the corresponding general ledger transactions get automatically posted as well.

By unmarking the option “Voiding/Correcting of Subsidiary Transactions”, the system will not allow us to void a journal entry batch that is created when a sub ledger transaction is posted. This prevents any voiding of a journal entry that was created from a sub ledger transaction. that if we have enabled “Post Through General Ledger” in the posting setup, this would not make a big difference, since whenever a transaction is posted in the sub ledger, the corresponding general ledger transactions get automatically posted as well. Further, this option will not allow the user to back-out (or) back-out and correct a journal entry that was posted for a sub ledger transaction.

The third error listed above can be eliminated by disallowing account entry for all the sub ledger control accounts like the GL accounts for Accounts Receivable, Accounts Payable and Inventory accounts, in the Account Maintenance window from Cards >> Financial >> Accounts, as shown below. We need to unmark the option “Allow Account Entry” for all the control accounts needed.

Unmarking this option for the key control accounts, will prevent the user from picking up this account in any transaction entry windows and will prompt the message as shown below.

This option will prevent any direct posting into the sub ledger control accounts in the General Ledger. All postings will happen only from the sub ledger module.

These options mentioned above are some key setups that we can enable in GP to minimize the data integrity issues between the sub ledgers and the general ledger which consumes precious reconciliation time which is spent by many resources during the key audit time.

http://cvakumar.com/msdynamics/2010/05/16/maintaining-data-integrity-between-sub-ledgers-and-general-ledger/

How to Hammer Out Deal Terms Like Warren Buffett

In the early stages of selling your business, you're better off taking cues from the Oracle of Omaha and keeping the lawyers away from the negotiations.

I once went to a Broadway play starring James Belushi. I had good seats, so Belushi was only a few feet in front of me. I remember how captivating it was to be that close to someone famous. I became engrossed in the show and felt as though Belushi was talking directly to me. I lost touch with time and my surroundings. I forgot about all of the work behind the scenes—the lighting, staging, music—and just sat there and enjoyed Belushi’s performance.

Selling your business should work like a good play: You should put your best, most engaging actors onstage and keep the people handling the messier details of the deal behind the curtain. In my opinion, the lawyers should remain offstage until you agree to a set of business terms with the acquirer.

It's tempting to let your lawyer be the “bad cop” in the early stages of negotiations, pounding on the table, demanding better terms. However, coming to an agreement on the core business terms before the lawyers get involved will ensure a positive foundation that will serve you well when the legal teams inevitably reach loggerheads on some of the finer details.

One of the biggest hurdles in coming to an agreement on the core business terms is drafting a simple letter that outlines the deal in language both parties understand. It's hard not to get mired in the details and document every conceivable possibility. If you get bogged down, take inspiration from Warren Buffett.

Buffett is the master of writing about business in plain English. When Buffett tries to buy a company, he approaches the CEO with a short, plain-spoken letter before any of the deal-makers get involved. Read Buffett’s annual letter to Berkshire Hathaway shareholders if you find your correspondence with a potential acquirer becoming too technical.

Here are a few things to work out with an acquirer (in plain English) before you get the lawyers involved:

• The cash on closing (what you get the day the deal closes)

• The earn-out (or vendor take-back) you are agreeing to, along with a clear understanding of what budget and resources you have at your disposal to meet the goals you’re signing up for

• What happens if you decide to leave or the buyer asks you to leave before the earn-out or transition period is over

• The working capital in the business at closing (how much money needs to be left in the company the day it changes hands)

• Your salary and benefits when you become an employee of the acquirer

• The degree of decision-making autonomy you’ll have as an employee during the earn-out or transition period (e.g., Do you get to decide what technology platform to use? Do you control decisions around marketing and selling? How about hiring and firing?)

If you can agree to a set of business terms, you’ll be building a good working relationship with your acquirer, which will be important once you become a division of their business. Besides, if you can’t come to an agreement on the basic business terms with a buyer, no two lawyers will be able to agree to the legal terms, which are infinitely more complex.

So follow Buffett’s lead and get the basics down in plain English before you invite the lawyers onto the stage.

http://www.inc.com/articles/2010/04/negotiating-basic-deal-terms.html?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+inc%2Fheadlines+%28Inc.com+Headlines%29&utm_content=My+Yahoo

5 Ways to Ensure Mediocrity in Your Organization

The recession is no excuse for ignoring, misusing, or demeaning talent. But hey, if that's what you really want to do, follow these suggestions.

The last time I checked, the U.S. led the world in productivity per employee. That's the good news. The bad news is that much, if not all, of that boost in productivity has come on the backs of workers, especially salaried types viewed by too many management teams as infinitely elastic resources. As one management consultant told me: "The average company takes better care of its copiers than it does its talent."

Many chief executives use the tough competitive environment as a handy excuse to put off salary increases, tighten the screws on performance, and generally drop any pretense of creating a human-centered workplace. But the tough-economy picture has two sides. Only those companies that make the effort to keep their employees productive by treating them decently can expect to see continued productivity gains. Much of the workforce has tuned out, waiting for a more welcoming job market to make career moves. Those organizations that haven't wavered on their commitments to flexibility, recognition of talent, and transparent leadership will keep A-list players on board as the job market improves. Their competitors may be wishing they'd paid a little more attention to employee TLC as employees start peeling off for greener pastures.

Here are five of the most insulting leadership practices, the ones that virtually guarantee a business will end up with the most self-esteem challenged, optionless team members when the dust settles.

1. If you desire a mediocre workforce, make sure your employees know you don't trust them.

Nothing spells "You're dirt to us" like a corporate culture that screams, "We don't trust you as far as we can throw you." I refer to company policies that require employees to clock in and out for lunch or software that tracks every keystroke and change of URL in case a molecule of nonwork-related activity squeaks into the workday. When employees know they're not trusted, they become experts at "presenteeism"—the physical appearance of working, without anything getting done. Congratulations! Your inability to trust the very people you've selected to join your team has cost you their energy, goodwill, and great ideas.

2. If you want to drive talented people away, don't tell them when they shine.

Fear of a high-self-esteem employee is prevalent among average-grade corporate leadership teams. Look how hard it is for so many managers to say, "Hey Bob, you did a great job today." Maybe it's a fear that the bit of praise will be met with a request for a pay raise. Maybe it's the fear that acknowledging performance will somehow make the manager look weak. Whatever the reason for silence, leaders who can't say, "Thanks—good going!" can plan on bidding farewell to their most able team members in short order.

3. If you prefer a team of C-list players, keep employees in the dark.

Sharp knowledge workers want to know what's going on in their organizations, beyond their departmental silos. They want some visibility into the company's plans and their own career mobility. Leaders who can't stand to shine a light on their firms' goals, strategies, and systems are all but guaranteed to spend a lot of money running ads on Monster.com. Marketable top performers want a seat at the table and won't stand for being left in the dark without the information they need to do their jobs well.

4. If you value docility over ingenuity, shout it from the rooftops.

I heard from a new MBA who had joined a global manufacturer. "They told me during my first week that I need a manager's signature to organize a meeting," he recalled. "They said I'm too low-level to call a meeting on my own, because unauthorized meetings of nonmanagers are against company policy." How fearful of its employees would a leadership team have to be to forbid people to gather together to solve problems? The most desirable value creators won't stick around to be treated like children. They'll hop a bus to the first employer who tells them, "We're hiring you for your talent—now go do something brilliant."

5. If you fear an empowered workforce more than you fear the competition, squash any sign of individualism.

When you go to college, you learn about Economic Man, but in the corporate workplace we see that real people don't always act rationally. Lots of individual managers and plenty of leadership teams fear nothing more than the idea that a self-directed employee might buck authority. That's equivalent to shaking the organizational power structure to its foundation, possibly a fate worse than death. Leaders who want the most docile, sheep-like employees more than the smartest and ablest ones create systems to keep the C players on board and drive the A team out the door. They do it by instituting reams of pointless rules, upbraiding people for miniscule infractions ("What? Twenty minutes late? Sure you worked here until midnight last night, but starting time is starting time.") and generally replacing trust with fear throughout their organizations. Companies that operate in fear mode will never deliver great products and services to the marketplace. Their efforts will be hamstrung by their talent-repelling management practices.

How long will it take these enterprises to figure out they're shooting themselves in the foot? It doesn't matter—you'll be long gone by then.

http://finance.yahoo.com/career-work/article/109519/5-ways-to-ensure-mediocrity-in-your-organization?mod=career-leadership

SQL Server APPLY operator

If you would love two scoops of yummy SQL Server query goodness, the APPLY operator is for you.

As I've said before, rarely a day goes by when I don't say "D'oh!" in surprise (and embarrassment) at being unaware of a tool or technology that I probably should have known about, but just didn't.

The SQL Server APPLY operator is one of those gems that I should have known about 5 years ago. I really need to start buying those "What's New In SQL Server" books, and actually reading them.

Imagine this Microsoft Dynamics GP scenario: You need to query vendors and include the vendor's PRIMARY address information, as well as their REMIT TO address information. But it's not quite that simple (it never is).

You find that the client has inconsistent vendor address IDs in their database. Sometimes they just use the address ID of "REMIT", sometimes "REMIT TO" and sometimes "REMIT ADDRESS", among others. And naturally, there are some vendors who don't have a separate REMIT TO address at all. In theory, some vendors may have two or more remit addresses, so you have to handle that situation as well--in which case you only want the TOP 1 remit address record.

While trying to figure out how to write such a query, I came across this article that discussed the OUTER APPLY operator.

Think of it as an OUTER JOIN, but without needing the ON keyword and subsequent matching columns from the two tables. It's also a bit like a subquery, but with the flexibility of a JOIN.

In short, it will make your inner SQL geek salivate.

Here is an example query that retrieves basic vendor information, along with the TOP 1 primary and remit to address records, allowing me to reference the fields in my query through a derived table.

Note that I used the OUTER APPLY version, since I want to return the vendor information even if there is no primary or remit to address. If you only want to return records that have a match in your apply operation, you can use CROSS APPLY, which works like an INNER JOIN. (hence the two scoops of yummy goodness)


SELECT
RTRIM(v.VENDORID) AS VENDORID,
RTRIM(v.VENDNAME) AS VENDNAME,
RTRIM(ISNULL(vpa.VNDCNTCT, '')) AS CONTACT,
RTRIM(ISNULL(vpa.ADDRESS1, '')) AS ADDRESS1,
RTRIM(ISNULL(vpa.ADDRESS2, '')) AS ADDRESS2,
RTRIM(ISNULL(vpa.CITY, '')) AS CITY,
RTRIM(ISNULL(vpa.STATE, '')) AS STATE,
RTRIM(ISNULL(vpa.ZIPCODE, '')) AS ZIPCODE,
RTRIM(ISNULL(vra.ADDRESS1, '')) AS REMITADDRESS1,
RTRIM(ISNULL(vra.ADDRESS2, '')) AS REMITADDRESS2,
RTRIM(ISNULL(vra.CITY, '')) AS REMITCITY,
RTRIM(ISNULL(vra.STATE, '')) AS REMITSTATE,
RTRIM(ISNULL(vra.ZIPCODE, '')) AS REMITZIPCODE,
RTRIM(ISNULL(vpa.PHNUMBR1, '')) AS PHONE1,
RTRIM(ISNULL(vpa.PHNUMBR2, '')) AS PHONE2,
RTRIM(ISNULL(vpa.FAXNUMBR, '')) AS FAX
FROM PM00200 v
OUTER APPLY (SELECT TOP 1 * FROM PM00300 vpa WHERE VENDORID = v.VENDORID AND ADRSCODE LIKE '%PRIMARY%') AS vpa
OUTER APPLY (SELECT TOP 1 * FROM PM00300 vpa WHERE VENDORID = v.VENDORID AND ADRSCODE LIKE '%REMIT%') AS vra


I told you it was tasty! Bon Appétit!


Here are a few more articles on the topic:

http://msdn.microsoft.com/en-us/library/ms175156.aspx

http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

http://decipherinfosys.wordpress.com/2007/10/08/apply-operator-in-sql-server-2005/

FROM: http://dynamicsgpland.blogspot.com/2010/05/sql-server-apply-operator.html

What financial data business owners neeed to know everyday

Yesterday Sales
Yesterday Cashflow
Last 30 Day Sales
Last 30 Day Cash Flow
Last 30 Day Top Selling Items
Last 365 Day Sales
Last 365 Day Cash Flow
Low Inventory Items