Sheila said:
You will need to create a report
You are right that this is probably best done in a report but it *can*
also be achieved in a query...
you may need to have something to identify which
records need to be added and then Group on that field (unless you already
have this) Access DOES NOT work like Excel so you cannot assume that records
are maintained tin the order in whihc they are entered.
In this case the OP has stated that the 'Date' column provides the
order. There are missing aspects of the spec, though e.g. what to do
when there are ties for 'previous date' values? I'll assume the date
values are unique.
I didn't post this yesterday because I was ashamed of the fact I had to
use the table *three* times. Perhaps someone can do better...?
SELECT DT1.blah_date, DT1.blah_amount,
DT1.blah_previous_date,
T3.blah_amount AS blah_previous_amount,
DT1.blah_amount +
IIF(T3.blah_amount IS NULL, 0, T3.blah_amount)
AS blah_cumm_amount
FROM
(
SELECT T2.blah_date, T2.blah_amount,
MAX(T1.blah_date) AS blah_previous_date
FROM Blah AS T1
RIGHT JOIN Blah AS T2
ON T1.blah_date < T2.blah_date
GROUP BY T2.blah_date, T2.blah_amount
) AS DT1
LEFT JOIN Blah AS T3
ON DT1.blah_previous_date = T3.blah_date
ORDER BY DT1.blah_date;
As usual, here's the VBA to reproduce the scenario, test data and
results:
Sub blahs()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
' Create test table
.Execute _
"CREATE TABLE Blah ( blah_date DATETIME NOT" & _
" NULL UNIQUE, blah_amount INTEGER NOT NULL)"
' Create test data
.Execute _
"INSERT INTO Blah (blah_date, blah_amount)" & _
" VALUES (#2006-01-01 00:00:00#, 1);"
.Execute _
"INSERT INTO Blah (blah_date, blah_amount)" & _
" VALUES ('2005-01-12 00:00:00', 2);"
.Execute _
"INSERT INTO Blah (blah_date, blah_amount)" & _
" VALUES (#2004-01-24 00:00:00#, 3);"
.Execute _
"INSERT INTO Blah (blah_date, blah_amount)" & _
" VALUES (#2006-01-24 00:00:00#, 4);"
Dim rs
Set rs = .Execute( _
"SELECT DT1.blah_date, DT1.blah_amount, DT1.blah_previous_date," &
_
" T3.blah_amount AS blah_previous_amount," & _
" DT1.blah_amount + IIF(T3.blah_amount IS" & _
" NULL, 0, T3.blah_amount) AS blah_cumm_amount" & _
" FROM (SELECT T2.blah_date, T2.blah_amount," & _
" MAX(T1.blah_date) AS blah_previous_date" & _
" FROM Blah AS T1 RIGHT JOIN Blah AS T2 ON" & _
" T1.blah_date < T2.blah_date GROUP BY T2.blah_date," & _
" T2.blah_amount) AS DT1 LEFT JOIN Blah AS" & _
" T3 ON DT1.blah_previous_date = T3.blah_date" & _
" ORDER BY DT1.blah_date")
MsgBox rs.GetString(2, , , , "(null)" & vbTab)
rs.Close
End With
Set .ActiveConnection = Nothing
End With
End Sub
Jamie.
--
