# DbQuery/Step-by-Step querying Microsoft Access

This mini-tutorial takes you through a step-by-step process of connecting to and querying data from a Microsoft Access database using DbQuery.

## Find the Database

Microsoft Access 2000 and later includes a sample database called Northwind that we will use for this exercise. First, locate this database on your computer and view it in Access:

• Start Microsoft Access
• Select File → Open..., and find the Northwind.mdb file, usually at:
C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb
• If you don't have a local copy, the database can be downloaded from Microsoft Templates at this link
• After opening in Access, it may display an introduce dialog. Click through this and examine the list of Tables. Open the Customers table. We'll query this table from Analytica.

## Set up a Data Source

Here you will set up a DSN for the Northwind database, which will allow you to connect to it by name from DbQuery. To do this, follow these steps:

• Start → Control Panel → Administrative Tools → Data Sources (ODBC)
• Click the System DSN tab.
• Press Add..., select Microsoft Access Driver (*.mdb)
• Enter:
Data Source Name: Northwind
Description: Sample database from Microsoft Access
• Press the Select... button, and navigate to the Northwind.mdb database file identified earlier. Select it.
• Press OK to complete the Data Sources dialog.

Now we have a data source named Northwind that we can connect to using DbQuery. With this datasource, we can query any of the tables in the Northwind database.

## Set up the Analytica model

Now you will create the query in an Analytica model.

• Start Analytica
• Drag an index node to the diagram, title it Customer Number
• Set its definition to:
DbQuery("DSN=Northwind", sql: "select * from Customers")
• Evaluate it.

At this point, if you've done things correctly, you'll see an index from 1 to 91 (or however many records are present in your own Customers table).

• Drag another index node to the diagram, name it Customer Field, define it as:
DbLabels(Customer_Number)
• Drag a variable node to the diagram, name it Customer Table, and define it as:
DbTable(Customer_Number, Customer_Field)
• Evaluate Customer_Table

The data is now imported (as shown below).

## Reindexing the Table

For convenience, we may want to use the CustomerID column of the table as the row index. To do this, we'll re-index the data.

• Create a new index node, name it CustomerID, defined as:
CopyIndex(DbTable(Customer_Number,"CustomerID"))

This will now serve as our row index.

• Change the definition of Customer_Field to:
index tmp := DbLabels(Customer_Number) do subset(tmp <> "CustomerID")
• Change the definition of Customer_Table to
DbTable(Customer_Number, Customer_Field) [@Customer_Number = @CustomerID]
• Re-evaluate Customer_Table. You should see a two-D table having indexes: CustomerID and Customer_Fields