Learn about Subqueries in Access Courses
Published by Christine Harrell in Databases, 8 months 1 week 1 day 6 hours 31 minutes 52 seconds ago
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.
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.
| Traffic Travis Do you want to achieve top -10 rankings in Google, Yahoo, and MSN? | Reciprocal Manager Increase Your Link Popularity,FLOOD Your Website With FREE Traffic |
| Recommended by Team77 | |
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:
| SEOelite The Grand Daddy Of All SEO Software Our Website is ranked #1 in Google | WhyPark.com Stop Parking Your Domain Names... Start Driving Traffic & Revenue |
| Recommended by Team77 | |
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
| Domain Dashboard Manange Domains, Stats, And Keyword From One Control Panel. | Get On Google Find The Key To Google Success With The Google Back Door... |
| Recommended by Team77 | |
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.
| EZ SEO News Keyword Analzyer, SEO website Builder, Sitemap Creator. | SEO Get Top Rankings On Yahoo Google And Msn. Fast Ranking. |
| Recommended by Team77 | |
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 Christine Harrell
Author is a freelance copywriter. For more information on New
Jersey Court Reporters and Court Reporting
Agencies, visit http://www.huseby.com/.
Resources
Research GC History Through Library's Database Garden City News, USA - Through one of the Library's databases, ProQuest Direct, Garden City Library patrons can access the historical backfile of The New York Times to learn more ... Garden City Library News |
 ABC News | Interpol to investigate Mexican databases Middle East Times, Egypt - 19 (UPI) -- Interpol is sending a team of authorities to Mexico to investigate whether its communications system and databases are being used legitimately. ... Arrest of Interpol official sparks security breach concerns |
Computer search system now has unique sign-ons Dayton Daily News, OH - Contractor had been using state databases to run searches even after he stopped working for AG's office. By Laura A. Bischoff COLUMBUS — A powerful system ... |
 Washington Post | STATE POLICE SURVEILLANCE Senators Want Proof That Activists ... Washington Post, United States - ... officials to purge the names of any peaceful protesters the Maryland State Police might have entered into databases that track suspected terrorists. ... Cardin and Mikulski ask federal officials about links to state ... Little Data Disclosed In Files, Activists Police spied on activists through '07 |
 Direct | Jeremy Johnson of Specialists Marketing Services Inc Direct, NY - Publishers are following the lead of catalogers and turning to cooperative databases, because counts for individual lists are shrinking. ... |
Databases - Google News