Nested Queries

Some Access queries are created on top of other queries in a nested fashion. Nested queries in Access become nested views in SQL Server. The ORDER BY clauses cannot be part of a view definition; instead they are appended to the SELECT statement that queries the VIEW. If you have nested Access queries, create several views, and then create stored procedures that both perform a SELECT operation on the view and append an ORDER BY clause to the SELECT statement.

For example, the following Access query:

SELECT * 

FROM STUDENTS

WHERE COUNTRY = “USA”

ORDER BY LAST_NAME

Becomes a SQL Server view and a stored procedure:

CREATE VIEW US_STUDENTS AS

SELECT * FROM STUDENTS

WHERE COUNTRY = “USA”

CREATE PROCEDURE US_STUDENTS_ORDER AS

SELECT * FROM US_STUDENTS ORDER BY LAST NAME