Access Query Magic

Creating a Count Query

On many websites, you'll see a page that lists items along with a total number of items that meet that criteria. Here is an example:

Beverages (12)

Condiments (12)

Confections (13)

Dairy Products (10)

Grains/Cereals (7)

Meat/Poultry (6)

Produce (5)

Seafood (12)

You'll often see this when you are listing items within a large database site. It's handy to let your site visitors know how much additional information is available under each category. It's also remarkably easy to do. All you need to learn is how to create a count query in Microsoft Access.

Using the Northwind database that comes with Access, open a new query and select design mode. You'll need to add a table. In this case add the Categories and the Products tables. Your Query window should look like the following:

Of course to make the query work, we also need to add some fields. Since we are looking to count the number of records in each category, drag the CategoryName field from the Categories table.

Now, drag the CategoryName field from the Categories table into the second column. You should see two columns with the exact same field value showing.

If you switch to datasheet view, you'll see two columns, both called Category Name, and both listing the same items.

Now we do the cool stuff. Switch back to design view, and select "Totals" from the View Menu.

This will add an additional row to the query in design mode. It will be labeled Total and by default will indicate "Group by". This is the default for normal queries.

On the first column in the Sort field, select ascending. This will sort your data alphabetically. In the second column, under the total row, select count from the drop down list. It should look like the following:

If you now run the query, you'll see a list of all your categories in the left column with a total count of how many items are contained within each category on the right.

Save the query with a name like qCategoryCount. You will then be able to reference it in the DRW just like any other table or query and display the total number of any of your category items. In the following example, I have created a Database Results Wizard that displays the Category Name followed by the CountOFCategoryName field contained within brackets.

I always recommend that most people to do the bulk of their data selection within Access. It is more robust, more flexible and doesn't require learning to use custom sql.