How can I reference a value from a previous record?

[Guest]

Greetings forum members:

I have a simple inspection data table that contains the following five fields:

1. Primary Key
2. Asset ID (Duplicates OK)
3. Inspection Date
4. Start Value
5. Finish value

The data in this table must abide by the following rule:

For each Asset ID; The start value of the current inspection record must be
greater than or equal to the finish value of the previous inspection record.
(The previous record would be determined using the Inspection Date field)

My question is:

How can I reference the value from the previous inspection record, in order
to verify that this rule has not been broken?

PS. My first thought was to create a query and try to accomplish this within
a calculated field. I do not know how to reference a previous record,
however. Am I on the right track???

 

[Jeff Boyce]

That could depend on whether your data is being stored in an Access .mdb
table (a new record ID is created when you "dirty" the new record) or in
SQL-Server (a new record ID is created AFTER you save the record), or in
....).

I'd go with finding the maximum FinishValue for any record(s) with the same
AssetID. You could use a query, but you could also use the DLookup()
function.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

I'll also point out that you could have the form default to a value in the
new record's StartValue that is one greater than that looked up value (but
maybe 1 greater than isn't an appropriate default).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

You should be able to identify any existing rows in the table which break the
rule by means of a query containing a subquery which returns the End Value
for the outer query's current asset where, by means of a further subquery,
the Inspection Date is the latest date prior to the current one:

SELECT T1.*
FROM [Your Table] AS T1
WHERE [Start Value] <
(SELECT [End Value]
FROM [Your Table] AS T2
WHERE T2.[Asset ID] = T1.[Asset ID]
AND T2.[Inspection Date] =
(SELECT MAX([Inspection Date])
FROM [Your Table] AS T3
WHERE T3.[Asset ID] = T1.[Asset ID]
AND T3.[Inspection Date] < T1.[Inspection Date]));

The above compares each row with the latest previously dated row for the
current asset, but it should be possible merely to compare each row with any
previously dated row for the current asset as the values are incremental.
You should be able to do this with a join as follows:

SELECT T1.[Asset ID], T1.[Inspection Date], T1.[Start Value],
T2.[Inspection Date] As [Previous Inspection Date],
T2.[End Value] AS [Previous End Value]
FROM [Your Table] AS T1, [Your Table] AS T2
WHERE T1.[Asset ID] = T2.[Asset ID]
AND T1.[Inspection Date] > T2.[Inspection Date]
AND T1.[Start Value] < T2.[End Value];

Having corrected any rows which break the rule, for future data entry
purposes you can validate the value entered into the Start Value control on a
form by looking up the highest End Value for the current Asset ID. Put code
in the control's BeforeUpdate event procedure, which has a Cancel argument
whose return value you can set to true if the validation fails:

Const conMESSAGE = "Start value is less than previous end value for this
asset."
Dim strCriteria As String

strCriteria = "[Asset ID] = " & Me.[Asset ID]

If Me.[Start Value] < Nz(DMax("[End Value]", "[Your Table]", strCriteria),0)
Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

Ken Sheridan
Stafford, England