Getting value from previous record

  • Thread starter Thread starter jtfalk
  • Start date Start date
J

jtfalk

Hello,

I have an inventory database. I have the sum of apples in my last record and
want to subtract apples today from it:

Total apples (in last record) = 25
(new record) sell 5 apples = total apples = 20

me.total_apples = me.sell_apples + (last record me.total_apples)

How can I get that last records me.total_apples.

I do not have an open internet access so I can not go see alan browns web
page only microsoft web pages.

Thanks
 
jtfalk said:
Hello,

I have an inventory database. I have the sum of apples in my last record
and
want to subtract apples today from it:

Total apples (in last record) = 25
(new record) sell 5 apples = total apples = 20

me.total_apples = me.sell_apples + (last record me.total_apples)

How can I get that last records me.total_apples.

I do not have an open internet access so I can not go see alan browns web
page only microsoft web pages.
Click to expand...


In order to even begin to talk about getting values from a previous record,
you have to define what "previous" means in the context of your table. For
most purposes, Access treats a table as a "big bag of records", with no
defined order -- most specifically, records are not inherently ordered by
entry sequence. In any situation where you want to talk about "previous" or
"next" records, you must specify a sort order for the records -- for
example, in the ORDER BY clause of a query. And that sort order must be
defined in terms of data in the records.

If you need to process records in order of entry, then you need to have a
date/time field in the records that is set to Now() whenever a record is
entered. If your records have a consecutive autonumber primary key, then In
carefully restricted circumstances, you might use that key field as an
indicator of the entry sequence. However, not all tables use autonumber
keys, and even in those that do, autonumbers can become random, not
consecutive, under certain circumstances. So if you care about entry
sequence, you really ought to use a date/time field to store the
information.

Once you have established a field that you can use to determine the sequence
of records, you don't really need to store the current inventory total in
any record. It can always be calculated on the fly by adding up the
transaction quantities of all records entered up through the current one.
In some cases, it may be useful to store it for efficiency's sake, but that
runs the risk of having inconsistent data if, for example, somebody modifies
one of the earlier records without recalculating the total in all subsequent
records. So I don't recommend storing this calculated data unless you find
you really need to.
 
Store each individual transactions, with their date (and time).

To have the "total" as by the last transaction, use a total query:

SELECT SUM(amount)
FROM tableName

Note that amount can be + or -, accordingly to the movement in inventory.

The benefits are that you can still see each transaction individually, and
the total sum is always up to date, even if you change the amount value way
back in the transactions; the solution you proposed would require to modify
all the records from that transaction up to the current one.

If you need a running total, you can use:

SELECT a.*, DSUM("amount", "tableName", "dateTimeStamp<=" & Format(
dateTimeStamp, "\#mm-dd-yyyy hh:nn:ss\#")
FROM tableName As a
ORDER BY a.dateTimeStamp ASC


and again, the running total is always "up to date" since it is freshly
computed, as required.


Vanderghast, Access MVP
 
Access stores data in something like "buckets o' data", with no inherent
order. When you say "last record", that may not be the same thing that
Access believes is the "last record".

First you'll need to define how YOU determine the "last record".

Then you have another potential issue. If your table is storing a total
number, then it will need to be changed every time a new record is added or
an old record is updated. This kind of "maintenance" can be very
cumbersome.

As an alternate approach, consider storing only the "transaction", and using
a query to total up the 'current' total.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
I should have said more. The table has an autonumber. I just want to look up
the last record (highest autonumber) and get the value from Total Apples and
put it into the Total Apples on the new rocord as well as + and - some:

Example:
Record 3: Total Apples = 25
New Record: Total Apples = Total Apples + new apples (10)- sold apples (5)
New Record on close is Total Apples = 30
Then on the nest new record I can call the 30
 
jtfalk said:
I should have said more. The table has an autonumber. I just want to look
up
the last record (highest autonumber) and get the value from Total Apples
and
put it into the Total Apples on the new rocord as well as + and - some:

Example:
Record 3: Total Apples = 25
New Record: Total Apples = Total Apples + new apples (10)- sold apples (5)
New Record on close is Total Apples = 30
Then on the nest new record I can call the 30
Click to expand...

You are asking me to tell you how to shoot yourself in the foot, and you
aren't even giving me enough information to do that. Let me stress again
that you can show on your form the correct "Total Apples" value without ever
storing it in the record.

IF you really want to do this BAD THING, despite the advice of everyone who
has posted in this thread to date, then -- IF YOU REALLY MUST -- you will
use the form's BeforeUpdate event to do something like this:

'------ start of example code ------
Dim varPrevID As Variant
Dim lngLastTotal As Long

If Me.NewRecord Then

varPrevID = DMax("RecordID", "tblApples", "RecordID<" & Me.RecordID)

if IsNull(varPrevID) Then
lngLastTotal = 0
Else
lngLastTotal = Nz( _
DLookup("[Total Apples]", "tblApples", "RecordID=" &
varPrevID), _
0)
End If

Me![Total Apples] = lngLastTotal + Me![New Apples] - Me![Sold Apples]
'------ end of example code ------

Of course, you would need to use your own names for tables, fields, and
controls.

BUT THIS IS A BAD IDEA!
 
You posted in a newsgroup and asked for suggestions. You can choose not to
follow them, and since you are paying nothing for them, you can decide they
are worth what you're paying.

.... or you can consider that some folks here may have already learned about
that and found ways that, in the long run, work much better.

Access is not a spreadsheet. You don't need to keep a "total" in your
records... or rarely so.

Good luck

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
GOT IT,

I was not even thinking about just using the sum it the query from the
control source. Simple and easy and as you said much better.

Dirk Goldgar said:
jtfalk said:
I should have said more. The table has an autonumber. I just want to look
up
the last record (highest autonumber) and get the value from Total Apples
and
put it into the Total Apples on the new rocord as well as + and - some:

Example:
Record 3: Total Apples = 25
New Record: Total Apples = Total Apples + new apples (10)- sold apples (5)
New Record on close is Total Apples = 30
Then on the nest new record I can call the 30
Click to expand...

You are asking me to tell you how to shoot yourself in the foot, and you
aren't even giving me enough information to do that. Let me stress again
that you can show on your form the correct "Total Apples" value without ever
storing it in the record.

IF you really want to do this BAD THING, despite the advice of everyone who
has posted in this thread to date, then -- IF YOU REALLY MUST -- you will
use the form's BeforeUpdate event to do something like this:

'------ start of example code ------
Dim varPrevID As Variant
Dim lngLastTotal As Long

If Me.NewRecord Then

varPrevID = DMax("RecordID", "tblApples", "RecordID<" & Me.RecordID)

if IsNull(varPrevID) Then
lngLastTotal = 0
Else
lngLastTotal = Nz( _
DLookup("[Total Apples]", "tblApples", "RecordID=" &
varPrevID), _
0)
End If

Me![Total Apples] = lngLastTotal + Me![New Apples] - Me![Sold Apples]
'------ end of example code ------

Of course, you would need to use your own names for tables, fields, and
controls.

BUT THIS IS A BAD IDEA!

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 
Back
Top