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!

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!

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!

Embedding SQL in VBA

Often I find myself writing VBA code that creates SQL "on the fly". Most seasoned Access developers do this every day, but if you're new to this you may find this recent post at UA useful.

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.

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."

How do I show the data that isn't there?

Now isn't that an interesting question. It's not such an unusual request: which students haven't submitted a paper? which departments haven't sold any coats today? Or the summary report flavour: when I run my monthly sales some departments don't show up- I want to see a zero where there have been no sales.

To get the answer to a question like this in Access usually involves two steps: find out what "everything" is, and then find out what part of "everything" you don't have.

Let's tackle this one with a students and papers example. Let's say we have three tables:

tblStudents -StudentID -StudentName -etc...

tblPapers -PaperID -PaperName -etc...

tblStudentPapers -StudentID -PaperID -etc...

To find all the papers I should have, I need all the possible student/paper combinations. You do this with an unusual query. Here's the SQL:

SELECT StudentID, PaperID FROM tblStudents, tblPapers;

The query has no join specified- that's why it gives you every combination. I think this is called a Cartesian Product.
For our example let's create the query above and call it qryAllStudentPapers.

Now you just need to use that query and tblStudentPapers and do an "unmatched query". The wizard can create simple unmatched queries, but this one joins on two fields so I don't think it can do it. Here's the SQL. (You can type the SQL into the SQL window and then see how it looks in the design view, then next time create it in the design view from scratch.)

SELECT qryAllStudentpapers.StudentID, qryAllStudentpapers.PaperID
FROM qryAllStudentpapers LEFT JOIN tblStudentPapers ON 
  (qryAllStudentpapers.PaperID = tblStudentPapers.PaperID) AND   
  (qryAllStudentpapers.StudentID = tblStudentPapers.StudentID)
WHERE tblStudentPapers.StudentID Is Null;

I won't stretch this blog out with explanations of LEFT JOINs and how unmatched queries work- you can read up on those on your own. The key here is the two step process- a cartesian product to find what "everything" is, and then an unmatched query to find what part of everything is missing in your data.   #

Poker Series Ranks Using a Simple Subquery

Here's a post at UtterAccess that demonstrates using a simple subquery to calculate ranks.

Ranks are a common challenge for folks using SQL queries to get at their data. This basic example demonstrates a simple technique and includes an example database.   #

Access Basics for Programming

A longer item than I normally link to, this is a great starting point for several topics related to programming Access and VBA, from Crystal, a frequent poster at UtterAccess.com and a Microsoft Access MVP.

If you're new to Access and/or VBA this might be a great place to start. **This link updated to reflect the Feb 2008 revisions**

More subqueries

Here's another cool subquery. The use of a subquery here allows a left join on a calculated field!

SELECT BatchID, NewSKUString, CleanedSKUNumber, SKUID, PROD_NBR, OFFRNG_ENG_DESC, CORP_STAT_CD
FROM
  [SELECT BatchID, NewSKUString, CleanSKUNumber(nz(NewSKUString),True) AS CleanedSKUNumber FROM tblNewSKULists]. AS NewSKUs
  LEFT JOIN tblExistingSKUData
  ON NewSKUs.CleanedSKUNumber = tblExistingSKUData.PROD_NBR;

(CleanSKUNumber is a function)

I read something on UtterAccess that said that when you use square brackets as above Access creates a separate query "behind the scenes". I don't know if that's true or what difference it makes. I can tell you that the above works just the same with round brackets:

SELECT BatchID, NewSKUString, CleanedSKUNumber, SKUID, PROD_NBR, OFFRNG_ENG_DESC, CORP_STAT_CD
FROM
  (SELECT BatchID, NewSKUString, CleanSKUNumber(nz(NewSKUString),True) AS CleanedSKUNumber 
   FROM tblNewSKULists) AS NewSKUs
  LEFT JOIN tblExistingSKUData 
  ON NewSKUs.CleanedSKUNumber = tblExistingSKUData.PROD_NBR;

I was also fascinated to see that if you key the above into the SQL view and then switch back to the QBE grid it shows the subquery as it were a sepeartely saved query.

Subqueries

I am fascinated by subqueries and keep finding things on-line that I didn't know would work. Some of the really elegant subqueries I've created have turned out to be reall performance problems. Others work fine and result in an all-in-one solutions that seems "smarter" and mean less components to manage.

I found one today in a post on the Access team blog. Here's one of the queries from the article:

SELECT C.Color, Sum(C.Value) AS Total, T2.N
  FROM
    (SELECT T.Color, Count(T.Color) AS N
     FROM
      (SELECT DISTINCT Color, Count(*) AS N
       FROM tblColors GROUP BY Color) AS T
       GROUP BY T.Color) AS T2
  INNER JOIN tblColors AS C
  ON T2.Color = C.Color 
  GROUP BY C.Color, T2.N;

This is for count of distinct items. See the blog post for more.