The fact that Access allows you to easily construct calculated fields based on other calculated fields means that you can produce very complex calculations with great ease. Unfortunately SQL Server will not allow you to use this technique. If faced with a poorly performing Access Query that uses multiple layered queries, complex calculations and lots of IIF logic in the queries, then you can be facing a bit of a nightmare to convert the SQL to a server-side query written in T-SQL.

This example uses the [Order Details] table in the Northwind database.

Below we have an Access query, which we shall assume is giving very poor performance, and needs to be converted to a pass-through query.

A Problem Converting Calculations

The example shown here is very simple, yet you will soon see how it can become very complicated

If you try the following in a pass through query it will not work.

SELECT [Order Details].UnitPrice, [Order Details].Quantity, [UnitPrice]*[Quantity] AS LinePrice, 0.175*[LinePrice] AS VAT, [LinePrice]+[VAT] AS TotalPrice FROM [Order Details]

This is because SQL Server does not allow calculated fields to refer to other calculations. You could try the following restating each calculation, but for complex calculations this can be very difficult.

SELECT [Order Details].UnitPrice, [Order Details].Quantity, [UnitPrice]*[Quantity] AS LinePrice, 0.175*[Quantity] *[UnitPrice] AS VAT, [Quantity] *[UnitPrice]+ 0.175*[Quantity] *[UnitPrice] AS TotalPrice FROM [Order Details]

Possible Solutions

In Access it is common practise to design a query which uses another query, this process of layering queries on top of queries can be reproduced using views in SQL Server. Views have more limitations than Access queries but breaking a query down into a series of steps layered on top of each other is one possible solution. Although in a complex application this could involve making what already could be a sequence of views into an even greater layered sequence of views. So what other alternatives are there?

Another alternative is to use SQL Server functions, building functions which use other functions, and if you have time this could also provide a possible soloution.

A third option would be to utilise joins, below is a partial solution to our problems, but the need here to specify the join criteria can again lead to unwanted complexity.

SELECT od.[OrderId], od.[ProductId],od.UnitPrice, od.Quantity,LinePrice, 0.175*[LinePrice] AS VAT FROM [Order Details] AS od INNER JOIN (SELECT OrderId, ProductId,UnitPrice, Quantity, [UnitPrice]*[Quantity] AS LinePrice FROM [Order Details] ) AS JoinedQuery ON od.[OrderId] = JoinedQuery.[OrderId] AND od.[ProductId] = JoinedQuery.[ProductId]

The final option is to use a Nested Queries, I attribute this to having spent a fair amount of time with one of my colleagues Brad Barton who first introduced this to me as his preferred technique when working with IBM systems. Nested Queries are a very much underused technique in SQL Server.

Top of Page

Nested Queries

Whilst subqueries allow a query to be injected into another queries WHERE clause, nested queries allow a query to be injected into another queries FROM clause. This is an extremely ellegant method for solving problems which require data to have complex calculations and summaries in a single step.

The example below goes beyond the join solution, to give a full solution based on a three level query. The only syntactical issue is that at each level the query must be given a name using an AS clause.

SELECT *,[LinePrice]+[VAT] AS TotalPrice FROM (SELECT *, 0.175*[LinePrice] AS VAT FROM (SELECT UnitPrice, Quantity, [UnitPrice]*[Quantity] AS LinePrice FROM [Order Details] ) AS InnerQuery ) AS NextLevel

I hope you will agree, that nested queries can provide an extremely elegant method for solving complex problems. In particular when dealing with systems where you are not allowed to create server-side views and objects, nested queries are an invaluable tool in a developers pocket.

MUST+SQL automatically translates your Access queries into Nested Queries. Click below to improve your development productivity.

Top of Page

Download Code

The sample code can be downloaded from here Access 2000 Database

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz

Top of Page