I have two tables, tblEvaluations and tblComplexity. The evaluation tables logs the relationship between fingerprint evaluations (match, no match) [Evaluation] to fingerprint impressions [EvidenceID] for the various people [SubjectID] in a case. A complexity rating is tied to an evaluation for a person only when there is a match. So the evaluations with a match to a person will have one complexity rating per impression. However, this complexity rating is calculated by what is selected on the complexity form. (I use a switch function in access to calculate the rating)
Since each evaluation has a unique ID I use a subquery to generate a list of evaluations that are a match (Evaluation =1) for all the evaluations in a case in a subquery, calculate the rating (Plex) and Left Join that query to the evaluations table by joining the appended EvaluationID from the subquery (aliased as Jg) to the EvaluationID in the main table.
The problem is I get a non updatable recordset and I need to be able to update it. I've seen Allen Browne's suggestion of an aggregate function like DLookup, but that seems very convoluted for what I'm trying to accomplish and am looking to see if there is a more elegant way to accomplish this.
Code in case it's helpful
Code:
SELECT ES.*, C.Plex
FROM EvidenceSubjects AS ES
LEFT JOIN (Select ES2.ID as Jg,
C2.*,
Switch( ((C2.FeaturesUsed=2 And C2.FeatureTypes=2) Or (C2.Ambiguity=1 And C2.Interpretation=2) Or C2.Availability=2),'COMPLEX'
, (C2.Orientation=2 Or (C2.FeaturesUsed=2 And C2.FeatureTypes=1) Or (C2.Ambiguity=1 And C2.Interpretation=1)),'ADVANCED'
, (C2.Orientation=1 And C2.FeaturesUsed=1 And C2.Ambiguity=2 And C2.Availability=1)
,'BASIC' ) AS Plex
From EvidenceSubjects ES2
Left Join Complexity C2
on ES2.EvidenceID = C2.ImpressionID
Where ES2.Evaluation =1) AS C
ON ES.ID = C.Jg
WHERE (((ES.SDelete)=False) AND ((ES.CaseID)=[Forms]![frmCaseDetails]![ID]))
ORDER BY ES.EvidenceID;