none
Transferring values from multiple firlds into a single field based on a condition RRS feed

  • Question

  • <style type="text/css">p { margin-bottom: 0.1in; direction: ltr; line-height: 120%; text-align: left; widows: 2; orphans: 2; }</style>

    Hello, I am designing an Access database to record various attributes of truck chassis we receive for our clients. There are several fields that record conditions of certain parts of the chassis (BUMPER, LEFT_ FENDER, TIRES, WINDSHIELD, etc.). The default value of these fields is “GOOD”, i.e. no problems found. However, if the value of one or more of these fields is something different (DENTED, CHIPPED, CRACKED, etc.) I would like to combine the sum of all these “non-good” fields into a single field called “COMMENTS”. I’ve tried using an append query, but to check for multiple conditions on multiple fields with an immediate action after each condition is met is beyond the scope of my coding ability. I need to perform several “If [FIELDX]<>”GOOD” then [COMMENTS]=[COMMENTS] & [FIELDX]” calculations. Any ideas how to do this in SQL view, VBA, Macros, or Expression Builder?

    Sunday, August 9, 2015 10:30 AM

Answers

All replies

  • Sunday, August 9, 2015 1:26 PM
  • I assume that the following is the table structure similar to what you have now:

    Vehicle

    VehicleID

    VehYear

    VehMake

    VehModel

    Windshield

    Bumper

    Fender_Left

    Fender_Right

    Tires

    Comments

    1

    2001

    Ford

    F150

    Cracked

    Good

    Dented

    Good

    Good

    2

    2003

    Chevy

    Tahoe

    Good

    Good

    Good

    Good

    Good

    3

    2002

    Chevy

    Z71

    Good

    Dented

    Dented

    Good

    Poor

    I assume that the following is how you want your comment field to be updated:

    Vehicle w/updated comments

    VehicleID

    VehYear

    VehMake

    VehModel

    Windshield

    Bumper

    Fender_Left

    Fender_Right

    Tires

    Comments

    1

    2001

    Ford

    F150

    Cracked

    Good

    Dented

    Good

    Good

    Cracked, Dented

    2

    2003

    Chevy

    Tahoe

    Good

    Good

    Good

    Good

    Good

    3

    2002

    Chevy

    Z71

    Good

    Dented

    Dented

    Good

    Poor

    Dented, Dented, Poor

    The above is a poor design and limits the information you can have about the condition of the vehicle.

    Below is the table structure you should have:

    Vehicle

    VehicleID

    VehYear

    VehMake

    VehModel

    Comments

    1

    2001

    Ford

    F150

    2

    2003

    Chevy

    Tahoe

    3

    2002

    Chevy

    Z71

    VehicleCondition

    ConditionID

    VehicleID

    Item

    Status

    1

    1

    Windshield

    Cracked

    2

    1

    Bumper

    Good

    3

    1

    Fender_Left

    Dented

    4

    1

    Fender_Right

    Good

    5

    1

    Tires

    Good

    6

    2

    Windshield

    Good

    7

    2

    Bumper

    Good

    8

    2

    Fender_Left

    Good

    9

    2

    Fender_Right

    Good

    10

    2

    Tires

    Good

    11

    3

    Windshield

    Good

    12

    3

    Bumper

    Dented

    13

    3

    Fender_Left

    Dented

    14

    3

    Fender_Right

    Good

    15

    3

    Tires

    Poor


    Sunday, August 9, 2015 1:33 PM
  • In fact, the following is more along the lines of how your vehicle condition table should be structured:

    VehicleCondition

    ConditionID

    VehicleID

    PartID

    StatusID

    1

    1

    1

    3

    2

    1

    2

    1

    3

    1

    3

    2

    4

    1

    4

    1

    5

    1

    5

    1

    6

    2

    1

    1

    7

    2

    2

    1

    8

    2

    3

    1

    9

    2

    4

    1

    10

    2

    5

    1

    11

    3

    1

    1

    12

    3

    2

    2

    13

    3

    3

    2

    14

    3

    4

    1

    15

    3

    5

    5

    Ref_VehiclePart

    PartID

    PartName

    1

    Windshield

    2

    Bumper

    3

    Fender_Left

    4

    Fender_Right

    5

    Tires

    Ref_PartStatus

    StatusID

    StatusName

    1

    Good

    2

    Dented

    3

    Cracked

    4

    Chipped

    5

    Poor


    Sunday, August 9, 2015 1:49 PM
  • To pull it all together, you would employ the following SQL:

    SELECT VehicleCondition.ConditionID, [VehYear] & " " & [VehMake] & ", " & [VehModel] AS Veh, Ref_VehicleParts.PartName, Ref_PartStatus.StatusName
    FROM Ref_VehicleParts INNER JOIN (Ref_PartStatus INNER JOIN (Vehicle INNER JOIN VehicleCondition ON Vehicle.VehicleID = VehicleCondition.VehicleID) ON Ref_PartStatus.StatusID = VehicleCondition.StatusID) ON Ref_VehicleParts.PartID = VehicleCondition.PartID;

    ConditionID

    Veh

    PartName

    StatusName

    1

    2001 Ford, F150

    Windshield

    Cracked

    2

    2001 Ford, F150

    Bumper

    Good

    3

    2001 Ford, F150

    Fender_Left

    Dented

    4

    2001 Ford, F150

    Fender_Right

    Good

    5

    2001 Ford, F150

    Tires

    Good

    6

    2003 Chevy, Tahoe

    Windshield

    Good

    7

    2003 Chevy, Tahoe

    Bumper

    Good

    8

    2003 Chevy, Tahoe

    Fender_Left

    Good

    9

    2003 Chevy, Tahoe

    Fender_Right

    Good

    10

    2003 Chevy, Tahoe

    Tires

    Good

    11

    2002 Chevy, Z71

    Windshield

    Good

    12

    2002 Chevy, Z71

    Bumper

    Dented

    13

    2002 Chevy, Z71

    Fender_Left

    Dented

    14

    2002 Chevy, Z71

    Fender_Right

    Good

    To see the comment field as your post requests:

    Create a standard module and insert the following code:

    Function GetVehicleCondition(VehicleID As Long, Optional Comment As Variant) As Variant
    
        On Error GoTo Err_Process
        
        Dim varReturn As Variant
        Dim strSQL As String
        Dim strDelimiter As String
        Dim rst1 As Recordset
        Dim dbs1 As Database
        
        varReturn = Null
        
        strSQL = "SELECT Ref_VehicleParts.PartName, Ref_PartStatus.StatusName" & vbCrLf & _
        "FROM Ref_VehicleParts INNER JOIN (Ref_PartStatus INNER JOIN VehicleCondition ON Ref_PartStatus.StatusID = VehicleCondition.StatusID) " & _
        "ON Ref_VehicleParts.PartID = VehicleCondition.PartID" & vbCrLf & _
        "WHERE VehicleCondition.StatusID<>1 AND VehicleID=" & VehicleID
    
        Set dbs1 = CurrentDb
        Set rst1 = dbs1.OpenRecordset(strSQL, dbOpenSnapshot)
        
        With rst1
            Do While Not .EOF
                If (Not IsNull(varReturn)) Then
                    strDelimiter = ", "
                End If
                varReturn = varReturn & strDelimiter & .Fields(0) & ": " & .Fields(1)
                .MoveNext
            Loop
            .Close
        End With
        
        If (Not IsMissing(Comment)) Then
            If (Not IsNull(Comment) And Not IsNull(varReturn)) Then
                varReturn = Comment & varReturn
            End If
        End If
        
    Exit_Process:
        Set dbs1 = Nothing
        Set rst1 = Nothing
        GetVehicleCondition = varReturn
        Exit Function
        
    Err_Process:
        MsgBox Err.Number & " " & Err.Description & vbCrLf & "Procedure: GetVehicleCondition", vbExclamation, "Error"
        Resume Exit_Process
        
    End Function

    Using the procedure above, create a query as follows:

    SELECT Vehicle.VehicleID, Vehicle.VehYear, Vehicle.VehMake, Vehicle.VehModel, Vehicle.VehVIN, GetVehicleCondition([VehicleID],[Comment]) AS CommentExt
    FROM Vehicle;

    Query Results:

    Sunday, August 9, 2015 2:35 PM
  • I agree with Stefan.  This sounds like a concatenate issue.  Please see several samples that I posted to a spot on my server.

    https://www.mediafire.com/folder/i72ypj6kns30w/Access_-_Concatenate


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, August 10, 2015 3:19 AM