Resource Logic Client/Server Development Services
Issues in Converting Microsoft Access 97
Microsoft Access 2000 ADP Projects
The following query is a 'Make Table' query in Microsoft Access 2000:
SELECT LocalInvoice.InvoiceNumber, LocalInvoice.CustomerRef_FullName,
LocalInvoice.TxnDate, LocalInvoice.Subtotal, LocalInvoice.ShipDate,
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:
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
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 email@example.com. Please put a description of your question or comment
on the subject line. Thanks.