[AccessD] A database problem....looking for a solution

Mark,

    Amongst multiple records for a given contract, you wish to update the first record as per latest significant entry in each field (ignoring Nulls). As stated by you the table has large number of fields (100 or so) required to be handled in this manner. 

    In such a situation, you might find it desirable to place the results in a new table so as to facilitate ready comparison with original data. Direct query with field-wise subqueries for such a large number of fields could turn out to be unwieldy and too slow.

    Let the name of source table be T_A, having fields RecID (PK) and ContractID, apart from other fields. Optional field SortField can be included for determining the order of records. If such a field is missing, it is presumed that sort order is governed by primary key field. Destination table T_B (in empty state to start with) has the same structure as that of source table T_A.

    Sample subroutine P_UpDtByLastSignificantValue() as given below (at the end of this post), should get you the desired results in destination table T_B, providing one record per ContractID. Sample call for running this procedure would be as follows:

' Sample call:
'==========================
    P_UpDtByLastSignificantValue _
                    "T_A", "T_B", "RecID", _
                    "RecID", "ContractID"
'==========================

    Note:
    The last argument is optional and can be omitted if the source table has data pertaining to only a single contract.

Best wishes,
A.D. Tejpal
------------

' Sample code in general module
'==========================
Sub P_UpDtByLastSignificantValue( _
            SourceTblName As String, _
            DestnTblName As String, _
            PkFldName As String, _
            SortFldName As String, _
            Optional CategoryFldName _
            As String = "")
    ' Updates various fields first recordfor
    ' each category as per latest distinct
    ' values (ignoring Nulls) in all subsequent
    ' records for the given category.
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim fd As DAO.Field
    Dim Qst As String
    
    Set db = DBEngine(0)(0)
    
    If Len(CategoryFldName) > 0 Then
        Qst = "SELECT DISTINCT " & _
                CategoryFldName & _
                " FROM " & SourceTblName & ";"
    Else
        Qst = "SELECT TOP 1 'ZZZ' " & _
                " FROM " & SourceTblName & ";"
    End If
    
    Set rs1 = db.OpenRecordset(Qst)
    
    Do Until rs1.EOF
        With db.OpenRecordset(DestnTblName)
            .AddNew
            For Each fd In db.TableDefs( _
                                        SourceTblName).Fields
                If fd.Name = PkFldName Then
                Else
                    Qst = "SELECT TOP 1 " & _
                        fd.Name & " FROM " & _
                        SourceTblName & " WHERE " & _
                        " (NOT " & fd.Name & _
                        " IS Null) "
                    Qst = Qst & _
                        IIf(CategoryFldName <> "", _
                        " AND " & CategoryFldName & _
                        " = '" & rs1.Fields(0) & "' ", "")
                    Qst = Qst & " ORDER BY " & _
                        SortFldName & " Desc;"
                    Set rs2 = db.OpenRecordset(Qst)
                    If rs2.RecordCount > 0 Then
                        .Fields(fd.Name) = rs2.Fields(0)
                    End If
                End If
            Next
            .Update
        End With
        rs1.MoveNext
    Loop
    
    rs1.Close
    rs2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set fd = Nothing
    Set db = Nothing
End Sub
'==============

  ----- Original Message ----- 
  From: Mark Simms 
  To: 'Access Developers discussion and problem solving' 
  Sent: Wednesday, November 24, 2010 06:48
  Subject: [AccessD] A database problem....looking for a solution


  A row is inserted with contract data - 100 columns are populated.
  6 months later, an "amended" row is inserted  - 10 columns are changed to
  reflect updated data; the other 90 columns are null.
  4 months after that, another 4 columns are changed and another "amended" row
  is inserted.
   
  So essentially, there is a base row, and then update rows....similar to a
  log.
  I see no easy way with SQL to report A SINGLE ROW with all columns that
  reflects the columns changed in the amended rows.
  However, inserting the first row into a temp table and then conditionally
  updating using the amended rows, 
  and then reporting from the temp table seems like one way to go.
   
  Any other suggestions ?