Pull highest non-numeric value from multiple fields in an Access form (1 Viewer)

dmacias · Jul 12, 2018

I have an Access .accdb form with a query that is itself being fed by 2 different tables. (SEE ATTCHED FILES)

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 attached image, 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.

plog · Jul 12, 2018

In table 2 ("tblTPRMdb"), I have 10 fields called "EngID1" through "EngID10" that are also added to the query as well.

Stop whatever it is you just asked about and fix your tables. Most likely this issue is a symptom of your poor structure or will be resolved easily when you do.

When you start numbering field names, its a sign you need a new table. Tables should accomodate data vertically (with more rows) and not horizontally (with more columns).

That means Unique#=4809 in Database1 would be 5 records in a new table. Its entire data would look like this:

Database1
Unique#, autonumber, primary key
Vendor, short text, name of vendor

Unique#, Vendor
4809, Goldberg Kohn Ltd

Database1Eng
DEngID, autonumber, primary key
Unique#, number, foreign key to Database1
EngID, short text, will hold value of all populated EngID fields currently in Database1
EngNum, number, will hold numeric part of fieldname of EngID currently in Database1

DengId, Unique#, EngID, EngNum
1, 4809, ENG9993PM00006, 1
2, 4809, ENG9993PM00007, 2
3, 4809, ENG9993PM00008, 3
4, 4809, ENGP993PM00009, 4
5, 4809, ENGP993PM0010, 5

That's how that data should be stored. It also assumes that the numeric part of the EngID in Database1 is signficant and not just a way to differeniate field names. If that's all you are using it for you don't need EngNum in the new table.

After that, you then work on reports and queries to get the data out of your database like you want. Then, finally, you work on forms.

Ranman256 · Jul 12, 2018

1 problem:
table 2 ("tblTPRMdb"), should only have have 3 fields called
ID, EngID, IRR_DataSecurity

this is part of the problem. Now you must have 10 queries because you your design.
either make 10 queries to pull all the fields (put them into a UNION query)
or
fix this table to the atomic design I mentioned at the start, and run 1 query.
then you run Q1 to get non numerics,
Q2 to pull the most recent
Q3 to join them and get what you wanted.

MajP · Jul 12, 2018

Stop whatever it is you just asked about and fix your tables. Most likely this issue is a symptom of your poor structure or will be resolved easily when you do

Please listen to that advice. Even if you can make this works as in, you will continually run into problems like this. This should be an extremely trivial task, but because your table design is incorrect it is now very challenging.

dmacias · Jul 12, 2018

Thanks for this Plog....I inherited a db nightmare and now I'm trying to fix all kinds of shhh thats wrong with it. Namely, the fact that the ACTUAL "Database 1" table (the file I attached has like 1% of the data I'm dealing with) has more than 200 fields in it and of course the numbered EngID fields. I don't look forward to restructuring this database but I know youre right....gonna have to get dirty and rebuild.:banghead:

plog said:
Stop whatever it is you just asked about and fix your tables. Most likely this issue is a symptom of your poor structure or will be resolved easily when you do.

When you start numbering field names, its a sign you need a new table. Tables should accomodate data vertically (with more rows) and not horizontally (with more columns).

That means Unique#=4809 in Database1 would be 5 records in a new table. Its entire data would look like this:

Database1
Unique#, autonumber, primary key
Vendor, short text, name of vendor

Unique#, Vendor
4809, Goldberg Kohn Ltd

Database1Eng
DEngID, autonumber, primary key
Unique#, number, foreign key to Database1
EngID, short text, will hold value of all populated EngID fields currently in Database1
EngNum, number, will hold numeric part of fieldname of EngID currently in Database1

DengId, Unique#, EngID, EngNum
1, 4809, ENG9993PM00006, 1
2, 4809, ENG9993PM00007, 2
3, 4809, ENG9993PM00008, 3
4, 4809, ENGP993PM00009, 4
5, 4809, ENGP993PM0010, 5

That's how that data should be stored. It also assumes that the numeric part of the EngID in Database1 is signficant and not just a way to differeniate field names. If that's all you are using it for you don't need EngNum in the new table.

After that, you then work on reports and queries to get the data out of your database like you want. Then, finally, you work on forms.

dmacias · Jul 12, 2018

Thanks Ranman!!

Ranman256 said:
1 problem:
table 2 ("tblTPRMdb"), should only have have 3 fields called
ID, EngID, IRR_DataSecurity

this is part of the problem. Now you must have 10 queries because you your design.
either make 10 queries to pull all the fields (put them into a UNION query)
or
fix this table to the atomic design I mentioned at the start, and run 1 query.
then you run Q1 to get non numerics,
Q2 to pull the most recent
Q3 to join them and get what you wanted.

dmacias · Jul 12, 2018

No argument MajP. Thank you for responding.

MajP said:
Please listen to that advice. Even if you can make this works as in, you will continually run into problems like this. This should be an extremely trivial task, but because your table design is incorrect it is now very challenging.

MajP · Jul 12, 2018

I don't look forward to restructuring this database but I know youre right....gonna have to get dirty and rebuild.

There are a lot of tricks to make doing this not as labor intensive as you think. A few xtabs and union queries to make new tables can do a lot of work. Additionally some code may be helpful. Ask here to get some ideas.

isladogs · Jul 12, 2018

This has been posted in at least three Forums: AWF, MAF 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?184-A-message-to-forum-cross-posters

dmacias · Jul 12, 2018

ridders said:
This has been posted in at least three Forums: AWF, MAF and UA with different user names.

Please follow standard forum etiquette when cross posting.

Understood. My apologies.

Mark_ · Jul 12, 2018

To answer your original question, I'd recommend making a custom function. You would pass it all of the criteria. It then get the following logic;

Code:

ReturnValue = 0
IF Value1 > ReturnValue then ReturnValue = Value 1
IF Value2 > ReturnValue then ReturnValue = Value 2
IF Value3 > ReturnValue then ReturnValue = Value 3
.
.
.

FunctionName = Returnvalue

As you can see, this IS doable, and for a "One off till I can fix it" may cover you for the moment. This is also the same type of logic that comes into play when you are checking more that one item where they are not otherwise easily connected. Example I am currently working on is required items for beginning of semester. Some are from the health tracking system, others are regarding registration, others are from documents required by the school and I'm supposed to be adding physical equipment purchased... eventually.

As they are being fed by different sources I'm using this approach until I can get the systems put together. It is by no means what I'd LIKE, but it is ... good enough for now.

dmacias · Jul 16, 2018

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

UPDATE: Even though the db needs to be reworked in terms of restructuring and normalizing tables, someone fortunately took pity on me and provided me with the following coding/solution and just wanted to post it here should someone else run up against this in the future. Thanks to everyone who replied to this thread:

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

Code:
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 to use in the textbox I wanted the desired value returned: =GetHighest()

Pull highest non-numeric value from multiple fields in an Access form (1 Viewer)

dmacias

Registered User.

Attachments

plog

Banishment Pending

Ranman256

Well-known member

MajP

You've got your good things, and you've got mine.

dmacias

Registered User.

dmacias

Registered User.

dmacias

Registered User.

MajP

You've got your good things, and you've got mine.

isladogs

MVP / VIP

dmacias

Registered User.

Mark_

Longboard on the internet

dmacias

Registered User.

Similar threads

Users who are viewing this thread