Business Solutions: Christmas Letter - Excel and Access

        by James D. Duncan, CPA    December 2004

 

            It is that time again:  the annual Christmas family letter.  You have the addresses in Excel 2003 and we can add a quick report in Access 2003 to send the family letter to everyone on the list.  The worksheet can be simple or complicated; we only need the basic information.  The example below shows what I have used.

 

 

Notice I put field names on the top of each column.  This makes it easier when we link the Excel file to Access.

            Create a new Access database or you can add this to an existing one.  First thing, we will link the Excel sheet to Access through the tables.  This is a simple task.  In the tables objects, select new, select link, and click on the OK button.  Now, find the Excel file that has your addresses in it, select it, and click on the Link button.

 

 

The link spreadsheet wizard will then walk you through the process.  On screen two select the box that says “First Row Contains Column Headings.”  Next name the table link something that has meaning to you.  I used AddressList.  In the tables screen Access now shows an Excel table link named AddressList.  Nothing to it and when you change the Excel sheet, Access knows it also.

            Now for a down and dirty (computer terms for “I was really rushed and will tidy up later..”(:>)…or not ) report that gets us the Christmas letter.  Click on reports, select new, use the default design view and select the table AddressList. 

 

Now we have the report ready to put together.

 Double click on the AddressList header bar, between the heading AddressList and the close “X”.  Then click on FirstName, drag and drop the complete field list to the detail section of the report.  Highlight the descriptions to the left of the field names and press delete to remove them.  Then arrange the fields to where you want them.  This is not the most effective method, but will work until we have time to tidy up. 

            Now click in the page header area and add a label for your name.  Then do this 2 more times for your full address.  I also added a label for “Christmas 2004” and a text box with the label removed with =now() in the control source to give me the date and time.  Select how you want the date to print by selecting the Format property and choosing how you want the date to appear.

Now to add the text of the letter.  Add a label several lines under city.  Then start typing your letter.  You will have to add a label for each paragraph you add. Not the most elegant solution but this is fast and furious.

             Now one last detail, we want each letter on a separate page. This requires a page break after each person.  Right click on the Detail band.  Then select “After Section” in the Force New Page property. 

Now let’s preview our letter.  Click on the print preview icon and see what we got.

Not pretty but usable.  To make this nicer we would concatenate the first and last name with a space in between. Change the Name and add the two fields and a space between like the screen below.  We would do this with city, state and zip also.   

            We could move the address to fit a window envelope if you have a large number to do.  You now have the basics for the Christmas letter.  And yes, a mail merge in Word is nicer….if you know how.  But isn’t that always the case…. 

            Have a wonderful Christmas.  In the coming year take time out to volunteer for an organization of your choosing.  Get off your duff, turn off the TV and SHOW the kids with your actions how to help the community.  You will meet just the most wonderful people.  Keep pounding out the code, solving those problems, and trying different approaches.  Just maybe you will learn a new trick.    

James is a member of the CCOKC and an expert in Access and Excel. Click here to reach James.