none
Update Query Question RRS feed

  • Question

  • I am trying to build a field that I can use as a primary key for data I already have in a table.  I want to name the key "EquipmentKey".

    I have the following Query

    UPDATE EquipmentMasterTable SET EquipmentMasterTable.EquipmentKey = [Serial Number] & "_" & [Equipment Type] & "_" & [Equipment Name] & "_" & [Plant] & "_" & [Location];

    It works fine. 

    However, I really want EquipmentMasterTable.EquipmentKey to be =  [Equipment Type] & "_" & [Equipment Name] & "_" & [Serial Number] & "_" & [Plant] & "_" & [Location];

    That Order doesn't work for all the records.  On some, not all, of the records, the concatenation stops after [Equipment Type] & "_" & [Equipment Name] , none of the rest of the fields are concatenated.

    Any help/ideas?

    Saturday, November 21, 2020 9:30 PM

All replies

  • Is the field size large enough to fit all possible concatenated values?>

    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Saturday, November 21, 2020 9:37 PM
  • Any help/ideas?

    Hi tkosel,

    The longer the PrimaryKey (in bytes), the less efficient it will be!

    So make your PrimaryKey as short as possible, e.g. a (complete meaningless) Autonumber.  A next field, as SecondaryKey if you wish, could have the concatenated value of some fields.

    Imb.

    Saturday, November 21, 2020 9:59 PM
  • Hans,

    Thanks as usual for your response.  It must be large enough, as it works when serial number is first.  For verification, I made the field Long Text instead of short text and it doesn't work if serial number is not first.

    Saturday, November 21, 2020 10:06 PM
  • IMB,

    As usual, thanks for your response.  I originally considered using a autonumber primary key, but knowing my users, this will be extremely confusing to them. 

    Example data is below.

    Equipment  type, name, etc can be duplicated and many serial numbers are unknown, so I need to have a way to identify each piece of equipment uniquely.  In the example above, there are different serial numbers, but many of the records have other fields that are different even though the serial numbers are the same.  This isn't a large table, so I don't think the length of the key will be a performance issue.

    If I use an autonumber field, the user will not know which piece of identical equipment the record refers to based on the autonumber value.

    Saturday, November 21, 2020 10:18 PM
  • If I use an autonumber field, the user will not know which piece of identical equipment the record refers to based on the autonumber value.

    Hi tkosel,

    Your table can be just the same, but extended with an additional field Equipment_id.

    If that still means confusion for the user, then hide that Equipment_id field. With forms it is very easy to not display fields.

    In my applications I always use forms for the users to interact, and let them never go to the tables directly, in whatever mode.

    Imb.

    Edit: Users never see/edit/interpret/...  PrimaryKeys. They are absolutely irrelevant for the users.

    • Edited by Imb-hb Saturday, November 21, 2020 10:32 PM Edit:
    Saturday, November 21, 2020 10:28 PM
  • IMB,

    You are right, I am probably going down the wrong path here, but, I would still like to know why the query doesn't work when I use serial number after the first two fields.

    Saturday, November 21, 2020 10:42 PM
  • Agree with previous comments - use an autonumber field as the table primary key.

    Now make a query to include the concatenated field: 

    As already stated, users should never interact with tables or queries - base your form on the query with the concatenated field:

    EquipmentKey: [Equipment Type] & "_" & [Equipment Name] & "_" & [Serial Number] & "_" & [Plant] & "_" & [Location]

    However as you are having issues when one or more fields are null, use the Nz function to handle that. For example:

    EquipmentKey: Nz([Equipment Type],"") & "_" & Nz([Equipment Name],"") & "_" & Nz([Serial Number],"") & "_" & Nz([Plant],"") & "_" & Nz([Location],"")

    NOTE: You may need to tweak that expression slightly to get exactly what you want

     


    • Edited by isladogs52 Saturday, November 21, 2020 10:46 PM
    Saturday, November 21, 2020 10:46 PM
  • All the previous advice is appreciated and welcome!  Thanks a lot.

    However method used aside, the query does not work. You said:

    "As already stated, users should never interact with tables or queries - base your form on the query with the concatenated field:

    EquipmentKey: [Equipment Type] & "_" & [Equipment Name] & "_" & [Serial Number] & "_" & [Plant] & "_" & [Location]"

    I haven't even got to the form yet!  I do not have users interacting with the table or this query.  The whole issue is that the concatenated field (EquipmentKey) I want to build does not work properly.  I would like to ascertain why!  The order you specified above does not work correctly.

    EquipmentKey: [Serial Number] & "_" & [Equipment Type] & "_" & [Equipment Name] & "_" &  [Plant] & "_" & [Location]"

    Does work.  What I am really interested in is why one works, but not the other.

    Saturday, November 21, 2020 11:26 PM
  • As already mentioned, it probably fails when you have a null value for one or more component fields. Although you gave sample data, it didn't show examples where there were issues

    From your description, a null Serial Number may be the issue. 

    That was why I suggested wrapping each field in the Nz function. Did you try that?

    If that still fails, then replace the '&' operator with '+' before each underscore

    EquipmentKey: Nz([Equipment Type],"") + "_" & Nz([Equipment Name],"") + "_" & Nz([Serial Number],"") + "_" & Nz([Plant],"") + "_" & Nz([Location],"")

    or just

    EquipmentKey: [Equipment Type] + "_" & [Equipment Name] + "_" & [Serial Number] + "_" & [Plant] + "_" & [Location]"

    Does that help or make things worse?

    • Edited by isladogs52 Sunday, November 22, 2020 8:48 AM
    Sunday, November 22, 2020 8:35 AM
  • There are no null values in the table.  I will try using the + symbol.
    Sunday, November 22, 2020 3:45 PM
  • You can concatenate values, while suppressing any Nulls, with the following function:

    Public Function ConcatValues(strSeparator As String, ParamArray arrVals())
     
        ' Pass this function a character or characters
        ' to be used as the separator followed by the values to be combined
        ' For example: strFullName =
        ' ConcatValues(" ",FirstName,MiddleName,LastName)
        
        Dim X As Integer, strLine As String
        
        For X = 0 To UBound(arrVals)
            If Not IsNull(arrVals(X)) And Trim(arrVals(X)) <> "" Then
              strLine = strLine & strSeparator & arrVals(X)
            End If
        Next
          
        ' remove leading separator character(s)
        ConcatValues = Mid(strLine, Len(strSeparator) + 1)
     
    End Function

    You can see how it works in the immediate window:

    ? ConcatValues("_","a","b","c",Null,"d")
    a_b_c_d

    The function is one of a number which you'll find demonstrated in Concat.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    However, if you really want to use a natural key, provided that none of the columns contain Nulls, there is no need to create a separate column.  You can simply make all of the columns the composite primary key.  If the table is referenced by other tables in any relationships, such a key would be very cumbersome, and a surrogate autonumber key would be a better solution.  As the subset of columns apparently constitute a candidate key, if you do use a surrogate key, the subset of columns should be contained in a single unique index to prevent invalid duplication of the candidate key.

    Why your attempt at an UPDATE query fails is impossible to say with any certainty without our being able to debug your database.

    Ken Sheridan, Stafford, England

    • Proposed as answer by KHURRAM RAHIM Sunday, November 22, 2020 4:03 PM
    Sunday, November 22, 2020 3:53 PM
  • In addition to the advice already given, if you are going to mix numbers, letters and symbols as part of a field, then that field needs to be a Text Data Type. Make sure the table EquipmentKey field is a text field. It cannot be used as a Primary Key field, so make sure it is not designated as such. Also, if you include that field in a query, you are not going to be able to format it as anything else and can be used for display purposes only. You should be able to edit it in a form.
    Sunday, November 22, 2020 4:06 PM
  • I read fast what others suggested, when I need something like that I create a view using the master table and add to it the new field EquipmentKey column in the view such as :

    item_complete: UCase(Trim([CODE]))+UCase(Trim([DESC]))

    and then use that field to search or display (hidden in the form or visible depends on my needs)

    Samir Ibrahim


    .Net Blog VFP Blog

    18 hours 22 minutes ago