Re: Retrieving data from a previous record

Al Campagna

unread,

Nov 17, 2006, 9:33:13 AM11/17/06

to

t4,
Using the AfterUpdate event of MRN on a New record, you could do a DLookup to see if
another MSN exists with that number.
If true, then Dlookup the MRNDate (don't use 'Date' as a date field Name) and the INR
from that previous record.
If False, do nothing.

However, it "seems" like MRN and subsequent transactions against that MRN should be a
one to many relationship... a main table/form for the original MRN, MRNDate, and IRN, and
a related table/subform to hold all subsequent "trasnsactions" against that MRN... rather
than multiple "single record" transactions.
I can't be sure about that from your brief info, but you might want to consider that
option.

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"t4tm" <t4...@discussions.microsoft.com> wrote in message
news:1EC310C8-1B97-42CF...@microsoft.com...
> My first record contains MRN, date, INR. I would like the records that
> follow (based on matching MRN's) to pick up the previous date and previous
> INR, then perform a calculation on the current date and previous date. Is
> this possible?

t4tm

unread,

Nov 17, 2006, 9:48:01 AM11/17/06

to

This is what I would like the data to look like:

MRN MRNDate INR
MRN MRNDate INR PrevINR PrevDate Calculation
MRN MRNDate INR PrevINR PRevDate Calculation

Al Campagna

unread,

Nov 17, 2006, 12:46:58 PM11/17/06

to

t4,
It would have been helpful to see some example data "values", so we could see the flow
of the data.
(don't need to see the calculation)

Are you saying you always need to find the "last" entry against that MRN?
Does each new recoird have a new INR?

If so then your records should contain a TransDate (the date of the new record), and
the Dlookup would use the DMax of the TransDate as a criteria to correctly identify the
"last" existing entry against that unique MRN. That would give you the PrevINR and
PreviousDate for that your new MNR record.

Try this...

> MRN MRNDate INR PrevINR TransDate PrevDate Calculation
> MRN MRNDate INR PrevINR TransDate PrevDate Calculation
> MRN MRNDate INR PrevINR TransDate PRevDate Calculation

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"t4tm" <t4...@discussions.microsoft.com> wrote in message

news:E6234880-EA63-42C8...@microsoft.com...

t4tm

unread,

Nov 21, 2006, 11:31:02 AM11/21/06

to

Here is some sample data if it helps:

MRN MRNDate INR PrvINR TransDate Calculation
123456 01/01/2006 2.1 01/03/2006
111111 01/01/2006 2.8 01/03/2006
123456 01/15/2006 2.5 2.1 01/17/2006 14
111111 01/15/2006 3.0 2.8 01/17/2006 14
123456 02/01/2006 2.6 2.5 02/02/2006 17
111111 02/01/2006 3.1 3.0 02/02/2006 17

Your help is greatly appreciated.

Al Campagna

unread,

Nov 21, 2006, 12:57:47 PM11/21/06

to

t4,
OK, but what happened to PrevDate?
And, did you try my DMax/DLookup suggestion?

Private Sub MRN_AfterUpdate()
Dim PrevTransDate As Date
PrevTransDate = DMax("[TransDate]", "tblINR", "MRN = " & [MRN])
PreviousINR = DLookup("[INR]", "tblINR", "TransDate = #" & PrevTransDate & "# and MRN
= " & MRN)
End Sub

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"t4tm" <t4...@discussions.microsoft.com> wrote in message

news:C8CE6826-7CC8-49D1...@microsoft.com...

aaron...@gmail.com

unread,

Nov 21, 2006, 1:01:29 PM11/21/06

to

Access doesn't support triggers; your shit out of luck
use SQL Server and Access Data Projects

-Aaron

t4tm

unread,

Nov 21, 2006, 2:31:01 PM11/21/06

to

Sorry, forgot to include previous date.
That code works for the INR.

t4tm

unread,

Nov 21, 2006, 2:36:02 PM11/21/06

to

Thank you for all your efforts!

t4tm

unread,

Nov 22, 2006, 7:36:02 AM11/22/06

to

Ok, new twist. Your code works with new data, but my client would like to
populate existing data. Using this code with existing data retrieves only
the last INR and date entered.