The following query is a 'Make Table' query in Microsoft Access 2000:

SELECT LocalInvoice.InvoiceNumber, LocalInvoice.CustomerRef_FullName,
LocalInvoice.TxnDate, LocalInvoice.Subtotal, LocalInvoice.ShipDate,
Customer.JobStatus, qryTotalTransactionsByInvoice.InvoiceAmount,
qryTotalTransactionsByInvoice.MaxOfShipWeek AS ShipWeek, 0 AS PayrollAmount,
CCur(0) AS SalesItem, LocalInvoice.IsPending INTO ProductivityAnalysis
FROM (LocalInvoice INNER JOIN qryTotalTransactionsByInvoice ON
LocalInvoice.InvoiceNumber = qryTotalTransactionsByInvoice.InvoiceNumber)
INNER JOIN Customer ON LocalInvoice.CustomerRef_ListID = Customer.ListID
WHERE (((Customer.JobStatus)="Closed") AND ((Year([ShipDate]))=2002))
ORDER BY LocalInvoice.ShipDate;

The TotalTransactionsByInvoice query is:

SELECT AnalysisDetail.InvoiceNumber,
Sum(AnalysisDetail.Quantity) AS InvoiceQuantity,
Count(AnalysisDetail.UnitPrice) AS PriceCount,
Sum(AnalysisDetail.Amount) AS InvoiceAmount, Max(AnalysisDetail.ShipWeek)
AS MaxOfShipWeek FROM AnalysisDetail WHERE AnalysisDetail.NonInventoryItem=1
GROUP BY AnalysisDetail.InvoiceNumber;

Below is a stored procedure that does the equivalent work in SQL-Server:

CREATE PROCEDURE GenerateProductivityAnalysis AS

truncate table ProductivityAnalysis;

SELECT i.InvoiceNumber, i.CustomerRef_FullName, i.TxnDate, i.Subtotal,
i.ShipDate, c.JobStatus, t.InvoiceAmount, t.MaxOfShipWeek AS ShipWeek,
0 AS PayrollAmount, convert(money, 0) AS SalesItem, l.IsPending
FROM (LocalInvoice i INNER JOIN qryTotalTransactionsByInvoice t
ON i.InvoiceNumber = t.InvoiceNumber) INNER JOIN Customer c ON
i.CustomerRef_ListID = c.ListID WHERE Customer.JobStatus='Closed'
AND datepart(yyyy, ShipDate)=2002 ORDER BY i.ShipDate;

'Total Transactions By Invoice' is a view in SQL-Server, and this is defined as:

SELECT InvoiceNumber, SUM(Quantity) AS InvoiceQuantity,
COUNT(UnitPrice) AS PriceCount, SUM(Amount) AS InvoiceAmount,
MAX(ShipWeek) AS MaxOfShipWeek FROM AnalysisDetail d
WHERE NonInventoryItem = 1 GROUP BY InvoiceNumber;

In Access, the query is 'MakeTable' with the name of the new table in the "INTO" expression. The SQL-Server equivalent does not create a new table. Truncate table... gets rid of any existing rows in the table named.

The Access CCur(0) function becomes the SQL convert(money, 0) function.

The Year([ShipDate]) function becomes datepart(yyyy, ShipDate).

The table name assignments work nearly the same, however the SQL convention of using 'short form' table names is used advantageously. Access always prefixes a data value with the originating table if such a thing exists. In SQL this can be replace with the letter code which is assigned to a real table as the database is updated.

If you have a 'query from hell' send it to me. I may not be able to answer all questions, but I'll try to address the interesting ones. The address is info@resourcelogic.net. Please put a description of your question or comment on the subject line. Thanks.