Retrieving data from a previous record

[Guest]

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?

 

[Al Campagna]

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.

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[aaron.kempf]

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

-Aaron

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

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.

 

[Guest]

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

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

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.

 

[Guest]

Thank you for all your efforts!

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

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.

 

[Guest]

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.

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

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.