Database Lessons

Please consider supporting this site by purchasing items from the Book Store. Thanks for visiting!

Someone read the post from 2 days ago and took this to a slightly further point.

How can I list all the details, and also list subtotals by a subset of the itemID, and then a grand total. They were referring specifically to a report of the data.

Here is some fake data I created to illustrate.

itemid   cost
01142    $1.00
01222    $7.00
01333    $2.00
01623    $4.00
01777    $7.00
01997    $4.00
02456    $8.00
02556    $5.00
02666    $9.00
02716    $8.00
02796    $9.00
02916    $2.00
03455    $5.00
03485    $4.00
03555    $7.00
03675    $7.00
05666    $6.00
05886    $1.00

The report should look like this. (plus appropriate headers)

itemid   cost
01142    $1.00
01222    $7.00
01333    $2.00
01623    $4.00
01777    $7.00
01997    $4.00
01 subtotal = $25.00
02456    $8.00
02556    $5.00
02666    $9.00
02716    $8.00
02796    $9.00
02916    $2.00
02 subtotal = $41.00
03455    $5.00
03485    $4.00
03555    $7.00
03675    $7.00
03 subtotal = $23.00
05666    $6.00
05886    $1.00
05 subtotal = $7.00

grand total = $96.00

Here are the steps needed in Microsoft Access 2000. I am not putting in every small detail, as I believe you should think about what you are doing, and copying every tiny detail from me will not help you.

• start the report wizard, giving it the name of the table you are needing
• select the itemID and cost fields
• choose itemID as a grouping field
• click the Grouping Options button and choose a Grouping interval of “2 initial letters”
• no sorting is needed, but under Summary Options you want the Sum of the cost field
• pick a style and finish the report’s creation (Note: give it a proper name, like rptCosts, or similar)

The report is not quite what we wanted, but is close.

Next I went into the design view of the wizard created report and …

• deleted the “Summary for …” item from the itemID footer
• moved the “Left$ …” item from the itemID header to the itemID footer
• rearranged the objects in the itemID footer to better emulate the design I wanted
• went into the Sorting and Grouping section under the view menu
• changed the itemID Group Header to “No”
• continued to play with formatting until I had it the way I wanted it

See printouts, before and after changes, by clicking on the links in this sentence.

The key in my MS Access self-done education period was to use the wizard like we just did, and look at the design and especially properties of objects, and the Sorting and Grouping dialogue box. I experimented and discovered “stuff”.

Hopefully I can help you discover that “stuff” faster than I did, but in a way that still helps you “see” what is going on. Don’t ever depend solely on a recipe book. Discover the principles so that you can create your own recipes.

I know. Lousy title. Could not dream anything better up at 5:30 am on a Monday morning!

A reader wrote in to ask why they could not get their MS Access summary query working. They wanted a report like this.

Group       Budget      Actual
01          $ 34,000    $ 33,756
02          $ 75,000    $ 69,955
03          $ 12,000    $ 13,750
04          $ 25,000    $ 21,223

But their report looked like this.

Group       Budget      Actual
010250      $ 20,000    $ 20,000
010500      $ 14,000    $ 13,756
020722      $ 20,000    $ 13,455
020999      $ 55,000    $ 56,500
etc.

The thing is, that “Group” column is based on a BudgetCode field that has values like this.

010250
010500
020722
020999
etc.

The base query that gets us partway there is easily done using the Query Builder. (the steps that I followed below are based on Microsoft Access 2000, but easily translated to other versions)

• go to the query tab in Access
• click on “New”
• choose Design View and click OK
• select the table (or possibly another query) and click Add
• click Close
• drag the BudgetCode, BudgetAmount, and ActualAmount fields to the query grid
• click on the sum symbol (funny looking “E” in the toolbar)
• under the BudgetAmount and ActualAmount fields in the query grid, click on the Total cell
• from the drop down list choose Sum
• run the query (use the red exclamation mark in toolbar)

That gives us the report style that our reader initially created. With one minor change we will get the final result that we want, summarizing based on just the first 2 characters of the BudgetCode field.

• go back to design view (a little blue triangle symbol at the far left of the toolbar, or under the “View” menu)
• change the BudgetCode field name to this (without quotes) – “left([BudgetCode],2)”
• rerun the query

As a final note, let’s see what the actual “code” looks like. Under the View menu, choose the “SQL View”.

SELECT Left([BudgetCode],2) AS Expr1,
Sum(data.BudgetAmount) AS SumOfBudgetAmount,
Sum(data.ActualAmount) AS SumOfActualAmount
FROM data
GROUP BY Left([BudgetCode],2);

Cool!

As usual, leave comments about your success or questions. Thanks.

In MS Access, what is a query?

This series of articles on MS Access Queries will start off from raw basics. Most of my regulars will already know this material, and so please allow for the fact that this is meant for “newbies” to some degree. I promise to get into more challenging topics as we go along.

Simply put, a query is a question asked of the database, specifically of one or more of the tables in the database. A query’s answer returns values from selected fields (columns) and records (rows).

A basic example could be, “Which of our customers are located in Springfield?”

A more complex query (or question) could be, “Which of our customers in Springfield have not ordered any products in the past 6 months?”

The above examples are of just one type of query; the select query. MS Access has a number of types of queries available.

This series of articles will be written based on Access 2000. Newer versions of Access may have added to this list. The principles explained here should work fine on version 2000 and any newer ones.

Types of Queries

If you create a query manually (more on this later) you choose from a list of 6 query types.

• Select Query retrieves data from one or more tables (or queries!)
• Crosstab query displays summarized data in a column/row format (like pivot tables in Excel)
• Make Table query creates a new table based on data from one or more existing tables (or queries!)
• Update query updates the data in a table (a simple example would be “Add an across the board raise of 2% to each employee’s pay rate”)
• Append query adds new records (rows) to a table
• Delete query will delete records (rows) from a table

Most of the above queries accept criteria (parameters) such that only some of the records in a table are retrieved or updated.

NEXT: How to create a basic query.

I know what you are saying. “Ya, right!”

However, I am serious. My main resolution is to post more often on databaselessons.com.

I will start with a serious of postings about queries.

If you have been at the MS Access game for a while, you may find most of these query postings too simple. Well, this site is for people that are just shedding the pure wizard approach to MS Access, and so you may be right.

As always, please leave comments if you have a good one.

Thanks for reading.

Richard “Manxman” Killey

The open source alternatives to MS Access that I have looked at over the years did not match my favourite database’s full set of features.

A new offering is Kexi, which is a Free/Libre and Open-Source integrated data management application. Kexi is a long awaited Open Source competitor for products like Microsoft Access. Kexi can be used for creating database schemas, inserting data, performing queries, and processing data. Forms can be created to provide a custom interface to your data. All database objects – tables, queries and forms – are stored in the relational database, making it easy to share data and design.

Unfortunately, although Python and Ruby programming languages are supported (beta stage) and JavaScript support is in development, scripting is not yet available on the Windows version.

I do not think I could live without VBA. It makes Access what it is for me. Here is a link to the scripting info for Kexi.

For now, I have to sit in the wings, until the scripting is ported to the Windows version of Kexi.