Results 1 to 15 of 15
  1. #1
    Spiken is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    5

    Comparing values between fields


    Hi,

    Would very much appreciate help here.
    I have a table that contains two fields; “Audio” and “Delivered audio”.

    The value in these two fields are formatted in the same way (ex: EN,PL,HU etc).
    I want to compare these two fields and provide a status in a third field, depending on if the language codes are the same in both fields.

    Problem is I can’t do a simple “compare” as the order of the language codes can differ between the two fields, so I need to compare each two letter code. Both fields can contain 10+ language codes, all separated by a comma and can be empty.

    Preferrably I would like to run an update SQL but can’t get my head around how to do it.
    Note that the table currently contains about 200k rows so can’t be “too heavy” as I noticed Access sometimes can’t cope with larger updates.

    Examples:
    Row 1 - Audio: “EN, PL” - Delivered audio: “EN” - Status: Should say “Incomplete”
    Row 2 - Audio: “EN, HU, CR, BG” - Delivered audio: “EN, BG, MK, SL, HU, CR” - Status: Should say “Completed”
    Row 3: - Audio: “EN, PL, PT” - Delivered audio: “EN, PT, SL, SK” - Status: Should say “Incomplete”

    Of course, if possible it would be nice to display which languages are missing in the status field as well, but only nice2have.

    Highly appreciate any suggestions.

    Best

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    This is a text field with a comma separated string, not a multi-value field?

    This is bad data structure. Will probably need a VBA custom function.
    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.

  3. #3
    Spiken is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    5
    Unfortunately yes
    The Audio data is from a report that is imported and since the language code can be pretty much any language it is hard to define the possible language codes as different values in a multi-valued field. The data in the “Delivered Audio” is from yet another report that is imported and cleaned to at least display the languages in the same format (two letter code).

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    table that contains two fields; “Audio” and “Delivered audio”.
    What exactly is the table layout?

    Row 1 - Audio: “EN, PL” - Delivered audio: “EN” - Status: Should say “Incomplete”
    What does this mean --row1?

    How does the table relate to the "row"? I would have expected records --is that what you mean?

    If you provide some sample data, I will try to get you a function.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Row 1 - Audio: “EN, PL” - Delivered audio: “EN” - Status: Should say “Incomplete”
    Row 2 - Audio: “EN, HU, CR, BG” - Delivered audio: “EN, BG, MK, SL, HU, CR” - Status: Should say “Completed”
    Row 3: - Audio: “EN, PL, PT” - Delivered audio: “EN, PT, SL, SK” - Status: Should say “Incomplete”
    At first I thought "I can't follow the logic that dictates why the resulting status should be what you say". Now I'm thinking that if all the values on the left are in the field on the right, it is complete. If not, then it is incomplete.

    There are situations when provided data does not meet normal db requirements, so I don't have a problem with what you're trying to do. I don't see a query solution though; only code. Looping through a recordset, putting the left values into an array, looping over them and checking for each value in the other field should work. If there is just one left side value that isn't found, I guess the value to be updated in the table is "Incomplete".
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Spiken is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    5
    Apologies orange, yes of course records

    Table contains a total of 30-ish fields with different data types but these two mentioned above are both short text type.

  7. #7
    Spiken is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    5
    Yes correct Micron; if all values in the field “Audio” exists in the field “Delivered audio” it is “completed”.

    My initial thought was to have a temp table populated with the record ID from the first table and then split up all the languages on separate records, and then run a query looking for the value in the delivered audio field etc... however, we’re talking 10 years ago (at least) since I spent time “coding” anything more complex than an if in Access :/

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, if you upload something to work with, I think that I or someone else will be able to figure it out. Best would be a db of some sort - I think just the table in question, with a good sample of records. If you want to drive this from a form, then perhaps copy your db, compact and zip it and upload it here. You probably don't need to include all forms/reports/tables, just the form you want to run this from (along with the necessary table(s) of course).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Can use table builder in the Advanced post editor to provide sample data directly in post.

    Code could be as simple as:
    Code:
    Function IsComplete(strA As String, strDA As String) As Boolean
    Dim ary() As String, x As Integer, intC As Integer
    ary() = Split(strA, ", ")
    For x = 0 To UBound(ary)
        If InStr(strDA, ary(x)) > 0 Then intC = intC + 1
    Next
    If intC = UBound(ary) + 1 Then IsComplete = True
    End Function
    Call function in query:
    SELECT Audio, DeliveredAudio, IsComplete([Audio],[DeliveredAudio]) AS IsComplete FROM tablename;
    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.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I made some assumptions about your data and posted some materials.

    Subsequent review showed it was not consistent ---so I removed things.
    I notice that June has offered a function, so you should be good to go.
    Good stuff June -works well.
    Last edited by orange; 01-20-2022 at 06:41 AM. Reason: removed my inconsistent code

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What I found in my dabbling is that you need 2 loops with 1 array each. Taking each element in the outer loop, check for it in the inner loop. Add 1 to counter if found. If the counter = Ubound of the second array +1 then all elements of the 1st array are found in the 2nd array, meaning complete. I decided to use a query to run the function.
    @June7; Not stated if that's air code or not, but if it works my hat's off to you.

    SQL
    Code:
    SELECT tblAudio.Audio, tblAudio.DeliveredAudio, updatestatus([audio],[DeliveredAudio]) AS Status
    FROM tblAudio;
    SINGLE FUNCTION
    Code:
    Function UpdateStatus(strAudio As String, strDeliveredAudio As String) As String
    Dim aryAudio() As String, aryDeliveredAudio() As String
    Dim i As Integer, n As Integer, c As Integer
    
    aryAudio = Split(strAudio, ",")
    aryDeliveredAudio = Split(strDeliveredAudio, ",")
    
    If UBound(aryAudio) > UBound(aryDeliveredAudio) Then
       'Incomplete - audio exceeds delivered audio
       UpdateStatus = "Incomplete"
       Exit Function
    End If
    
    For i = 0 To UBound(aryAudio)
       For n = 0 To UBound(aryDeliveredAudio)
          If aryAudio(i) = aryDeliveredAudio(n) Then c = c + 1
       Next
    Next
    
    If c = UBound(aryAudio) + 1 Then 'add 1 to UBound because it's zero based
       UpdateStatus = "Complete"
    Else
       UpdateStatus = "Incomplete"
    End If
    
    End Function
    Seems to work, but I only used the 3 records from original post.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Yes, tested with given sample data and worked. Revised to return list of missing elements if incomplete.
    Code:
    Function Missing(strA As String, strDA As String) As String
    Dim strL As String, ary() As String, x As Integer
    ary() = Split(strA, ", ")
    For x = 0 To UBound(ary)
        If InStr(strDA, ary(x)) = 0 Then
            strL = strL & ary(x) & ","
        End If
    Next
    If strL = "" Then
        Missing = "Complete"
    Else
        Missing = "Incomplete: " & Left(strL, Len(strL) - 1)
    End If
    End Function
    SELECT tblAudio.Audio, tblAudio.DeliveredAudio, Missing([Audio],[DeliveredAudio]) AS Status FROM tblAudio;
    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.

  13. #13
    Spiken is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    5
    This is absolutely amazing! So hugely appreciated, thank you all!
    I attached a test sample from the table.

    Ultimately I would like to perform this update on a button click.
    So the code you have provided would be inserted in the onclick sub?
    Where do I put the SQL vs the vb code in this example?

    (Sorry for my lack of knowledge here)
    Attached Files Attached Files

  14. #14
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure whom you're addressing.
    I think the sql you'd need would be for an UPDATE query as SELECT that uses a calculated field will only return a non editable set of records. So more like

    UPDATE tblAudio SET tblAudio.Status = updatestatus([audio],[DeliveredAudio]);

    If using my solution, the code would go in a standard module (not code behind the form). The button click code would be behind the form and would just execute the UPDATE query. You can create that by copying the posted sql, opening a new query in sql view, pasting and saving as qryAudio or whatever you wish to name it. Button click code could be like
    Code:
    Private Sub Command0_Click()
    Dim db As DAO.Database
    
    Set db = CurrentDb
    db.Execute "qryAudio", dbFailOnError
    set db = Nothing
    
    End Sub
    As long as the query doesn't fail, it should be OK. Just thinking now that it probably should be tested where Audio field contains nulls.

    EDIT - always properly name your controls. Normally I would not code for myself using the Access given names like Command0. I got lazy because I wasn't sure whom you were addressing, and coffee is getting cold as I play.
    Also, 3 records doesn't really indicate performance vs 200,000 records if that's what you typically have. I guess I'd have to download the latest db if it has that many records in order to find out.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Whichever Function code you want to use, put it in a general module.

    Build the SQL statement to deal with null fields:

    UPDATE example SET [audio status] = Missing(Nz([Audio],""), Nz([Delivered audio],""))

    If you want to update [Subs status] in same action:

    UPDATE example SET [audio status] = Missing(Nz([Audio],""), Nz([Delivered audio],"")), [Subs status] = Missing(Nz([Subs],""), Nz([Delivered subs],""))

    Instead of a query object, SQL can be constructed in VBA:
    Code:
    Private Sub Command0_Click()
    CurrentDb.Execute "UPDATE example SET [audio status] = Missing(Nz([Audio],''), Nz([Delivered audio],'')), [Subs status] = Missing(Nz([Subs],''), Nz([Delivered subs],''))", dbFailOnError
    End Sub

    Strongly advise not to use spaces in naming convention.
    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.

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

Similar Threads

  1. Comparing values in Access
    By mikesal57 in forum Access
    Replies: 18
    Last Post: 10-18-2021, 02:15 PM
  2. If statement comparing 2 values
    By Ekhart in forum Programming
    Replies: 3
    Last Post: 09-15-2016, 11:16 AM
  3. Replies: 1
    Last Post: 11-11-2015, 04:25 PM
  4. Comparing two fields
    By cbende2 in forum Access
    Replies: 3
    Last Post: 10-02-2015, 09:09 AM
  5. Comparing positive to negative values.
    By rubberducky in forum Access
    Replies: 1
    Last Post: 01-20-2014, 04:44 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