Re: event update

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


"justlearnin" <justlearnin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D268AA29-7219-4574-9A26-1F7CFE5661E3@xxxxxxxxxxxxxxxx
> 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" wrote:
>
>> 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" <justlearnin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:BE8C2240-DBE9-487F-8924-69949E386C4F@xxxxxxxxxxxxxxxx
>> >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.
>>
>>
>>


.