Thanks Ajax for your response,
If I followed your solution well it seems to address the SELECT query's position. Similar to the one I have below which is actually working and giving me the table in figure 1 below. The problem with this solution is its taking to long to fetch the data, but it is getting the job done nonetheless. So to speed up execution I want the query results to be added to a table so that other reporting operation can can fetch from the table instead.
This SELECT query is working
Code:
SELECT qrySales.[BP No], qrySales.[Customer Name], qrySales.[Sales Date],
(SELECT Max([Sales Date])
FROM qrySales AS OldInvoices
WHERE OldInvoices.[Sales Date] < qrySales.[Sales Date]
AND OldInvoices.[BP No] = qrySales.[BP No]
AND OldInvoices.[Item No] = qrySales.[Item No]) AS [Previous Invoice],
[Sales Date]-[Previous Invoice] AS Lag,
qrySales.[Item No],
qrySales.[Product Name],
qrySales.[Sales Units],
qrySales.Doses,
qrySales.[Sales Amount],
qrySales.[Discount Allowed],
[Sales Amount]-[Discount Allowed] AS [Net Sales]
FROM qrySales
ORDER BY qrySales.[Sales Date] DESC;
The UPDATE query is the one not working
Code:
UPDATE [Product Sales],
[Product Sales] AS [Old Invoices]
SET [Product Sales].[Previous Invoice] =
DMax("[Sales Date]","Product Sales",
"[Old Invoices].[Sales Date] < '" & [Product Sales].[Sales Date] &
"' AND ]Old Invoices].[BP No] = '" & [Product Sales].[BP No] &
"' AND ]Old Invoices].[Item No] = '" & [Product Sales].[Item No] & "'"),
[Product Sales].Lag = [Sales Date]-[Previous Invoice];