Pull highest non-numeric value from multiple fields in an Access form

[Cratebug]

I have an Access .accdb form with a query that is itself being fed by 2 different tables (
Views: 8 Size: 492.0 KB">EngID_dB.accdb).

I have a field in the query named "IRR_HighestLevel" which resides in "Database 1" tbl (Its a ratings field that could have one of the following 4 ratings: Critical, High, Moderate or Low. Critical being the highest.)

In table 2 ("tblTPRMdb"), I have 10 fields called "EngID1" through "EngID10" that are also added to the query as well.
"Database 1" tbl also has these same name Engagement ID fields in it and it is what links both tables in the query.

Now each record visible in the form can have up to 10 different EngIDs associated with it....Each with its own "IRR_DataSecurity" rating (1 of the 4 ratings mentioned above)

For example: In the image below, the desired rating to be returned to the "IRR_HighestLevel" field for the record in this case should be 'Critical' as it is the highest rating ...



I'm wondering if there is some VBA coding Event I could apply to one of the fields or the Access form itself (On Current event property??) with perhaps an IF statement that would automatically return/pull the highest rating from the "IRR_DataSecurity" rating fields and have it auto-populate in the "IRR_HighestLevel" field??

Hope I painted a clear picture.

Any help would be greatly appreciated.

[June7]

Both tables have these 10 fields as opposed to 10 records? Would not be normalized structure and therefore more difficult to accomplish.

If they are 10 fields why do you show data vertically?

Exactly what value do you want to return? I see 5 fields with High rating. Do you want to know the max field with High rating?

Not really understanding table relationship. Post example of both tables data and/or the query SQL statement.

[Cratebug]

I am attaching a copy of a small version of the db with the field I'm trying to automate.

Some of the field/tbl naming conventions are slightly different from my previous post but simply put, I want the "IRR Highest Level" to auto-populate the highest "IRR - Data Security" rating among the 10 "EngID" fields you see below.

I am not sure if the way to go would be via vba coding or what.....perplexed and a bit beyond me I must admit.

Any assistance with this would be greatly appreciated.

UPDATE:

The following field(s) are from the first tbl: Unique, Third Party Name, IRR Highest Level & Engagement ID
The following field(s) are from the second tbl: IRR Data Security

The link between both tbls are the "EngID" fields


Hierarchy in ratings:

Critical
High
Moderate
Low





Please see link to file in the next post.

[Cratebug]

Having issues uploading an .accdb file and not sure why but here is a link to it...

https://www.dropbox.com/s/0m00e9mbc2..._dB.accdb?dl=0

[RuralGuy]

If you ZIP up the db, you should have no problems uploading it.

[Cratebug]

Thanks RuralGuy...hope this works.

EngID_dB.accdb

Turns out my file was way bigger than the allowed file size limit.......kept having to downsize it.

[Micron]

Late to the party, so haven't downloaded the db. While it does sound like at least one table isn't properly normalized (then by exendtion the db itself) the problem appears to be the lack of a sorting field for the conditions/states. Since they are alpha, there cannot be a reliable sort order or Max value. If there was a sort field, then the approach would be to get the Max of the numeric sort field - possibly via simple DLookup.

[isladogs]

This has been posted in at least three Forums: here, AWF and UA with different user names.
Please follow standard forum etiquette when cross posting. Say you have done so and provide the links. For an explanation of why this matters, read this link
https://www.excelguru.ca/content.php...-cross-posters

[orange]

Also late to the thread, but like micron and june7 I wonder if you should spend some time and effort to redesign/normalize the database.
And, make sure your design spans your complete requirement.

Perhaps you could describe - in simple English - exactly the issue/opportunity you are trying to support with automation.

Good luck.

[June7]

The comboboxes are locked and value cannot be selected from list so why use comboboxes and not textboxes?

And why would you want to populate a combobox with this 'highest' category? Are users allowed to change this value?

Options:

1. VBA function that cycles through fields of current record and returns the 'highest' category encountered, like:

Function GetHighest() As String
Dim x As Integer
Dim intLevel As Integer, intLevel1 As Integer, strLevel As String
For x = 1 To 10
    If Not IsNull(Me.Controls("VM R" & x & " Rating")) Then
        strLevel = Me.Controls("VM R" & x & " Rating")
        intLevel1 = Switch(strLevel = "Critical", 4, strLevel = "High", 3, strLevel = "Moderate", 2, strLevel = "Low", 1)
        If intLevel1 > intLevel Then intLevel = intLevel1
    End If
Next
GetHighest = Choose(intLevel, "Low", "Moderate", "High", "Critical")
End Function

Expression in textbox: =GetHighest()

2. UNION query to rearrange the 10 fields into normalized structure

[Cratebug]

June7, thank you very much for this. Works excellent!

Yes you're right....forgot to convert combobox to textbox sorry. Users would not be allowed to make changes to that field.


orange - Agreed.....I inherited current db I'm working with and per what June7 and micron alluded to, redesign/normalization is definitely in order and forthcoming.

Again, thank you to all who replied!

The comboboxes are locked and value cannot be selected from list so why use comboboxes and not textboxes?

And why would you want to populate a combobox with this 'highest' category? Are users allowed to change this value?

Options:

1. VBA function that cycles through fields of current record and returns the 'highest' category encountered, like:

Function GetHighest() As String
Dim x As Integer
Dim intLevel As Integer, intLevel1 As Integer, strLevel As String
For x = 1 To 10
    If Not IsNull(Me.Controls("VM R" & x & " Rating")) Then
        strLevel = Me.Controls("VM R" & x & " Rating")
        intLevel1 = Switch(strLevel = "Critical", 4, strLevel = "High", 3, strLevel = "Moderate", 2, strLevel = "Low", 1)
        If intLevel1 > intLevel Then intLevel = intLevel1
    End If
Next
GetHighest = Choose(intLevel, "Low", "Moderate", "High", "Critical")
End Function

Expression in textbox: =GetHighest()

2. UNION query to rearrange the 10 fields into normalized structure