Results 1 to 4 of 4
  1. #1
    thr33of4 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    1

    Subquery alternative to create updatable recordset

    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;

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you cannot write to an aggregate query, but you can write to a field, in a reg query/table that the aggregate reads.
    you update the table value, and this alters the agg.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    How is a domain aggregate function more convoluted than an aggregate query included in a form's RecordSource that makes the form non-updatable? Now you have to write code to save data instead of utilizing the functionality of bound form.

    Suggest you do DLookup.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Show readers some sample data from your tables and an example showing us the output you would like.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Why does this recordset become not-updatable
    By bginhb in forum Programming
    Replies: 3
    Last Post: 08-24-2011, 05:29 PM
  2. Recordset not updatable from form
    By WendyCha in forum Forms
    Replies: 2
    Last Post: 08-09-2011, 12:30 PM
  3. Recordset not updatable...WHY?
    By jgelpi16 in forum Queries
    Replies: 3
    Last Post: 11-30-2010, 08:40 AM
  4. Recordset not updatable
    By Ogian in forum Forms
    Replies: 3
    Last Post: 10-20-2010, 12:08 AM
  5. The Recordset is not updatable
    By bullet_proof302 in forum Access
    Replies: 2
    Last Post: 11-07-2009, 12:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums