calculation between 2 records

[Guest]

I want to make a calculation between 2 records, for example:

My original table looks like:

Itemnumber date quantity
1 01-11-05 10
1 05-11-05 -8
1 07-11-05 -3
2 01-11-05 5
2 05-11-05 -6

The final result should be:

Itemnumber date quantity total available
1 01-11-05 10 10
1 05-11-05 -8 2
1 07-11-05 -3 -1
2 01-11-05 5 5
2 05-11-05 -6 -1

Because I don't think this is possible in Access, I export my original table
to Excel and calculate the last column. Afterwards I import the table (with
the last column) back to Acess.

I want that this calculation goes automatically.

The first thing I tried was to make a macro in Access with the function
"send keys".
So I open the excelsheet and send keys to calculate the last column.
For the first record this was no problem. But I can't find the right keys to
send to excel to copy the formula downwards.

Does anyone know this keys??

The second thing I tried was to make a macro in excel and tried to send the
keys Ctrl + Z to run the macro.
This din't work uptill now.

Does anyone know how I must send Ctrl + Z to excel?

Finally: Is there perhaps a solution in Access??

Jan van Hooff

 

[Al Camp]

Jan,
Have you tried a Google Groups search in the Access NewGroups under
"access" "forms" "running totals"
There are a lot of "hits" listed under that, with several different
solutions suggested.

 

[Guest]

Firstly, you should not store the Total Available as a column in the table,
but compute it in a query. Storing the data constitutes redundancy and
leaves the door open to update anomalies. To compute the values use a
subquery like so:

SELECT T1.ItemNumber, T1.[Date], T1.Quantity,
(SELECT SUM(Quantity)
FROM YourTable AS T2
WHERE T2.ItemNumber = T1.ItemNumber
AND T2.[Date] <= T1.[Date]) As TotalAvailable
FROM YourTable
ORDER BY T1.ItemNumber, T1.[Date];

The result set of this query won't be updatable due to the use of the SUM
function. If you want it to be updatable you'd have to use the VBA DSum
function:

SELECT ItemNumber, [Date], Quantity,
DSum("Quantity","YourTable","ItemNumber = " & YourTable.ItemNumber &
" And [Date] <= #" & Format(YourTable.[Date],"mm/dd/yyyy") & "#") As
TotalAvailable
FROM YourTable
ORDER BY ItemNumber, [Date];

Note that both of these assume that there is only one transaction per
itemnumber per date. Incidentally I'd advise against the use of Date as a
column name; it might be confused by Access with the date function in some
circumstances. This is why I've enclosed it in square brackets, which shows
Access its an object.