Northwind Database Examples Microsoft provides a sample database called Northwind. This database is an excellent place to test and learn without worrying about the perils of testing on live databases. Following are some exercises to perform in Northwind I: Downloading the Sample Northwind Database II: Summing Currency Fields in Access III: Building Crosstab Queries  IV: Converting Northwind Database for Use in Your Office Downloading the Sample Northwind Database Start Microsoft Access Click the Help menu, choose Sample Databases. A side menu will appear. Choose Northwind Sample Database If Northwind was already installed, it will open immediately. Otherwise a dialog box will appear called: Installing Components for Microsoft Office Access. Simply work through this wizard to install your sample copy of the Northwind Sample Database. Note: You may be asked to insert the Microsoft Office CD(s) during the installation process. Back to Top   Summing Currency Fields in Access   Access queries can be used to calculate currency totals. Followings is an example from the Northwind Sample Database, in which sales are grouped by Company Name, Product Name and Month.      In order to run this test query in the Northwind Sample Database use the following steps: Open your Northwind Sample Database. Click the Query tab Click New Choose Design View and click OK - A query window will open - with a Show Table dialog box. Add the following tables to your query grid. Orders Order Details Products Suppliers Once you've added the above tables and closed your Show Table dialog box, you'll need to maximize your grid screen and add the following fields to the query grid. This can be done by double-clicking on the needed field in the appropriate table. Fields needed are: CompanyName from the Suppliers table ProductName from the Products table UnitPrice from the Order Details table Quantity from the Order Details table. OrderDate from the Orders table If you were to run your query now you would see ALL the companies for EVERY date in the database. In order to narrow your record set down to a manageable size we will limit the record set by Company Name and Order Date. In the Criteria row of Company Name type "New England Seafood Cannery". Make sure and use the quotation marks. Also, in the Sort row of the query grid, choose Ascending In the Criteria row of OrderDate type Between #01/01/1997# And #12/31/1997#  Also, in the Show row of the query grid, unclick the show button.   Now, when you run your query, you will only see records from New England Seafood Cannery for all of 1997. But, you will see one record for every sale throughout the year. In order to total all your sales you will need to use the Totals capability. Simply go back into design mode of your query grid. Click the View menu Choose Totals from the View menu. (A new Total row will appear in your query grid) Group the query by month as shown in the example below. See the SaleMonth column. This column takes the OrderDate and converts it to a Month value. Make sure to sort this column next to ProductName in ascending order. Now go to the Quantity column in your query grid. Perform the following steps: In the Field row type: QtySold: Quantity In the Total row type: Sum This column should now appear as: In order to calculate TotalSales, perform the following steps: Tab to the first empty column past QtySold, in the Field row type the following: TotalSales: Sum([Quantity])*[Order Details]![UnitPrice] This will calculation will Sum the Quantity field and multiple it against the UnityPrice in the OrderDetails table. For the calculation to work - go to the Total row and type Expression Your new column should look like:   When you are finished your query grid should look like the following:          Pay particular attention to the order in which the field columns are displayed and sorted. Since queries sort in order from left to right, it is very important to arrange your columns/fields appropriately. In this example CompanyName, ProductName and SaleMonth are all sorting in Ascending order (see the sort row in the above example). Because CompanyName is arranged as the first column then all the ProductNames will sort secondary to company and your results will display Company first - then Product. Lastly the query will sort sales by month within Product and company.   Back To Top     Building Crosstab Queries Crosstab queries are a common tool used to help rearrange data for financial analysis. The queries take data and rearrange it from the format in Diagram 1 to the format in Diagram 2. Diagram 1   Diagram 2     In order to build a test Crosstab query in the Northwind Sample Database use the following steps:   Open your Northwind Sample Database. Click the Query tab Click New Choose Design View and click OK - A query window will open - with a Show Table dialog box. Add the following tables to your query grid. Customers Orders Order Details Products Once you've added the above tables and closed your Show Table dialog box, you'll need to maximize your grid screen and add the following fields to the query grid. This can be done by double-clicking on the needed field in the appropriate table. Fields needed are: CompanyName from the Customerstable ProductName from the Products table In order to get a total sold per product - you will need to calculate off the UnitPrice and Quantity fields to arrive at a total sold amount. In the query grid column just to the right of ProductName enter the following in the Field row: Sold: [Order Details]![UnitPrice]*[Quantity] This function will multiple the UnitPrice x Quantity - for a total sold. After typing the function in the field row go down to the Total row of the same column and choose Sum from the list. Your query grid should now look like Diagram 3.   Diagram 3   Please note the following details: Column 1 - CompanyName sorts in ascending order. See the Sort row on the query grid. Column 3 - Sold calculates [unitPrice] x [quantity] to arrive at a toal. Column 3 also uses the Sum function in the Total row If you were to run your query now the data would appear as shown in Diagram 4   Diagram 4   In order to re-arrange the data in crosstab format, simply go back to design view and follow these instructions. Select the Query menu Choose the Crosstab Query option This action will add a new Crosstab Row to your query grid. See Diagram 5 to set up the options in your new crosstab row. Crosstab options should be chosen as follows: CompanyName Column - Choose crosstab setting Row Heading ProductName Column - Choose crosstab setting Column Heading Sold Column - Choose crosstab setting Value Diagram 5   Now run your query - the results should appear as shown in diagram 6.   Diagram 6   Back To Top     Converting Northwind for Use in Your Office

1. I recently downloaded the Northwind Sample Database and attempting to convert and modify the Order Entry, Inventory and Purchasing tables and forms for my business. I know there will be some stumbling along the way that I will not be able to complete. I have already modified some fields and added some additional table and update the Relationships.  Just how will you be able to help me? 

You ask how I can help you....

 

Clients use my services in many ways. Many times my services are used to develop Access databases from the ground up. Also, businesses use my services to maintain and enhance existing Access databases.

 

There are a few clients, however, who have enough interest in and comfort level with Access to do much of the development work themselves. These clients prefer to use my services as a tutor. Tutorial sessions are interactive - remote - sessions. We work on the client's database and focus on client priorities.

 

For instance you wrote: 

I recently downloaded the Northwind Sample Database and attempting to convert and modify the Order Entry, Inventory and Purchasing tables and forms for my business. I know there will be some stumbling along the way that I will not be able to complete.

You could use my services to: 

1. Do the development work for you .... or ...
2. Tutor you as you work through the development process yourself.

Either way - we would work closely together. I follow a collaborative approach to database work. This means that work sessions are no longer than three hours and clients are directly involved in the work sessions from setting priorities to do the testing.

 

And in the case of tutoring clients, the client is at the keyboard doing development work as I teach them Microsoft Access. Of course, during any given tutorial, control of the keyboard will move between the client and myself as I instruct. But, the client is very involved, learning how to write their own queries, reports, etc... This ensures a sense of ownership once the database is completed; this ownership allows clients to do normal database maintenance themselves without having to call me every time there is need for a new report or query.