Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    rfigueroa1976 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    34

    Labeling Data?

    Not sure what to call it but, what i am trying to do is populate a field with something if the other field is equal to something...



    I need to have a cell return a value depending on the value of another cell...
    The cell i need to look at is Rootcause issues for failures: [dbo_NCR_RootCauses].[Description]
    The possible outcomes are: Carrier*, Shipper*, Consignee*, Unforeseen*, etc* (Each one can have several versions hence the "*", for example: Carrier - Spill, Carrier - Dispatch Error, Carrier - Breakdown, etc)

    What I'm trying to do is if the cell starts with "Carrier*", the cell next to it will be labeled with "1-Carrier", if the cell starts with "Shipper*", the cell next to it will be labeled with "2-Shipper", if... Etc.

    In excel, the formula would be =If(a2="Carrier*", "1-Carrier", If(a2="Shipper*", "2-Shipper", If(a2="Consignee*", "2-Consignee", "Other")

    I would assume that the formula in Access would be similar: Fault Hierarchy: =if([dbo_NCR_RootCauses].[Description]="Carrier*, "1-Carrier", if([dbo_NCR_RootCauses].[Description]="Shipper*, "2-Shipper", if([dbo_NCR_RootCauses].[Description]="Consignee*, "3-Consignee", "Other")

    Please let me know how the formula in access needs to be written out.

    Thanks!

    Click image for larger version. 

Name:	Rootcause Hierarchy.jpg 
Views:	27 
Size:	217.1 KB 
ID:	31305

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Build a lookup table. This table will translate 1 name for another

    tLookupName
    FindName,Caption
    Robert, Bob
    Bobby, Bob
    Rob, Bob
    Robby, Bob

    join the lookup table to your data table (outer join) and it will replace 1 field for another.
    example above: conntect tLookup.FindName to your data field tData.[name] and bring down tData.[Caption] into the query.

    you can do this with partials too.
    Q1 is a list with the partial Left([field],4)
    Q2 joins Q1 to the lookup table.

  3. #3
    rfigueroa1976 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    34
    Is there a way to do it via a formula? I'd rather not build a table because my company is really quick to delete anything that wasn't built by a DB admin. If it starts with X label it Y?

  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    How about Select Case? If you need to use this in a query, put it in a UDF that can be called by the query.

    Code:
    Select Case Left([Root Cause],7)
        Case "Carrier"
            [Fault Hierarchy] = "1-Carrier"
        Case "Shipper"
            [Fault Hierarchy] = "2-Shipper"
        Case "Consign"
            [Fault Hierarchy] = "3-Consignee"
        Case Else
            [Fault Hierarchy] = "Other"
    End Select

  5. #5
    rfigueroa1976 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    34
    Sorry. I'm new to the Access world. That looks like SQL talk. One and the same? How to would I Put that into an access formula?

  6. #6
    thewatcher101 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    Los Angeles
    Posts
    5
    Click image for larger version. 

Name:	Capture.PNG 
Views:	24 
Size:	5.4 KB 
ID:	31308

    right click, on tab and click on SQL View

  7. #7
    rfigueroa1976 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    34
    Still not able to figure it out.
    Attached Thumbnails Attached Thumbnails Rootcause Hierarchy SQL.jpg  

  8. #8
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Do CTRL-G
    Add module
    Put this in the module:

    Code:
    Option Compare Database
    Option Explicit
    
    Public Function fcnFindFault(arg as string) as string
    Select Case Left(arg,7)    'only look at first 7 characters
        Case "Carrier"
            fcnFindFault = "1-Carrier"
        Case "Shipper"
            fcnFindFault = "2-Shipper"
        Case "Consign"
            fcnFindFault = "3-Consignee"
        Case "Unforse"
            fcnFindFault = "4-Unforseen" 
        Case Else
            fcnFindFault = "5-Other"
    End Select
    End Function
    Then in the query call the function:
    .
    .
    dbo_NCR_RootCauses.Description as [Root Cause],
    fcnFindFault([dbo_NCR_RootCauses].[Description]) as [Fault Hierarchy],
    dbo_NCR_preventativeAction.Description as [Preventative Action]
    .
    .
    Last edited by davegri; 11-17-2017 at 12:13 PM. Reason: more

  9. #9
    rfigueroa1976 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    34
    I tried the CTRL + G but it just gives me a gray screen... I hate to be the guy that doesn't take advice but, I'm really hoping for something simple. I was hoping a IIF formula would work... I tried this but for some reason, it's coming back blank:

    Fault Hierarchy: IIf([dbo_NCR_RootCauses].[Description]="Carrier*", "1-Carrier", IIf([dbo_NCR_RootCauses].[Description]="Shipper*","2-Shipper", "Other")

    Please let me know if you see something that is not right on this formula. Thanks!

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you did that expression in query builder, should not have been able to run or save the query because of missing ) at the end. Should have gotten error message.

    Is there more than one field named [Description]? This is a reserved word. Should not use reserved words as names. http://allenbrowne.com/AppIssueBadWord.html

    If you have only a few conversions, an expression should work. However, you need LIKE operator with wildcard. Advise no space in the field name.

    FaultHierarchy: IIf([Description] LIKE "Carrier*", "1-Carrier", IIf([Description] LIKE "Shipper*", "2-Shipper", "Other"))



    Other replies were trying to guide you to build a VBA procedure that would be called by query. The suggested SELECT CASE code does not belong in a query, it would be in a VBA code module.
    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.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @rfigueroa1976,

    You've got to forget anything you know about Excel; Access and Excel are tow completely different animals!! Access tables DO NOT have "Cells"; even though an Access table LOOKS like a worksheet, they are NOT the same.
    Excel has Rows and Columns,
    Access has Records and Fields. An Access table does not have "Cells".

    The image (Rootcause Hierarchy SQL.jpg) in Post #1 must be a query?

    In the image, you are looking at the field "Root Cause", but your formulas use "Description"???


    You might try using the SWITCH() function:
    Code:
    <snip>
    dbo_NCR_IncidentTypes.Description AS [Incident Type],
    dbo_NCR_RootCauses.Description AS [Root Cause],
    Switch (dbo_NCR_RootCauses.Description LIKE "Carrier*", "1-Carrier", dbo_NCR_RootCauses.Description LIKE "Shipper*", "2-Shipper", dbo_NCR_RootCauses.Description LIKE "Consignee*", "3-Consignee", "Other") AS FaultHierarchy,
    dbo_NCR_PreventiveActions.Description AS [Preventive Action],
    <snip>

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Need TRUE condition for the last parameter:

    Switch (dbo_NCR_RootCauses.Description LIKE "Carrier*", "1-Carrier", dbo_NCR_RootCauses.Description LIKE "Shipper*", "2-Shipper", dbo_NCR_RootCauses.Description LIKE "Consignee*", "3-Consignee", TRUE, "Other")
    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
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    Thanks June...... I missed that.

    Didn't count the pairs for switching.....

  14. #14
    rfigueroa1976 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    34
    Hi all... Sorry for the delay on this. Holidays and other tasks slowed my progress on this...

    Trying to follow the direction but am receiving an error message "syntax error (missing operator) in query..."

    Below is exactly how i have the query setup. Please let me know what Doing wrong. Thank you ALL for ALL the help thus far. Much appreciated!

    SELECT dbo_orderheader.ord_number AS [Order Number], dbo_orderheader.ord_refnum AS [Reference Number], dbo_NCR_Occurrence.BookingTerminal AS [Booking Terminal], dbo_orderheader.ord_origin_latestdate AS [Scheduled Loading Date], dbo_orderheader.ord_startdate AS [Actual Loading Date Date], dbo_orderheader.ord_dest_latestdate AS [Scheduled Delivery Date], dbo_orderheader.ord_completiondate AS [Actual Delivery Date], dbo_company_1.cmp_name AS [Shipper Name], dbo_city.cty_name AS [Shipper City], dbo_city.cty_state AS [Shipper State], dbo_company.cmp_name AS Consignee, dbo_city_1.cty_name AS [Consignee City], dbo_city_1.cty_state AS [Consignee State], dbo_NCR_Occurrence.EnteredBy AS [NCR Created By], dbo_NCR_Occurrence.NCR_ID AS [NCR ID], dbo_NCR_Occurrence.CustomerComplaintNumber AS [Customer Complaint Number], dbo_NCR_Occurrence.OccurrenceDate AS [Occurrence Date], dbo_NCR_IncidentTypes.Description AS [Incident Type], dbo_NCR_RootCauses.Description AS [Root Cause], dbo_NCR_PreventiveActions.Description AS [Preventive Action], Switch (dbo_NCR_RootCauses.Description LIKE "Carrier*", "1-Carrier", dbo_NCR_RootCauses.Description LIKE "Shipper*", "2-Shipper", dbo_NCR_RootCauses.Description LIKE "Consignee*", "3-Consignee", dbo_NCR_RootCauses.Description LIKE "Unforeseen*", "4-Unforeseen",TRUE, "5-Other")" AS [Fault Hierarchy],


    FROM (((((((dbo_NCR_Occurrence INNER JOIN dbo_NCR_RootCauses ON dbo_NCR_Occurrence.RootCause_ID = dbo_NCR_RootCauses.RootCause_ID) INNER JOIN dbo_orderheader ON dbo_NCR_Occurrence.OrderNumber = dbo_orderheader.ord_number) INNER JOIN dbo_NCR_IncidentTypes ON dbo_NCR_Occurrence.IncidentType_ID = dbo_NCR_IncidentTypes.IncidentType_ID) INNER JOIN dbo_company AS dbo_company_1 ON dbo_orderheader.ord_shipper = dbo_company_1.cmp_id) INNER JOIN dbo_city ON dbo_company_1.cmp_city = dbo_city.cty_code) INNER JOIN dbo_company ON dbo_orderheader.ord_consignee = dbo_company.cmp_id) INNER JOIN dbo_NCR_PreventiveActions ON dbo_NCR_Occurrence.PreventiveAct_ID = dbo_NCR_PreventiveActions.PreventiveAct_ID) INNER JOIN dbo_city AS dbo_city_1 ON dbo_company.cmp_city = dbo_city_1.cty_code


    WHERE (((dbo_orderheader.ord_origin_latestdate) Between [start] And [end]) AND ((dbo_NCR_RootCauses.Description) Not Like "*internal*") AND ((dbo_NCR_Occurrence.CustomerName) Like "*arclin*") AND ((dbo_NCR_Occurrence.BilltoName) Like "*arclin*") AND ((dbo_NCR_Occurrence.Status_ID) Not Like "26"))
    ORDER BY dbo_city.cty_name, dbo_NCR_RootCauses.Description;

  15. #15
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Replace that red comma with a space... That's a syntax error.
    Code:
    dbo_NCR_RootCauses.Description LIKE "Unforeseen*", "4-Unforeseen",TRUE, "5-Other")" AS [Fault Hierarchy],
    
    
    FROM (((((((dbo_NCR_Occurrence INNER JOIN dbo_NCR_RootCauses ON dbo_NCR_Occurrence.RootCause_ID = dbo_NCR_RootCa

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

Similar Threads

  1. Labeling Page 2 of a report?
    By PATRICKPBME in forum Reports
    Replies: 14
    Last Post: 07-20-2017, 10:32 AM
  2. Labeling an icon on my dashboard
    By PATRICKPBME in forum Access
    Replies: 2
    Last Post: 05-01-2017, 01:00 PM
  3. Tool Check Out and labeling small items
    By WDD in forum Database Design
    Replies: 5
    Last Post: 11-29-2014, 10:57 PM
  4. Labeling command buttons
    By SemiAuto40 in forum Forms
    Replies: 6
    Last Post: 07-17-2012, 11:03 AM
  5. labeling multiple copies
    By tgavin in forum Reports
    Replies: 1
    Last Post: 05-11-2011, 05:07 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