by: trycmcw
Total views: 1
Word Count: 500

An understanding of queries is critical to database management. Without queries, you can't get at the information and the data is useless. Once you master queries, a subquery is a powerful tool that greatly expands your abilities to get exactly the data you need. To unleash the real power of a subquery, you need to become familiar with SQL (pronounced "sequel"), the language of database queries. This type of knowledge, usually taught in advanced level Access courses and can make your database exponentially more powerful. Subquery Basics As you would learn in Access courses, a query in SQL is a SELECT statement. A subquery is simply a SELECT statement within a SELECT statement. For example a simple query from our Access courses might read like this: SELECT Employees.Name, Employees.Salary FROM Employees; This retrieves a list of employee names and their salaries from the Employees table. A subquery from advanced Access courses could appear as such: SELECT Employees.Name, Employees.Salary FROM Employees WHERE Employees.Salary IN ( SELECT TOP 5 Salary FROM Employees AS Dupe WHERE Dupe.Salary = Employees.Salary); The main query pulls the same list of employees and their salaries. The subquery then takes from that list the five highest paid employees and puts them in a separate table aliased as Dupe. Subqueries make it easy to perform such tasks as: - Expressing values such as sales made in a given month as a percentage for all sales in the year - List customers who haven't ordered in 60 days or employees who haven't made a recent sale - Extract demographic data on customers from a specific state or city - Calculate year to date totals Problems With Subqueries After learning how to create subqueries you will also learn some of the pitfalls to avoid. Complex subqueries can perform quite slowly. If you find that performance is poor, try a stacked query instead. Make your first query then use the output table as the input table into the second query. Avoid nested subqueries (a query within a query within a query) as they can really drag down performance. Subqueries that use the same table as the main query will require aliases, as demonstrated in the example above. The subquery uses the alias Dupe to make it clear it is referring to fields in the output of the main query and not in the main Employees table. Sometimes Access just chokes on what seems to be a straightforward subquery. Verify the statement, confirming things like matching data types and that none of your names are on the reserved words list. Rewrite the query or, if necessary, break it into stacked queries. Subqueries are incredibly powerful and flexible tools for data access but can also be a source of frustration and error if used incorrectly. Though they can be complicated, you may be amazed at how much more control you have over your data once you master this knowledge.

About the Author

Author is a freelance copywriter. For more information on Access Courses, visit http://www.MicrosoftTraining.net/.

---

View PDF | Print View

---

Rating: Not yet rated

--Choose-- 0 1 2 3 4 5

Comments

No comments posted.

Add Comment

You do not have permission to comment. If you log in, you may be able to comment.
Next Article - Taking A Look At Database Applications
Previous Article - A Nightmare For SEO Hosting

More Related Articles In This Category

1: Does My Company Need Managed Hosting?
2: Taking A Look At Database Applications
3: Learn about Subqueries in Access Courses
4: A Nightmare For SEO Hosting
5: Do You Have Trouble Understanding Databases?
6: MS Access: Access your Personal Information with Ease
7: What The Heck Is An Mup.Sys Error?
8: Material Covered On A Microsoft Access Course
9: The Benefits of Certified Microsoft Training in Access
10: Developing Data Services And Data Models Training
11: Why a Microsoft Access Course Could Change the Way You Do Business

---

For WebMasters

Link This Article - HTML Code -

<a href="http://americanahost.com/articles/computers/databases/learn-about-subqueries-in-access-courses.html" target=_blank>Learn about Subqueries in Access Courses</a>

Link This Article - BBcode -

[url=http://americanahost.com/articles/computers/databases/learn-about-subqueries-in-access-courses.html]Learn about Subqueries in Access Courses[/url]

Copy This HTML Code To Put This Article In Your Web Site.

<h1>Learn about Subqueries in Access Courses</h1><br><br> <br><br> An understanding of queries is critical to database management. Without queries, you can't get at the information and the data is useless. Once you master queries, a subquery is a powerful tool that greatly expands your abilities to get exactly the data you need. To unleash the real power of a subquery, you need to become familiar with SQL (pronounced "sequel"), the language of database queries. This type of knowledge, usually taught in advanced level Access courses and can make your database exponentially more powerful. Subquery Basics As you would learn in Access courses, a query in SQL is a SELECT statement. A subquery is simply a SELECT statement within a SELECT statement. For example a simple query from our Access courses might read like this: SELECT Employees.Name, Employees.Salary FROM Employees; This retrieves a list of employee names and their salaries from the Employees table. A subquery from advanced Access courses could appear as such: SELECT Employees.Name, Employees.Salary FROM Employees WHERE Employees.Salary IN ( SELECT TOP 5 Salary FROM Employees AS Dupe WHERE Dupe.Salary = Employees.Salary); The main query pulls the same list of employees and their salaries. The subquery then takes from that list the five highest paid employees and puts them in a separate table aliased as Dupe. Subqueries make it easy to perform such tasks as: - Expressing values such as sales made in a given month as a percentage for all sales in the year - List customers who haven't ordered in 60 days or employees who haven't made a recent sale - Extract demographic data on customers from a specific state or city - Calculate year to date totals Problems With Subqueries After learning how to create subqueries you will also learn some of the pitfalls to avoid. Complex subqueries can perform quite slowly. If you find that performance is poor, try a stacked query instead. Make your first query then use the output table as the input table into the second query. Avoid nested subqueries (a query within a query within a query) as they can really drag down performance. Subqueries that use the same table as the main query will require aliases, as demonstrated in the example above. The subquery uses the alias Dupe to make it clear it is referring to fields in the output of the main query and not in the main Employees table. Sometimes Access just chokes on what seems to be a straightforward subquery. Verify the statement, confirming things like matching data types and that none of your names are on the reserved words list. Rewrite the query or, if necessary, break it into stacked queries. Subqueries are incredibly powerful and flexible tools for data access but can also be a source of frustration and error if used incorrectly. Though they can be complicated, you may be amazed at how much more control you have over your data once you master this knowledge.<br><br> <h3>About The Author</h3> Author is a freelance copywriter. For more information on Access Courses, visit http://www.MicrosoftTraining.net/.<br><br> Keywords: <b>microsoft access training courses, london, UK, education, computers</b> <br><br>Article Source : <a href="http://americanahost.com/articles"><b>Articles Directory</b></a></br> Article URL : <a href="http://americanahost.com/articles/computers/databases/learn-about-subqueries-in-access-courses.html" target=_blank>Learn about Subqueries in Access Courses</a></br> Article Category : <a href="http://americanahost.com/articles/computers/databases/">Databases</a></br> Article Author : <a href="http://americanahost.com/articles/profile/trycmcw-456.html">trycmcw</a>

---

Article URL : Learn about Subqueries in Access Courses
Article Category : Databases
Article Author : trycmcw