Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Updating a crosstab query

    First, yes I know you can't. I'm after a way round the problem more than anything.

    Click image for larger version. 

Name:	pic1.png 
Views:	63 
Size:	17.1 KB 
ID:	29013



    This form is just what I need but its based on a crosstab query so is non updateable.

    I was hoping to allow staff to fill in pupil scores then have the total calculated. Infuriating.

    Any ideas how I can get round the problem?

  2. #2
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Are the results stored in a junction table?

  3. #3
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Post the relationships relating to this. (that's my new favourite saying)

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397

  5. #5
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    not really Ajax. Perhaps i'll just have users enter results of one paper at a time

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can use this code to create a disconnected recordset which is editable. This has only been very roughly tested and may not meet your needs,

    Note that you can use this method for changing existing data - if an xtab field was previously null, then you would need to use an insert query (you'll need to test for oldvalue=null) since there is no existing record. Similarly, it is not a way of adding new rows to the xtab, although I guess if the parameters are available you could do.

    Also, if you delete an entry - should this delete the underlying record? or just set the value to null?

    Your work will be in the xtabAfterUpdate function below and what you need to do depends on the complexity of your xtab and what you want to happen


    put this in your form
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Form_Open(Cancel As Integer)
    Dim rst As DAO.Recordset
    Dim DB As DAO.Database
    
        Set DB = CurrentDb
        Set rst = DB.OpenRecordset("name of xtab query")
        If Not rst.EOF Then
            Set Me.Recordset = makeADORecordset(rst)
        End If
        rst.Close
        Set rst = Nothing
        
    End Sub
    and this in a module
    Code:
    Function makeADORecordset(ByVal rstD As DAO.Recordset) As ADODB.Recordset
    Dim rstA As ADODB.Recordset
    Dim Fld As DAO.Field
    Dim i As Integer
    
        Set rstA = New ADODB.Recordset
        With rstA
            .CursorLocation = adUseClient
            .LockType = adLockPessimistic
            For Each Fld In rstD.Fields
                Select Case Fld.Type
                    Case dbText
                         .Fields.Append Fld.Name, adVarChar, 255, adFldIsNullable
                    Case dbMemo
                         .Fields.Append Fld.Name, adLongVarWChar, 64000, adFldIsNullable
                    Case dbByte
                         .Fields.Append Fld.Name, adUnsignedTinyInt, , adFldIsNullable
                    Case dbInteger
                         .Fields.Append Fld.Name, adSmallInt, , adFldIsNullable
                    Case dbLong
                         .Fields.Append Fld.Name, adInteger, , adFldIsNullable
                    Case dbSingle
                         .Fields.Append Fld.Name, adSingle, , adFldIsNullable
                    Case dbDouble
                         .Fields.Append Fld.Name, adDouble, , adFldIsNullable
                    Case dbDecimal
                         .Fields.Append Fld.Name, adNumeric, , adFldIsNullable
                    Case dbDate
                         .Fields.Append Fld.Name, adDate, , adFldIsNullable
                    Case dbCurrency
                         .Fields.Append Fld.Name, adCurrency, , adFldIsNullable
                    Case dbBoolean
                         .Fields.Append Fld.Name, adBoolean, , adFldIsNullable
                    Case dbLongBinary
                        .Fields.Append Fld.Name, adLongVarBinary, , adFldIsNullable
                    Case dbGUID
                         .Fields.Append Fld.Name, adGUID, , adFldIsNullable
                    Case dbBinary
                         .Fields.Append Fld.Name, adVarBinary, , adFldIsNullable
                    Case Else
                        MsgBox "field type not converted"
                 End Select
            Next
            .Open
        End With
            
        'populate ado recordset
        If Not rstD.EOF Then
            rstD.MoveFirst
            While Not rstD.EOF
                rstA.AddNew
                For Each Fld In rstD.Fields
                    rstA.Fields(Fld.Name) = rstD.Fields(Fld.Name)
                Next Fld
                rstD.MoveNext
                rstA.Update
                
            Wend
            Set rstD = Nothing
        End If
        Set makeADORecordset = rstA
        
    End Function
    
    
    Function xtabAfterUpdate()
    
    msgbox "put some code here to update the source record"
    'you may need to bring some additional fields across in your xtab for reference purposes (perhaps even another xtab) but in principle it would be something like
    dim db as dao.database
    
        set db=currentdb
        with screen.activecontol
    
             'notes- .name may need surrounding with quotes or # depending on datatype. This example is based on a simple xtab displaying some details from a parent table (e.g. employeePK and name) and a child table showing what roles an employee has had
            'db.execute "Update tblRolesAssigned set val1=" & .value & " WHERE EmployeeFK=" & .parent.EmployeePK & " AND Role=" & .name
            
        end with
        set db=nothing
    
    End Function
    and in each of your crosstab controls you want to be able to update put

    =xtabAfterUpdate()

    against the afterupdate event (instead of [Event Procedure])

  7. #7
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Thanks a million. Will test later and let you know

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I have an assessments feature in a database for schools.
    the data entry form is based on a crosstab query as each course has a different number of assessments each with different names and max marks.
    To make the data entry form editable, a make table query is used to create a temp table as the form is opened.
    As each mark is entered the data is saved back to the original normalised table.
    Although convoluted it works seamlessly and quickly
    Last edited by isladogs; 06-08-2017 at 06:06 PM. Reason: typos

  9. #9
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    So you make a temp table, fill it in but how to results get into original normalised table

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    how to results get into original normalised table
    in the same way as I suggested for the adodb method. in creating the dataset for the form, either method is viable. It comes down to personal preference and perhaps some practicalities. A tmp table will be dao, so the normal form filter and sort options will work, but you have more work around managing a temp table to avoid db bloat

  11. #11
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Ajax this is great but the updater is proving a little tough for me to adjust

    the results go in the junction box as shown below if it helps?

    Click image for larger version. 

Name:	results to go here.png 
Views:	57 
Size:	15.4 KB 
ID:	29056


    So it would begin.....


    Code:
    'db.execute "Update tblPaperStudent set [TestScore]=" & .value & " WHERE EmployeeFK=" & .parent.EmployeePK & " AND Role=" & .name
    ????


    Here's the original CrosstabSQL if it helps?

    Code:
    TRANSFORM Avg(qryEnterResults.TestScore) AS AvgOfTestScore
    SELECT qryEnterResults.slotID_FK, qryEnterResults.Firstname, qryEnterResults.surname, qryEnterResults.MathsClass, qryEnterResults.PaperTier, Avg(qryEnterResults.TestScore) AS [Total Of TestScore]
    FROM qryEnterResults
    WHERE (((qryEnterResults.MathsClass)="10x/mm1") AND ((qryEnterResults.[slotID_FK])=7))
    GROUP BY qryEnterResults.slotID_FK, qryEnterResults.Firstname, qryEnterResults.surname, qryEnterResults.MathsClass, qryEnterResults.PaperTier
    ORDER BY qryEnterResults.surname
    PIVOT qryEnterResults.PaperNumber;

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi

    but you have more work around managing a temp table to avoid db bloat
    As Ajax pointed out repeatedly making & deleting temp tables is a cause of db bloat.
    Possible 'solutions' to that are:
    a) change to compact on closde in db settings but I don't like doing this unnecessarily
    b) add code to check the db file size before closing the db & then compact it on close if it exceeds a specified size.
    That code would then be disabled until the next time it is needed
    c) OR instead just APPEND new data to a TEMP table as needed and EMPTY the table on exit

    Quote Originally Posted by andy49 View Post
    So you make a temp table, fill it in but how to results get into original normalised table
    That's not a problem
    To explain I need to give you more info about my database (based on a fictitious school & students):

    Here is a small part of the assessment table:


    Click image for larger version. 

Name:	AssessmentMarksTable.PNG 
Views:	64 
Size:	30.8 KB 
ID:	29059


    This is the data entry form:

    Click image for larger version. 

Name:	AssessmentMarksForm.PNG 
Views:	62 
Size:	113.7 KB 
ID:	29057

    Up to 12 assessments can be entered for each class / course - the example shows 6 assessments for the selected class
    The assessment headers are named lbl1 to lbl12. The marks are entered in textboxes txt1 to txt12
    This is so the captions & existing marks can be obtained 'dynamically' using a crosstab query

    NOTE: This is likely to be 'over the top' if your column headers are fixed

    The following code does part of that process:
    Code:
    Private Sub InitialiseForm()    
    On Error GoTo Err_Handler
    
    
        For I = 1 To 12
            'set values for assessment headers
            Me("Lbl" & I).Caption = Nz(DLookup("AssName", "qryCourseAssessmentTypes", "AssNumber=" & I), "")
            
            'replace & with && and add max mark in brackets
            If Nz(Me("Lbl" & I).Caption, "") <> "" Then
                Me("Lbl" & I).Caption = Replace(Me("Lbl" & I).Caption, "&", "&&")
                
                
                If Nz(DLookup("Active", "qryCourseAssessmentTypes", "AssNumber=" & I), 0) = 0 Then
                    'assessment exists but is inactive - modify header
                    Me("Lbl" & I).Caption = Me("Lbl" & I).Caption & vbNewLine & "   (NOT IN USE)"
                Else
                    'add max mark to header
                    Me("Lbl" & I).Caption = Me("Lbl" & I).Caption & vbNewLine & _
                        "  (" & Nz(DLookup("MaxMark", "qryCourseAssessmentTypes", "AssNumber=" & I), "") & ")   "
                End If
                
            End If
             
            'set text field values
            Me("txt" & I).ControlSource = Nz(DLookup("AssName", "qryCourseAssessmentTypes", "AssNumber=" & I), "")
                    
            'lock field if assessment type not defined(column header is blank)
            If Me("txt" & I).ControlSource = "" Then Me("txt" & I).Locked = True
            
            'lock field if assessment type inactive (not currently in use)
            If Nz(DLookup("Active", "qryCourseAssessmentTypes", "AssNumber=" & I), 0) = 0 Then Me("txt" & I).Locked = True
            
            
            'check whether marks exist - needed to prevent code exiting when running DAvg
            If DCount("Mark", "tblAssessmentMarks", "ClassID= '" & GetClass() & "' And AssNumber=" & I) > 0 Then
                'get average values for each assessment
                Me("txtAvg" & I).Caption = Round(DAvg("Mark", "tblAssessmentMarks", "ClassID= '" & GetClass() & "' And AssNumber=" & I), 1)
            End If
        Next I
        
        UpdateFlag = True
        
        strOrderBy = "ORDER BY tblClassAssessmentMarksTEMP.Surname ASC, tblClassAssessmentMarksTEMP.Forename;"
        strSelection = "List sorted by surname"
        
        GetRecordSource
                
    Exit_Handler:
        Exit Sub
    
    
    Err_Handler:
        'create error message & log
        strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
        PopulateErrorLog
        Resume Exit_Handler
    
    
    End Sub
    Code:
    Private Sub GetRecordSource()    
    On Error GoTo Err_Handler
    
    
    'sets list order
        Me.RecordSource = "SELECT tblClassAssessmentMarksTEMP.* FROM tblClassAssessmentMarksTEMP " & strOrderBy
        
        Parent.lblSortOrder.visible = True
        Parent.lblSortOrder.Caption = strSelection
        Me.Requery
        
    Exit_Handler:
        Exit Sub
    
    
    Err_Handler:
        'create error message & log
        strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
        PopulateErrorLog
        Resume Exit_Handler
    
    
    End Sub
    The following code shows how marks are updated in the source table:

    Code:
    Private Sub UpdateMarks()
      
    On Error GoTo Err_Handler
    
    
    'used in After_Update event for txt1 -> txt12
    
    
        intMark = CInt(Nz(Me("txt" & I), 0))
         
        strPupilID = Me.PupilID
        
        strSQL1 = "UPDATE tblAssessmentMarks" & _
            " SET tblAssessmentMarks.Mark = " & intMark & "" & _
            " WHERE (((tblAssessmentMarks.PupilID)=GetPupilID()) AND ((tblAssessmentMarks.CourseID)=GetCourseID())" & _
            " AND ((tblAssessmentMarks.AssNumber)=" & I & "));"
            
        CurrentDb.Execute strSQL1, dbFailOnError
        
        If Me.Dirty Then Me.Dirty = False
        
        'update average marks & rank order(if done automatically)
        If UpdateFlag = True Then
            RecalculateClassAverageMarksRank
            Me.txtAvg1.Caption = Round(DAvg("Mark", "tblAssessmentMarks", "ClassID= '" & GetClass() & "' And AssNumber=" & I), 1)
            Me.txtAvgCompleted.Requery
            Me.txtAvgTotal.Requery
            Me.txtAvgAvgMarks.Requery
        End If
        
    Exit_Handler:
        Exit Sub
    
    
    Err_Handler:
        'create error message & log
        strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
        PopulateErrorLog
        Resume Exit_Handler
    
    
    End Sub
    This function runs in the after update events for the textboxes e.g.

    Code:
    Private Sub txt1_AfterUpdate()
    
    
        I = 1
        UpdateMarks
        
    End Sub
    By default marks are updated automatically in the source table after each change
    This will also update the total & average marks columns
    However this feature can be disabled if users find it distracting.
    If so, all unsaved marks are updated by clicking a button (or this happens automatically on closing the form)

    HTH

    Colin
    Last edited by isladogs; 06-10-2017 at 11:32 AM. Reason: Added extra info / image

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Here's the original CrosstabSQL if it helps?
    not really - needs the source tables as a source, not a query but in principle if qryEnterResults was a table your PIVOT will create columns based on the value of PaperNumber and unless you change it, the control will have the same name - which is the value of the field you would use in your criteria



    PIVOT qryEnterResults.PaperNumber;

    'db.execute "Update qryEnterResults set [TestScore]=" & .value & " WHERE EmployeeFK=" & .parent.EmployeePK & " AND [PaperNumber]=" & .name

    Don't know about employeeFK - can't work it out from what you have provided

  14. #14
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Still heading into a wall but sense I'm really close to climbing over it. Hope one of you can help me out




    Click image for larger version. 

Name:	mywork.png 
Views:	54 
Size:	19.7 KB 
ID:	29091

    So as you can see with Ajax's help I can add test scores.

    However, currently they aren't saving as the update code wont work.

    I now need to complete a line similar to below and I'm having problems.


    Code:
    Update tblPaperStudent set [TestScore]=13 WHERE SLOTID_PK=7 AND [PaperID_FK] = 6

    The Second part is causing me grief. How can I get the PaperID_FK value from the cross tab query (which has Paper (only1,2 or 3) as the column)

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in your crosstab, how do you get P1,P2 etc?

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 05-11-2017, 07:54 AM
  2. Replies: 2
    Last Post: 04-30-2014, 03:01 PM
  3. Replies: 2
    Last Post: 08-16-2013, 01:36 AM
  4. Crosstab Query Help
    By ksmith in forum Programming
    Replies: 7
    Last Post: 12-01-2010, 07:00 AM
  5. Replies: 1
    Last Post: 07-30-2010, 10:28 AM

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