query

Question:

In a query with 2 columns based on 2 fields in a table, how can I create a 3rd column that lists the minimum of the other 2 columns?

Answer:

Here is a simple list of sample data for this query.

 Field1      Field2      Smallest
----------------------------------
   5            7            5
   9            3            3
   8            7            7
   1            2            1

Field1 and Field2 come from the table. Smallest is determined by the query.

You might think that there would be an “IF” command that we use in the query. You are almost correct. The command is an “IIF”, or in full, Immediate IF. It is formally called a function and you will find it as part of the VBA programming language.

In Access, the iif function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

The syntax for the iif function is:

iif ( condition, value_if_true, value_if_false )

condition: the value that you want to test

value_if_true: the value that is returned if “condition” evaluates to TRUE

value_if_false: the value that is returned if “condition” evaluates to FALSE

Here is the query that we need:

Select Field1, Field2, iif(Field1 < Field2, Field1, Field2) as Smallest
From Table1

Enjoy.

Some of my Access Database using readers may not know that a query can use another query for its source. Many of us get into the mindset that a query only reads from a table.

So, if I create a new query and choose Design View, the next screen that pops up is called the Show Table dialogue box. But look closer. It is not only showing Tables. There are also tabs for Queries and Both.

When is this useful?

One of the best examples is a library book borrowing database. Answer questions like this. Who was the first person to borrow the book, “How to Create a Query”?

Here are the tables of a simple version of this concept.

tblBooks
---------
lngBookID
strBookName

tblCustomers
-------------
lngCustomerID
strCustomerName

tblBorrowing
------------
lngBorrowID
lngBookID
lngCustomerID
dtmDateDue

The first thought may be to create a “Totals” query that asks for the Min function of the date field. Like this.

This does not work directly, as it returns the first time that each customer borrowed that book. We just want the very first customer who borrowed the book. We can visually see that it was David Martin, but we want the query to determine this by itself.

One solution to this problem follows.

Remove the Customer Name from the first query. Since it is not needed anymore, remove the Customer table as well. The query now returns just one record; that of the first time the book was borrowed. Save that query as qryMinQueryBook, or similar.

Now, start a new query. Add in the query you just created, as well as the Customer table and the Borrowing table. Link the 2 date fields.

This will produce a single record; that of David Martin.

One term that is used for this method is “Cascading Queries”. I have personally used this technique to multiple layers, and horizontally had more than 1 query as well.

In my next article I will show how simple cascading queries like the one above can been done in a single query.

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