Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Improving sales reports at Northwind Traders #1. Tutorial by David Carter

by
3rd May 2007
Save content
Have you found this content useful? Use the button above to save it to your profile.

Northwind Traders run all their sales orders and stock control through an ageing accounts package written in Access. They can dramatically improve their sales and profitability reports by live-linking Excel direct to their Access database. Follow David Carter’s tutorial to see how.

[This tutorial uses the Northwind Traders database, a sample database for helping users to learn Microsoft Access. Our thanks to Microsoft for permission to use it in this tutorial. Northwind comes in a zipped file of 434kb. Click here to download a copy, then extract the file Northwind.mdb (.mdb = Access format = “Microsoft DataBase”). This tutorial assumes you have saved and extracted Northwind.mdb into a folder named c:\anyfolder\northwind]

1. Background to the tutorial
You are the boss of Northwind Traders Ltd. Your company is a distributor of exotic foods such as Aniseed Syrup, Camembert Pierrot cheese, Lakkalikööri, and Jack’s New England Clam Chowder.

You ship these delicacies to specialist food shops all over the world, famous names such as Alfreds Futterkiste in Berlin, Rattlesnake Canyon Grocery of Albuquerqe, and Wolski Zajazd in Warsaw.

Your sales order processing system
To handle the company’s sales orders you have an order processing and stock control program that has been specially written for you in Microsoft’s Access database. The staff use it each day to key in new customer orders, deliver and downdate the stocks, and to issue sales invoices.

Your admin staff are very happy with the Access package, but you, the MD, are less happy. The reports it provides are pretty poor; they don’t give you the information you really need in order to run the business. And you don’t know how the package works, so you are dependent on the staff to print off the reports for you.

Maybe if you bought a new accounts package, that would give you better reports? On the other hand, it would cost a lot of money, and cause unnecessary upheaval among your admin staff for whom the existing system works perfectly well. You're not sure what to do.

Answer: Live link to Excel!
If you have a problem with poor reporting from your accounts package, the answer is simple. There’s no need to go out and buy a new one, no need to suffer all that expense and upheaval. Instead, you need to improve the way you get data out of the package.

Specifically, you must “live link” your package directly to Microsoft Excel via Excel's Get External Data command. Get External Data works even with text or csv files, and EVERY package is capable of exporting data as text or csv.

Once you are live linked to Excel, there’s no more need for paper reports that come too late and in a format you don’t like. Instead, you will see all the orders on your own PC as they feed through to Excel automatically.

The power of Excel pivot tables will allow you to have pretty well all the reports you want. And to see them updated with the latest figures, all you have to do is to press a button.

Sounds too good to be true? Try this tutorial and find out!

2. View the Northwind system
Let’s start by looking at your Northwind Traders order processing package. (If you don’t have Access, you will have to skip this section and go onto section 3, Set up Northwind as a Data Source).

Start up Access. File – Open – c:\anyfolder\northwind\northwind.mdb.

The Northwind Traders logo appears (with the lighthouse). OK

The Main “Switchboard” menu screen appears. Go into Orders.

The Orders screen has a Bill-To and a Ship-To address at the top. The first order is from Alfreds Futterkiste of Berlin. They have ordered two products – 15 cartons of Laalaakorii and 6 of Aniseed Syrup.

Down the bottom you have the Fast Forward and Backward buttons. This is order number 1. There are 830 orders on the system.

This is the screen that your admin staff use to enter new customer orders onto the computer. With live linking established, any new order typed in here can also appear instantly on your own PC in Excel.

That’s the Northwind order processing system. Now we’ll switch back into Excel and establish the live link between Excel and Northwind.

3. Set up Northwind as a Data Source
Click on the Start button, then Settings - Control Panel.

Administrative Tools - Data Sources (ODBC). [This is for Windows XP. Otherwise, click on the ODBC32 icon.]

The ODBC Data Source Administrator box appears. You are looking at the User DSN tab. Change to the Drivers tab.

Drivers tab
This contains a list of drivers supplied by Microsoft. A driver will enable Excel to read any file in that format.

You can see that there is a driver entitled Microsoft Access.mdb. So Excel will be able to read the Access files. Good.

[Note: reading data from your own package]
If you want to read the data from your own accounts package, there has to be a driver for your package in this list. If there isn’t one here, check the folders for your package and see if there is a folder entitled ODBC. If there is, go in and see if there is a file called Setup.exe. Clicking on Setup.exe should automatically generate an ODBC driver and deposit it into this Drivers tab.]

Now change the tab to System DSN. Click onto Add.

System DSN tab
In the Create New Data Source box, select: Microsoft Access driver (*mdb)

The ODBC Microsoft Access Setup box appears. Type:

Data Source Name: Northwind Tutorial

Description : Northwind Tutorial

Then Database – Select.

In the middle of the Select Database box, in the Directories window, point to:

c:\anyfolder\northwind

In the left hand window, Northwind.mdb appears.

Highlight Northwind.mdb. Then: OK – OK

Northwind Tutorial is now in the list of System Data Sources. OK

Close down Administrative Tools and return to desktop.

4. Live link to Excel via Import External Data
Now start up Excel. Open a blank workbook. Click onto cell A1.

Select: Data – Import External Data – New Database Query
[Excel 2000 or earlier: Get External Data – Create Database Query].

In the Choose Data Source box, you see a list of data sources, including Northwind Tutorial. Highlight Northwind Tutorial and select it:

You now see the Query Wizard, Choose Columns box. Inside it are the data tables of the Northwind database, starting with Alphabetical List of Products.

5. A bit of theory - what are we looking for?
[This explains the reasons for what you are going to do in sections 6 and 7. If you just want to get on with the tutorial, you can go straight to section 6.]

In this tutorial we are going to design reports on Northwind’s sales orders.

When you want to find data that relates to sales orders, there are 4 essential tables you have to find. These four will hold 90% of the data you need. They are:

1. Sales order header
2. Sales order details
3. Customer
4. Product

Accounting vendors all design their data tables in different ways with different names. In the case of the Northwind database, the Sales order details table is for some reason split into two tables, so there are 5 data tables we need to look at. They are:

1. Orders
2. Order Details
3. Order Details Extended
4. Customers
5. Product

In addition, often the field names used by programmers in these tables are pretty meaningless. Usually you have to download the actual data to see exactly what each field contains.

In the next session we’ll take a look at the data in the 5 tables, and decide which fields we'll need to use from each one.

So we’ll end now by downloading the contents of all 5 files into Excel. Start with the Orders table.

6. Download the Orders data table into Excel
Scroll down from Alphabetical List of Products until you get to Orders. Highlight it with a single click [double click opens it up, which you don’t want].

Now click on the > chevron in the middle.

All the fields in the Orders table appear in the right hand box - OrderID, CustomerID, EmployeeID, OrderDate, etc.

Click on Next.

Query Wizard Filter data: choose Next

Query Wizard Sort Order: Sort by OrderID (first one). Change to Descending. Next.

Query Wizard Finish: Click Finish to Return Data to Microsoft Excel.

Import Data box. OK to accept =$A$1.

All the orders are imported into Excel, with the latest orders at the top (column A).

Go to the bottom of the list. There are about 830 lines – one per order. Return to the top of the list.

At bottom left, double click on Sheet1. Rename this worksheet Orders.

7. Download the four other tables
We need to download the other 4 tables. So, click into another worksheet. Then follow the Import Data procedure again to bring in:

2. Order Details
3. Order Details Extended
4. Customers
5. Product

Put each table into a separate worksheet, so that you end up with 1 workbook containing five worksheets.

When all 5 are downloaded, close down and Save. In the next session we’ll have a look at the contents of these data tables and select the fields we are going to use.

END OF SESSION

Replies (2)

Please login or register to join the discussion.

avatar
By panhiav
10th Jun 2011 02:59

Microsoft 2007

Hi, do  you have a tutorial for 2007? I only have the 2007 version, therefore i cannot download the zip file.

Thanks (0)
avatar
By amitkpattnaik
19th Jun 2012 08:44

Hi,

Order Details Extended  is missing in the.mdb file. Can you please help.

Cheers!

Amit

[email protected]

Thanks (0)