event update

[justlearnin]

I have a feild that I want to be updated from another feild in a form, but I
cant use on current because it adds the value each time I open it. What would
be the best event to use for this?
Me.Diff = Me.CurrentCount - Me.Standard
Diff is being passed so that it can be used in the next weeks totals. This
way the current count of the next week starts out with Diff added into it.

 

[Michel Walsh]

Hi,


You should do the computation in a query. Doing it under a form event means
you have to get the right record and the right "event firing stimulus",
which is quite unreliable.



If your table have a dateTime field, then


SELECT a.*, b.*
FROM myTable As a LEFT JOIN myTable AS b ON b.dateTime < a.dateTime
WHERE b.dateTime IS NULL or b.dateTime=(SELECT MAX(c.dateTime) FROM myTable
as c WHERE c.dateTime<a.dateTime)


should "match" the records such as b.* is the record occurring immediately
before a.*. In other words

a.fieldName - b.fieldName


subtract the value in the said field name from two "successive" records
(succession through their dateTime value).


On the other hand, if you just need the SUM up to the datetime, a running
sum, then, try:


SELECT a.dateTime, SUM(b.amount)
FROM myTable As a INNER JOIN myTable As b
ON a.dateTime >= b.dateTIme
GROUP BY a.dateTime



Hoping it may help,
Vanderghast, Access MVP

 

[justlearnin]

Here is a better understanding of what I am trying to do.
Standard=amount to be kept on hand
Used=how many haave been used
Ordered=how many were ordered
CurrentCount=amount ordered
diff=amount to be carried over to next week
I'm not sure using a date field is what I need. Any other suggetions?

 

[Michel Walsh]

Hi,


You know that the order of you records cannot be taken for granted unless
you have an element of data that can be used for the order. In cases of
transactions, the date and time where the said transaction did occur thus
insure the historical order for the records.

I don't know your exact case, but if you were to carry over a cash flow:
Credit - Debit, then


SELECT a.dateTime, SUM(b.Credit - b.Debit) as cashflow
FROM myTransactions As a INNER JOIN myTransactions as b
ON a. datetime >= b.dateTime
GROUP BY a.dateTime


will just do that running cash flow. Example with


dateTime Credit Debit
week1 100 40
week2 10 30
week3 75 115


then the query return

week1 60 ' = 100-40
week2 40 ' = 60 from previous week, + 10 - 30
week3 0 ' = 40, from previous week, + 75 - 115


Here, I used week1, week2, week3 for illustration, but you probably use real
date values (representative of the said week)


Your case is maybe more complex than a cash flow, but the basic principle is
probably the same.

SUM( expression with implied fields, table aliased as b )

hold the carry-over the previous week, and the actual week. You just supply
the expression, if different than for a cash flow.



Hoping it may help,
Vanderghast, Access MVP