FrontPage Tips by Ward Cameron Enterprises
Take Your Web Site to New Levels

Using SQL To Create Sub-Queries

I use SQL instead of Access Queries when I need to create subqueries. Sometimes, what may seem like a simple query can be extremely complex. When possible though, I create the SQL queries within Access. Here is an example.

I wanted to create a query that would count the number of records in a table and then give the percentage of respondents that selected each of 4 answers to a question. This seems simple, but it required several steps.

1. First, I needed a query that could count the number of records in the table.
2. Then I needed a query that would count the number of each of the various responses
3. Finally, I needed a query that would combine the two, essentially take the number of responses for each options, multiply that by 100 and then divide by the number we calculated in step 1.

The easiest way to accomplish this is through the use of a sub-query. Here is what we want to do:

Count the number of responses for each question
Multiply that number by 100
Divide by the total number of responses

Let's Create the First Count Query

In Microsoft Access, Click the Query window and then the New Button. In the New Query window, select Design View and then OK. In order to use SQL to create your query, you'll see a small down arrow to the left of the query button on the left-most button on the toolbar. Click on the down arrow and select SQL View.

In the SQL window, type the following code:

SELECT Count(Q01) AS CountOfQ01
FROM tBearTestResults;

This will create a query that will count the number of records in field Q01 that have values.

Your SQL window will look like the following:

Click on the datasheet view to see your query results:

Testing the query will confirm that your code for the sub-query is correct. We now have a count of the total number of records that provide a response for Question 1 (Q01).

Create A Query to Count the Number of Each Response

Now we follow the same procedure to create a query that will count the number of responses for each of the four options for Question 1.

In a new blank SQL Query window, type the following code:

SELECT Q01, Count(Q01) AS CountOfQ01
FROM tBearTestResults
GROUP BY Q01
HAVING ((Q01) Is Not Null);

This will provide the following query results in datasheet view.

Now, Let's Put It All Together

Finally, we need to combine them both into a single SQL statement.

In a blank Access SQL window, type the following code:

SELECT Q01, Count(Q01) AS CountOFQ01, Format(Count(Q01)*100/(SELECT Count(Q01)
FROM tBearTestResults),'0.0') A
S AnswerPercentage,
IIf([Q01]="Berry 2","(Correct Answer)","") AS Correct
FROM tBearTestResults
GROUP BY Q01, IIf([Q01]="Berry 2","(Correct Answer)","")
HAVING ((Q01) Is Not Null);

You will see the code from your sub-query highlighted in yellow. This combines both queries into a single statement. I've also added a statement that adds an additional field that displays the word (Correct Answer) beside the Berry 2 results. Here are the results in Datasheet View.