locked
Help: Comparing reports in the same field RRS feed

  • Question

  • Hi All

    I have a table like below in which field3 is the calculating result from field1 and field2

    Field1 Field2 Field3
    123 PK51 123A
    123 PK55 123B
    123 PK51 123A
    123 PK56 123C
    123 PK52 123D
    124 PK52 124A
    124 PK54 124B
    124 PK54 124B
    124 PK55 124C
    125 PK58 125A
    125 PK55 125B
    125 PK59 125C

    The rule of calculation is: Firstly the new record in field1 that is different others will offer one result in field3 is always field1.A. In case record of field1 is the same, it will compare the record in field2. Base on that it will have the result in field3 as like above table. On excel I can do it but on access I really got stuck. no idea for this point. I hope everyone can help me solve this or instruct me how to do well. Thank you in advance.

    Wednesday, May 30, 2018 11:42 AM

Answers

  • You do not put any code in the Field1 Control's AfterUpdate event procedure,  The SQL statement is a query, so you open the query designer, switch to SQL view and paste in the complete SQL statement in place of what is there already.  Then save the query under a suitable name.

    Test the query by opening it directly from the navigation pane.

    As regards the unbound control in the form, you substitute the name of the query for NameOfQueryGoesHere and put the expression in the control's ControlSource property, preceded by an = sign.  Field1 is presumably of text data type, as you've wrapped its value in literal quotes characters.

    Understand that this does not save the computed value to a column in the table, it returns the value at runtime on the basis of the other data in the table.  Consequently there is no redundancy and no risk of update anomalies.

    Finally, be sure that all the columns in the table are apprpriately indexed to improve performance.

    Ken Sheridan, Stafford, England

    • Marked as answer by Nghi Trinh Friday, June 1, 2018 4:36 AM
    Thursday, May 31, 2018 3:51 PM
  • The control's AfterUpdate event procedure is too early; the row has mot been committed to the table at this stage.  The form's AfterUpdate procedure should be used.

    Ken Sheridan, Stafford, England

    • Marked as answer by Nghi Trinh Wednesday, June 20, 2018 3:53 PM
    Wednesday, June 20, 2018 10:44 AM
  • Firstly you should ensure that all of the columns involved in the routine are appropriately indexed.  Good indexing is essential to good performance.  Your infrastructure must also be a high quality wired network.  The bottom line, however, is that you can only do what you want by means of procedural code, rather than by a more efficient set operation.

    Ken Sheridan, Stafford, England

    • Marked as answer by Nghi Trinh Thursday, July 19, 2018 4:33 AM
    Wednesday, July 18, 2018 3:49 PM

All replies

  • Well, the biggest problem is that in Access we work on sets. They have no order. So for your calculation: What is your order criteria?

    Without further columns this problem cannot be solved in a relational database system.

    Wednesday, May 30, 2018 1:44 PM
  • As Stefan has pointed out a set has no order.  Unlike in an Excel worksheet the order in which you see the rows in a table's datasheet is meaningless, and can easily be changed.  In a relational database table rows can only be given an ordinal value on the basis of sortable data values.  If for instance you were to add an autonumber column named MyID to the table as below:

    MyID    Field1    Field2
    1         123        PK51
    2         123        PK55
    3         123        PK51
    4         123        PK56
    5         123        PK52
    6         124        PK52
    7         124        PK54
    8         124        PK54
    9         124        PK55
    10       125        PK58
    11       125        PK55
    12       125        PK59

    The following query, which uses not only the equality of Field2 values per Field1 value, but also the alphabetic sort order of the values in the Field2 column, along with the distinct values in the MyID column:


    SELECT T1.MyID, T1.Field1,T1.Field2, Field1 & CHR(64+
       (SELECT COUNT(*)+1
         FROM (SELECT DISTINCT Field1,Field2
                      FROM YourTable) AS T2
                      WHERE T2.Field1 = T1.Field1
                      AND T2.Field2 < T1.Field2)) AS Field3
    FROM YourTable AS T1
    ORDER BY T1.MyID;

    would return the following result table:

    MyID    Field1    Field2    Field3
    1         123        PK51     123A
    2         123        PK55     123C
    3         123        PK51     123A
    4         123        PK56     123D
    5         123        PK52     123B
    6         124        PK52     124A
    7         124        PK54     124B
    8         124        PK54     124B
    9         124        PK55     124C
    10       125        PK58     125B
    11       125        PK55     125A
    12       125        PK59     125C

    You'll see that in this case the sort order of the values in Field1 has been used to determine the suffix letter per Field1 value in Field3.  This can be seen more easily by changing query's ORDER BY clause as follows:

    SELECT T1.MyID, T1.Field1,T1.Field2, Field1 & CHR(64+
       (SELECT COUNT(*)+1
         FROM (SELECT DISTINCT Field1,Field2
                      FROM YourTable) AS T2
                      WHERE T2.Field1 = T1.Field1
                      AND T2.Field2 < T1.Field2)) AS Field3
    FROM YourTable AS T1
    ORDER BY T1.Field1,T1.Field2;

    which now returns:

    MyID    Field1    Field2    Field3
    3         123        PK51     123A
    1         123        PK51     123A
    5         123        PK52     123B
    2         123        PK55     123C
    4         123        PK56     123D
    6         124        PK52     124A
    8         124        PK54     124B
    7         124        PK54     124B
    9         124        PK55     124C
    11       125        PK55     125A
    10       125        PK58     125B
    12       125        PK59     125C


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Wednesday, May 30, 2018 5:26 PM Typo corrected.
    Wednesday, May 30, 2018 5:17 PM
  • Thank Ken Sheridan so much. Of course, my table has auto-number ID column as your mention. Actually I build one Form to input data into this table. Field3 will be calculated from value of field1 and field2 as I mentioned. So do you have any idea to help me to write VBA code for this case. By the way please explain what does T1, T2 mean? T1 is name of table has field1, field2 and field3. T2 is temporary table, "Yourtable" you mentioned in code is one other table? . I truly hope this issue can be solved because it help us so much during controlling right data. Thank you in advance
    Thursday, May 31, 2018 2:17 AM
  • T1 and T2 are aliases for the two instances of the same table, called YourTable in my example, and should not be changed.  For YourTable you should substitute the actual name of your table.

    If you are saying you want to store the Field3 value in a column in the table, then I would strongly advise against any attempt to do this.  It would introduce redundancy into the table, and almost certainly result in update anomalies unless you completely renumbered all rows with the same Field1 value as the row being inserted.

    As the query does not return an updatable recordset you cannot use it as a form's RecordSource property.   You would probably have to return the Field3 value in an unbound control whose ControlSource property looks up the value in the query:

        =DLookup("Field3", "NameOfQueryGoesHere", "Field1 = " & [Field1])

    I'm assuming that Field1 is a number data type.  If it is a text data type the value will need to be wrapped in literal quotes characters:

        =DLookup("Field3", "NameOfQueryGoesHere", "Field1 = """ & [Field1] & """")

    Domain functions are notoriously slow, however, so you might well find that performance in the form is inhibited, particularly if the form is in continuous forms or datasheet view.  

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Thursday, May 31, 2018 10:43 AM Typo corrected.
    Thursday, May 31, 2018 10:40 AM
  • Thank Ken. In order to fully understand your instruction, Let me summarize my thought after reading.

    Firstly, I will add code in after update of field1 and field2:

    SELECT T1.MyID, T1.Field1,T1.Field2, Field1 & CHR(64+
       (SELECT COUNT(*)+1
         FROM (SELECT DISTINCT Field1,Field2
                      FROM Tblmixregister) AS T2
                      WHERE T2.Field1 = T1.Field1
                      AND T2.Field2 < T1.Field2)) AS Field3
    FROM Tblmixregister AS T1
    ORDER BY T1.Field1,T1.Field2.

    Field3 will get the result as above table. And the field3 value want to display on subform (continuous forms), I just only add one an unbound control and attach by Dlookup function:

    DLookup("Field3", "NameOfQueryGoesHere", "Field1 = """ & [Field1] & """")

    So here "NameofQueryGoesHere" is built from TblMixregister when field3 value already is available.

    Thank you so much

    Best Regards.

    Thursday, May 31, 2018 3:28 PM
  • You do not put any code in the Field1 Control's AfterUpdate event procedure,  The SQL statement is a query, so you open the query designer, switch to SQL view and paste in the complete SQL statement in place of what is there already.  Then save the query under a suitable name.

    Test the query by opening it directly from the navigation pane.

    As regards the unbound control in the form, you substitute the name of the query for NameOfQueryGoesHere and put the expression in the control's ControlSource property, preceded by an = sign.  Field1 is presumably of text data type, as you've wrapped its value in literal quotes characters.

    Understand that this does not save the computed value to a column in the table, it returns the value at runtime on the basis of the other data in the table.  Consequently there is no redundancy and no risk of update anomalies.

    Finally, be sure that all the columns in the table are apprpriately indexed to improve performance.

    Ken Sheridan, Stafford, England

    • Marked as answer by Nghi Trinh Friday, June 1, 2018 4:36 AM
    Thursday, May 31, 2018 3:51 PM
  • Thank you so muck. Let me try and feedback you soon.
    Thursday, May 31, 2018 4:17 PM
  • Well done. It is amazing. That helps us input data correctly. Thanks all guys.

    Best regards.

    Friday, June 1, 2018 4:36 AM
  • Dear Ken

    I already applied your method. But some record run incorrectly. You can see record 41. The letter must be "A" instead of "B" as the below pic. Some thing is in  AND T2.Field2 < T1.Field2)) AS Field3. You can fix this issue for me. Thank in advance.

    This is second case: at record 73. the letter must be "A" but it actually is B


    • Edited by Nghi Trinh Monday, June 18, 2018 7:04 AM Add pic
    Monday, June 18, 2018 7:00 AM
  • My understanding of your original post was that the suffix letter in the computed column is determined by the values of Field1 and Field2 being equal, with the suffix letter incrementing alphabetically per subset on the basis of the different values in Field2.  As I pointed out earlier, that can only be done on the basis of the sort order of field2, a fundamental property of a set being that it has no implicit order.

    The rows with the BJ23-727 value in Field1 have a value of 180612PK10 in Field2 as the first value in the distinct sort order, and consequently have a suffix of A in the computed column.  The row which you have cited has a value of 180612PK11 in Field2, which is next in the sort order, and consequently has a suffix of B in the computed column on the basis of my first sentence above.

    The same applies in the case of the rows with a Field1 value of BJ24-679.

    From the result table which you have posted you have clearly not included the ORDER BY clause which was present in the SQL statement you posted earlier.  If you do so the result table would be differently ordered and the logical basis behind values of the computed column would the more apparent.

    If you are now saying that the sort order of Field2 is not an appropriate determinant of how the suffix value increments alphabetically per subset, then the logic behind my query would not apply of course, and all bets are off.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, June 18, 2018 12:38 PM Typo correceted.
    Monday, June 18, 2018 12:36 PM
  • So... In this case, what should I do now? Please let me know how to do it! actually I cannot imagine that in fiield2 we don't have any order rule. It just considers to first appear. I hope you already understand my point now and help me to solve this gap. I am not strong in SQL statement in access. Looking forward to hearing your instruction.
    Monday, June 18, 2018 1:17 PM
  • Unless it is absolutely necessary that the suffix reflects the order in which the rows were inserted into the table, rather than the alphabetic order of the Field2 values, I don't see any real need to do anything.  In most contexts the order in which rows were inserted is immaterial to the information content of a table.

    Ken Sheridan, Stafford, England

    Monday, June 18, 2018 4:16 PM
  • Thank Mr.Ken so much.

    Due to the record is inputted by different shift and production date. You  said that "The rows with the BJ23-727 value in Field1 have a value of 180612PK10 in Field2 as the first value in the distinct sort order, and consequently have a suffix of A in the computed column". But the problem is 180612PK11 is inputted first. When the value in field1 change, it doesn't care the value of field2, it must have a suffix of A in the computed column. Otherwise it consequently consider to value of field2 to determine the next letter A or B or C...How I can fix at row 41, 42 the value of field3 is changed to A and at row 43, 44,45 the value of Field3 is B. that is my expectation. Please help me one again.

    Tuesday, June 19, 2018 3:14 AM
  • I don't think you'll be able to do this as a query.  You will have to add Field 3 to the Tblmixregister  table and populate it by executing the following code.  I've tested this against the sample data you posted in your original post, and it seems to work:


        Const conSQL = "SELECT * FROM Tblmixregister ORDER BY MyID"
        Dim rst As DAO.Recordset
        Dim strSuffix As String
        Dim strCriteria As String
        Dim varField3 As Variant
        
        CurrentDb.Execute "UPDATE Tblmixregister SET Field3 = NULL", dbFailOnError
        
        Set rst = CurrentDb.OpenRecordset(conSQL)
        
        With rst
            Do While Not .EOF
                strCriteria = "Field1 = " & .Fields("Field1") & _
                    " And Field2 = """ & .Fields("Field2") & _
                    """ And MyID < " & .Fields("MyID")
                    
                varField3 = DMax("Field3", "Tblmixregister", strCriteria)
                    
                If Not IsNull(varField3) Then
                    .Edit
                    .Fields("Field3") = varField3
                    .Update
                Else
                    strCriteria = "Field1 = " & .Fields("Field1") & _
                        " And MyID < " & .Fields("MyID")
                            
                    varField3 = DMax("Field3", "Tblmixregister", strCriteria)
                        
                    .Edit
                    If Not IsNull(varField3) Then
                        .Fields("Field3") = Left(varField3, Len(varField3) - 1) & Chr(Asc(Right(varField3, 1)) + 1)
                     Else
                        .Fields("Field3") = .Fields("Field1") & "A"
                    End If
                    .Update
                End If
                .MoveNext
            Loop
        End With

    As and when new rows are inserted into the table the code can be run to update the table.  It does rely on the MyID column, which I assume is an autonumber, reflecting the order in which the rows were inserted into the table.  While this should usually be the case, you cannot absolutely rely on it, as an autonumber is designed to guarantee distinct values, not necessarily sequential values.  It would be better to include a DateTimeStamp column in the table and set its DefaultValue property to Now() in the table design.  This column can then be used in the code in place of the MyID column.

    The code assumes that Field1 is a number data type, not text.

    Ken Sheridan, Stafford, England

    Tuesday, June 19, 2018 11:26 AM
  • Thank Mr.Ken Sheridan so much for your reply. I deeply appreciate your support. I already tested your code but unfortunately my Field1 in TABLE is text not number data type as your mention. The error occurs at "   varField3 = DMax("Field3", "Tblmixregister", strCriteria)". I feel this is quite complex for me.

    Here my database. Please help me to check how I can fix this point. You can open my form FrmMixRegister and select filter to open record.

    Click here

    • Edited by Nghi Trinh Tuesday, June 19, 2018 4:44 PM Add database
    Tuesday, June 19, 2018 4:22 PM
  • You simply have to delimit the value in literal quotes characters in the code, which will thus be:

        Const conSQL = "SELECT * FROM Tblmixregister ORDER BY MyID"
        Dim rst As DAO.Recordset
        Dim strSuffix As String
        Dim strCriteria As String
        Dim varField3 As Variant
        
        CurrentDb.Execute "UPDATE Tblmixregister SET Field3 = NULL", dbFailOnError
        
        Set rst = CurrentDb.OpenRecordset(conSQL)
        
        With rst
            Do While Not .EOF
                strCriteria = "Field1 = """ & .Fields("Field1") & _
                    """ And Field2 = """ & .Fields("Field2") & _
                    """ And MyID < " & .Fields("MyID")
                    
                varField3 = DMax("Field3", "Tblmixregister", strCriteria)
                    
                If Not IsNull(varField3) Then
                    .Edit
                    .Fields("Field3") = varField3
                    .Update
                Else
                    strCriteria = "Field1 = """ & .Fields("Field1") & _
                        """ And MyID < " & .Fields("MyID")
                            
                    varField3 = DMax("Field3", "Tblmixregister", strCriteria)
                        
                    .Edit
                    If Not IsNull(varField3) Then
                        .Fields("Field3") = Left(varField3, Len(varField3) - 1) & Chr(Asc(Right(varField3, 1)) + 1)
                     Else
                        .Fields("Field3") = .Fields("Field1") & "A"
                    End If
                    .Update
                End If
                .MoveNext
            Loop
        End With

    Ken Sheridan, Stafford, England

    Tuesday, June 19, 2018 4:59 PM
  • Thank Mr.Ken.

    I drop this code in after update of field2. When field1 and 2 is updated, field3 can not update immediately. It raises the message as below picture. When I click on copy to Clipboard the code start to perform. The value is updated on field3. How to remove this message.

    Wednesday, June 20, 2018 5:43 AM
  • The control's AfterUpdate event procedure is too early; the row has mot been committed to the table at this stage.  The form's AfterUpdate procedure should be used.

    Ken Sheridan, Stafford, England

    • Marked as answer by Nghi Trinh Wednesday, June 20, 2018 3:53 PM
    Wednesday, June 20, 2018 10:44 AM
  • Dear Mr.Ken

    I really love you so much. I would like to send a big thank to you. First of all I will apply your code in my database then I hope I can understand deeply the code you write. I will discover it by myself. The result will inform you soonest when the application is applied in my department.

    Wednesday, June 20, 2018 4:02 PM
  • Hi Mr.Ken

    My application has applied for a while. The code run well as my expectation. But one other big problem occur when running code. The speed is quite slow, every completed record is around 3 to 5 seconds. It is really inconvenient for all users. And one more point, it makes the backend (database) increasing size so fast. only several day it can reach 700Mb. How can i prevent and solve it.

    Anyway thank you so much again for your help.

    Best regards

    TVN

    Wednesday, July 18, 2018 2:45 PM
  • Firstly you should ensure that all of the columns involved in the routine are appropriately indexed.  Good indexing is essential to good performance.  Your infrastructure must also be a high quality wired network.  The bottom line, however, is that you can only do what you want by means of procedural code, rather than by a more efficient set operation.

    Ken Sheridan, Stafford, England

    • Marked as answer by Nghi Trinh Thursday, July 19, 2018 4:33 AM
    Wednesday, July 18, 2018 3:49 PM
  • Hi Ken

    How to narrow the record in above code, the code can be tidy more to run faster. If the record run from first to end then the system will be slow and increase size of database.

    i already check network, it is normal everywhere. I am afraid that if i can not control well the system will interrupt on some day. Speed is also important because the number of record is huge. 



    Can i disable this code: CurrentDb.Execute "UPDATE Tblmixregister SET Field3 = NULL", dbFailOnError because when error all fied3 will is null.
    • Edited by Nghi Trinh Thursday, July 19, 2018 9:21 AM
    Thursday, July 19, 2018 6:01 AM
  • Can i disable this code: CurrentDb.Execute "UPDATE Tblmixregister SET Field3 = NULL", dbFailOnError because when error all fied3 will is null.
    I don't think that is possible, or that it would help, as it is a simple set operation, so will execute very quickly.  It is the iteration through all rows in the table which takes the time.  As the row inserted could result in the value of Field3 being changed in any of the rows I don't see any way of avoiding that.

    Ken Sheridan, Stafford, England

    Thursday, July 19, 2018 3:04 PM
  • Hi Ken

    Actually I already tried. I just select 3 fields that I need to calculation from Table instead select all fields. Parallel I disable  this code: CurrentDb.Execute "UPDATE Tblmixregister SET Field3 = NULL", dbFailOnError. And one more I set condition for rst by field name is Lock = No. This will help to be narrow record. The result is better. Waiting time is reduce from 30s to 5s. I don't know It is the best way or not but the calculating speed is as faster as possible.

    Thanks

    Friday, July 20, 2018 2:40 PM
  • Hi Ken

    Just inform you that my application runs quite well excluding above code.  it runs slow because the record will be bigger day by day. I had tried to transfer that table to SQL server but even it can not run(i will open one topic on forum). i am finding one other solution that create one query as you mentioned before with above that code and using Dlookup to display the value. How do you thing for this point. Please give me your advice. Thank you in advance

    Best regards

    TVN

    Tuesday, September 4, 2018 6:25 AM