You are browsing the archives of manxman.
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?
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
As promised, here is the query within a query solution that simplifies the last article’s idea.
We saw in my last post how a question like “Who was the first person to borrow a book?” could be solved by having a query use the results from another query. That required 2 actual queries saved separately. Now let’s see that same concept done in a single saved query.
Let’s break the problem down into steps.
Step 1 – determine the earliest case of a book being borrowed
SELECT Min(tblBorrowing.dtmDateDue) AS EarliestDate FROM tblBorrowing WHERE (((tblBorrowing.lngBookID)=1));
I have shown the raw SQL above (good idea to learn that stuff!), but you could have just used the query builder to create a query like this following.
Note my use of a custom field name in “EarliestDate”.
Step 2 – Utilize the first query in a where clause
Note how I went back and created the original query style, utilizing all the tables and desired fields, and then added the first query as a criteria for the date field.
This creates a single saved query, rather than 2 separate ones. Here is the resulting (and somewhat hard to read!) SQL code.
SELECT tblCustomers.strCustomerName, tblBooks.strBookName, tblBorrowing.dtmDateDue FROM (tblBooks INNER JOIN tblBorrowing ON tblBooks.lngBookID = tblBorrowing.lngBookID) INNER JOIN tblCustomers ON tblBorrowing.lngCustomerID = tblCustomers.lngCustomerID WHERE (((tblBooks.lngBookID)=1) AND tblBorrowing.dtmDateDue = (SELECT Min(tblBorrowing.dtmDateDue) AS EarliestDate FROM tblBorrowing WHERE (((tblBorrowing.lngBookID)=1)); ));
I will admit, however, that I often clutter my databases with the 2 (or more!!!) query solutions as discussed last week. I find them easier to comprehend and fine tune.
If you want a cleaner looking Queries tab, use this sub-select version!
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.
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
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.
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);
As usual, leave comments about your success or questions. Thanks.