Subqueries Primer: Find Employees With Salary Higher Than Their Department Average

This is a classic SQL problem- probably more a textbook exercise than a real-life business problem, but a classic nonetheless. "Find all employees whose salary is higher than the average salary for their department."

In Access, the most straightforward approach is a multi-query solution. However it can be also be accomplished in a single query using subqueries. I'll discuss both approahces here

Read more »

Accessing external data using the IN clause

Accessing external data using the IN clause. This article on the Microsoft Access Team Blog shows some of the ways to access data from outside your database by specifying the source of the data in the "In" clause of your query.

Quote:The other use of the IN keyword is as part of the SELECT statement or FROM clause, and is called the IN clause. The IN clause is used to access external data, and can be used in place of linked tables. For example, let's say that you had a SQL Server database that is used as part of an application, but you don't want to maintain a DSN to connect. You could use the IN clause in a query that uses a DSN-less connection to quickly read data from the external table.

It's one of those weird and wonderful things you can do in Access that many developers may never have tried!

Sample Access Databases From Office Online

I find that looking at a demonstration or sample database is a great way to learn more about Access. Microsoft has some great samples databases here on Micorsoft Office Online. that demonstrate some really neat techniques for forms, reports, and queries.

Read more »

SQL Tutorial From W3Schools.com

If you work in Access you need to know SQL, but where do you go to learn it? Here's a tutorial from W3Schools.com. What I like about the W3Schools tutorials is the "try it" box that, in this case, lets you type some SQL and see what it does. Neat!

Video How-To: Make a Query Ask For Input.

Take a look at this Video How-To: Make a query ask for input. It's a great first place to start if you want to learn how to have queries prompt for input, or use a form to provide the criteria for a query.

Quote:
Want to make your query ask for input when it runs? Watch these demos to see how you can add parameters to a query, handle input that is the wrong kind of data, and use a form to collect parameters and then use them with multiple queries.

Subquery: Add Missing Master Records

Let's say you have some data to add to your detail table, but some of the master records are missing. Here's a example query that uses a subquery to add "missing records" based on matching an ID field between two tables.

INSERT INTO tblMasterRecords ( MyID, MyText )
  SELECT MyID , "New Master record"
  FROM tblNewData
  WHERE NOT EXISTS
      (SELECT * FROM tblMasterRecords WHERE MyID=tblNewData.MyID);

Some things to note here:

• The query in the brackets is called a subquery.
• The EXISTS condition resolves to true when the subquery returns any records. By using this in the WHERE NOT EXISTS syntax, the main query returns records from tblNewData only when the subquery doesn't return any records.
• Inside the subquery, the ID of current record in the main query is referenced as tblNewdata.MyID.

If My Filter Doesn't Find Any Records, Show Me All the Records Instead!

Here's a neat little SQL subquery. The request was: If no records match the specified criteria show me all records instead. I think it was to be used in a form where the user would be able to browse the data. They'd use keyword to subset the data, but the design point was to never not show any records.

This can be done using the Exists keyword. Here's an example:

SELECT Clients.*
FROM Clients
WHERE
  (Clients.ClientCode Between "BC" And "BZ")   
  OR   
  (NOT EXISTS
    (SELECT * FROM Clients WHERE Clients.ClientCode Between "BC" And "BZ")
   ) ;

The EXISTS condition resolves to true when the subquery (inside the brackets starting with SELECT) returns any records. In this case I use NOT to reverse that. So if the condition WHERE Clients.ClientCode Between "BC" And "BZ" doesn't find any records, the criteria staring with NOT EXISTS is true, and so all records match the complete criteria (because of the OR) and the query returns all the rows in the table.

Union Query Basics

Union queries are an important part of your SQL toolset that you may be overlooking. I ran across a two part overview of Union queries that would be a good starting point for anyone trying to understand how to use them, or even why they might be useful. Take a look at:

Microsoft Access Union Queries (Part 1)
Microsoft Access Union Queries (Part 2)

Quote:
Union Queries are used to bring together two recordsets of data to merge into one recordset of data. For instance, let’s say you have two tables, one for sales going to individuals, and one for sales going to companies. A union query can bring all of the records from both tables (providing you are querying the same number of fields) into one giant recordset so you can view all of your records at once.
This is useful because although you may want to keep tables separate because they may pertain to different departments, bringing them together into one big query will allow you to run different statistical numbers across all of your sales. You would easily be able to compare the percentage difference in corporate clients to individuals in any region, or perhaps see where your greatest individual sales base is in order to target corporations in the same area.
Union Queries can also be used to create a single source for a mailing list. Union Queries eliminate the need to create a make-table query in order to bring in some records, then an append query to add others on top which bloats the size of your database as you’re storing all this data twice – once in their own tables, and once merged in a new table – which means you’ll have to deal with deleting specific data or updating only certain data and creating new object after new object in your database.
Union Queries are just like other queries, they don’t take up the space of a table, and the query is always updated to reflect new table data. Just like other queries, Union Queries can be used for report record-sources as well! Great stuff!

Filtering Crosstab Queries

I was cruising UtterAccess this morning and came across a thread talking about filtering crosstab queries using parameters. I remember having trouble with this once but had not really needed it lately so hadn't looked into it. The solution in the thread pointed back to two ohter UA posts with the answer:

• I have a report pulling data from a crosstab query. I want to add a date range criteria and also display the start and end date of the result.
• Need to transpose data...crosstab?

It seems it's as simple as specifying the parameter explicity in the query's parameter list. I did a little more reading and found two other good articles:

• ACC2000: Error When Running Crosstab Query with a Parameter (from Microsoft)
• Crosstab query techniques | Handle Paramaters (from Allen Browne)

The problem is summed up tersely in the Microsoft article as:

If you create a crosstab query that uses an implicit parameter [XXX] or a form reference in a WHERE clause (criteria), you may receive the following error message:

The Microsoft Jet database engine does not recognize '[XXX]' as a valid field name or expression.

And including your parameter or a reference to the form control in the query's parameter list solves it quickly and easily. Allen Browne's article has a step-by-step description of doing that.
Buried within one of the Access posts listed above is the phrase:

"You can use the Parameter names in the Report just like a Field in the RecordSource of the Report."

What's that all about? I tried it and it's as simple as that. It's redundant when you're filtering on a form control, but when you let the query prompt for the parameter you can use the parameter in your report just like a column in your recordsource. Really neat for listing the selction criteria in a report's header.
Nice- it's 7:40 on Saturday morning and I've learned two things already today!

**Some links above corrected 2011-09-05**

Use controls on your form to enter criteria for a query

This is a pretty basic technique. Have your query use text boxes or combo boxes on your form as its criteria for selecting records. This allows you to build forms where the user chooses the department to view sales for, or the region to view customers in, etc. Here are just a few of the links on the 'net that demonstrate this technique. By building your query this way and then building your report on the query you effectively filter your report as well.

Access: Filter a query based on a text box value in Access 2003/XP/2000/97
Question: I have anAccess 2003/XP/2000/97 database that contains a form called Orders. I want to be able to create a query that returns the currently selected record from the Orders form. How can I do this?
Answer: One way to write your query is to include a reference to the text box (or other object) that contains the value that you want to use as a query filter.... (more)

How To Use A Combo Box As Criteria For A Query.

Query By Form & Query By Where
This article focuses on a simple Classic Access Techniques called Query By Form and it is written with the idea that you don't need to write masses of code to build a clever Access application, ....(more)

Grouping records in a query by 15 minute intervals

I recently posted on Grouping records on date/time values in an Access report. But what if you want to group on time in a query? Here's an expression you could use in a query for grouping records into fifteen minute intervals. It uses DatePart() to get the minutes part of the time and then uses Int() and multiplication to cut off to the nearest 15 minutes. Then it uses DatePart again to get the hours part and sticks them together.

MyInterval: Format(DatePart("h",[MyTime]),"00") & ":" & Format(Int(DatePart("n",[MyTime])/15)*15,"00")

Crosstab query how-to

Crosstab queries are really powerful. Like so many things in Access, there's a lot to learn to use them effectively. This article from Microsoft would be a great place to start if you were new to crosstabs.

I guess if I were new to crosstabs I might not know why I needed one. Here's an excerpt from the article.

When you want to restructure summary data to make it easier to read and understand, consider using a crosstab query.

A crosstab query calculates a sum, average, or other aggregate function (aggregate function: A function, such as Sum, Count, Avg, or Var, that you use to calculate totals.), and then groups the results by two sets of values— one down the side of the datasheet and the other across the top.

Think a row per sales person and a column per month with total commission per person per month. Or average salary by pay band by division, with pay bands across the top and divisions down the left. This is the functionality Excel calls pivot tables.

Actually despite starting at the beginning, the article seems to cover most of the things you could need to know about crosstab queryies, including specifying the column headings you want, filtering, and handling nulls. The only thing it doesn't cover is reports based on crosstab queries, which get messy because the queries columns can vary with the data. I'll have to watch for a good article on that.

Nulls: why it's important to understand Empty or Unknown fields

An important concept to understand in Access is Nulls. A field's value can be null when it has not been assigned a value. I recently read an updated FAQA post on UtterAccess about nulls which opened my eyes to one aspect of working with nulls. So here's that article, plus some slightly more basic stuff that will be of more general interest

First some advice on working with null data in queries. This is important- many a novice user has got results different than he expected by ignoring the impact of nulls. See the following articles from Allen Browne:

• Nulls: Do I need them?
• Common errors with Null
• The Query Lost My Records!

This article at UtterAccess discusses some of the interesting aspects of working with nulls, especially with regards to concatentating strings. It also has links to a whole bunch of other articles on nulls.

How To Query for Literal Special Characters in a Where Clause

Often in a query you'll want to compare the data in your tables to some text, either a constant or a variable in your VBA code. The query you'll ask Access to process for you will have your text surrounded by either single or double quotes. But what if your data might have quotes in it, such as the product description 12" Ruler, or the last name O'Holloran?

This Micsorsoft article discusses the problem and proposes tactics for dealing with it.

Count of unique items and count of all items in the same query

If you've scanned this blog you'll know of my fascination for subqueries. This thread at UtterAccess.com caught my eye today. The poster needs to count disticnt entries in one column, and all entries in another column, all grouped by the value in a third column.
Sounded to me like three queries. One for the each count and one to produce the final result. The poster, though, challenged the UAers to do it in one.
Van T. Dinh's post is a nice clean all in one query SQL solution. It's a good example of taking the two or three queries you would have done separately and building them up into one query, essentially by surrounding each with brackets and then putting it in the FROM clause like a table.
There are a couple of other posts here on my blog that put the whole subquery in the FROM clause.

• Writing a Count(Distinct) Query in Access
• More subqueries

** Post links cleaned up 2011-09-05 **

Returning Records around a specified ranked Record

Here's a really neat post that uses subqueries to return records "near" a particular record when a bunch of data is ranked.

Quote:
Sometimes, for example when you are dealing with a large table of data that you wish to have ranked, such as a sports league table, you may wish to return the ranking of a particular record, together with some records ranked just before this record, and some just after.

Subqueries: include a running total in a query

Here's another great subquery example from UtterAccess. This one uses a simple subquery to add a running total to a query.

Subqueries: find top 25 by group

Here's an interesting post on UtterAccess where one of the VIPs uses a subquery in a pretty clean and simple way to find top 25 by group.

Cumulative percent: show me the items that contribute to the first 10% of the sales

This is a common business problem, and not one that's easily solved with simple queries or reports. Look around the web and you'll find a number of solutions. Many of those solutions only work in reports and require VBA coding. Here's one that uses only a series of queries and will not require you to get into the muck of SQL or VBA.

Here's sample data for this example:

The question at hand is: how much space is allocated to items that contribute the last 25% of the margin dollars.

Let's show how this would be done in Excel. It may illustrate the concept better:

Having sorted the data and figured out cumulative margin and the percent of the total that represents, we know that in category 2, two items contribute the first 69% of the margin dollars, and the remaining three only contribute 31%. In Excel you have to build formulas by category and so it becomes tedious for large amounts of data. As well, the rest of the analysis you're doing may be in Access so to figure out the cumulative % in Excel would mean moving data back and forth- cumbersome and error-prone!

To do this in Access we need queries that will do the same thing we did using formulas in Excel.

The first query is the hardest. We need to sort the items in descending order and figure out cumulative sales at each item. Here's what it looks like in the query design grid:

The first thing you notice is that tblPerformance is in the query twice. Just add it twice in the Show Table dialog, and Access will automatically give it another name: tblPerformance_1 so that it (and you) can keep the two copies separate.

The two copies of the table are first joined on CategoryNumber, so that the sorting and summing is done within each category. The last column is where the magic happens. Access looks at the two copies of the table, and for each item finds all the items with margin greater than  or equal to that item. Then the sales for those items are summed. This is similar to the Excel example where we sum all the items in the list above the item we're calculating for. Because tblPerformance has two different names we can tell it which table to sum the values from and how to compare the value in one table to the other.

The rest of the queries are easy. You need one that finds the total margin by category:

And one that uses those two queries to turn cumulative dollars into cumulative percent:

Now you can use that query as a filter to find values to sum to answer the original question (how much space is allocated to items that contribute the last 25% of the margin dollars)

Performance:
A query like this can be quite resource intensive. If you run it on a large table you may find it takes too long to run. Consider making the first two queries make table queries and building the third query off the tables. Or try putting indexes on your data table. For this example I built indexes like this:

Remote Queries In Microsoft Access

Here's an article on remote queries: queries that go against tables (or queries!) in other databases. It sounds really powerful.

That's one of the things I like about Access- just when you think you're getting a handle on the breadth of what it can do you find out about a whole new topic.

Excerpt:
"This article discusses one of those hidden gems that I discovered when investigating Access Automation. Whilst looking into that technology, I stumbled across a SQL help topic in Access that described an extension that allows you to point your current query to a table in another database. This in itself didn't seem all that interesting because I had been doing this for years using Linked tables. Then I noticed that you could also point your local query to a query that was in another Access database. No links, no local query definitions, this was starting to get interesting."