How to look up a value using two fields as input

[Godfrey]

Hi - I'm new to Access and databases but can't seem to find how to do something I would have thought would be central to database use.



I would like to use the values in two fields to determine / lookup a third.

An example might be a customer who selects their Country (AU, US, UK) and Product (Small, Medium, Large) in two fields which puts a value in a third field (e.g. Price). In excel (or on a sheet of paper) this would be a table with Country as rows, Product as columns and you could lookup the Price using INDEX and MATCH. Obviously I would want to have the 'table' much bigger than 3x3 but it's an example.

Any suggestions on how this is done? This would teach me a lot. Thanks in advance.

[CJ_London]

for the simple example you would have 3 tables

tblCountries
CountryPK autonumber
CountryName text

tblProducts
ProductPK autonumber
ProductName text

tblPrices
PricePK autonumber
ProductFK long
CountryFK long
Price currency

then depends how you are looking up the price and want to display it

But if for example on a form you have two unbound combo's, cboCountry for countries, cboProduct for products in the header section where the rowsource of each combo is the respective table, your 'standard' filter would be

"[CountryFK]=" cboCountry & " AND [ProductFK]=" & cboProduct

e.g. you might have

DLookup("[Price]","tblPrices","[CountryFK]=" cboCountry & " AND [ProductFK]=" & cboProduct)

[Godfrey]

Thanks Ajax

I probably should have said what I'm doing from the start. I thought that my actual problem wouldn't be understood by everyone, so I used an example I thought everyone could appreciate. But the more I'm learning about Access and databases, the more I learn that my question really wasn't very good.

What I am actually trying to do is build a risk register. I know I could buy a system off-the-shelf, and probably will, but I would really like to learn about databases - and build one myself.

Engineers will understand FMEA's and at the corporate level the equivalent is ISO 31000. Small companies usually comply by making one big clunky excel spreadsheet, with columns like: RiskID (unique), Date, Raised by, Event, Cause, Impact, CONSEQUENCE, LIKELIHOOD, RISK RATING, and other fields on how you're going to mitigate that risk. I think this is better done in a database, because each risk has multiple mitigations, and each mitigation may apply to multiple risks.

It's the capitalised items in that that are causing me problems.

The relationship between Consequences and Likelihood determines the Risk Rating as shown in the attachment image. So risk rating is really 'calculated', but not logically/mathematically. It's by looking up the table I've attached.


Ignoring all the other fields, I'd like a Risk Table that looks like:

tblRiskRegister
--------
RiskID autonumber
...
Likelihood <-- a variable, entered by user
Consequence <-- a variable, entered by user
RiskRating <-- Calculated/lookup.
...

So for a given Consequence and Likelihood, the Risk Rating is automatically filled.

I greatly appreciate your time and patience. I should also add that when I finish this I will make it public.

[CJ_London]

the principle would be the same

assuming the variables entered by users match your headings, your ratings table would be something like the price table

tblRatings
RatingPK autonumber
Likelihood text - e.g. Rare
Consequence text - e.g. Catastrophic
Rating text - would be Tolerable

which by your table would contain 25 rows

if they are not, you would need to explain how they 'convert' e.g. a likelihood of 0-10% equates to Rare, 10.01-20% equates to Unlikely, etc

[Godfrey]

Hi Ajax,

I'm sorry to be a pain, but I just can't seem to get it to work. Are you able to take a look at the attached? TestRiskRegister.accdb

I owe you a beer.

[CJ_London]

regret can't open the file. please try again, but zip the file. However knocking off now so will respond tomorrow

[Micron]

I have done this for ISO 14001 for an old program called Aspectrum. Sold a few, but it never amounted to much since the preference was shifting to web based apps at the time.
Anyway, I don't get why you say it's not calculated. AFAIC, it is. Using simple numbers for example, each consequence and risk rating is assigned a value of 1 thru 5 in your case. Insignificant/High calculates to 2 as does Minor/Tolerable. The color of the grid controls can be set via conditional formatting, so those two would be the same. To keep the characteristics in ascending order, each table (consequence/likelihood) should have a ranking field to sort by, and a field for describing the circumstances upon which to base the ranking decision when the form is being filled out. This ensures the severity order is always ascending, regarding what else may be going on alphabetically. You apply those numbers and look up what I called the Assess table (tblAssess). You'd need a table record for each possible result for each color with a corresponding assessment. The table might look like this

LineID	MaxScore	Assess
1	1	Insignificant
2	2	Insignificant
3	3	Insignificant
4	4	Low
6	6	Low
8	8	Medium
9	9	Medium
12	12	High
16	16	High

I went through some FMEA's a long time ago, and what I find curious about your picture is that any failure whose consequence that can be considered catastrophic could also be tolerable.

[Godfrey]

Hi Micron, thanks for your response.


Fair enough re: using 1-5 and then calculating them. That's what I've always previously done with FMEAs: SEVerity x OCCurrence = Risk Ranking Number, so you would get RRN's from 1-25. You would start at the most risky x likely (25's) and work your way down until the cost of implementing a mitigation is more than the likely damage from the failure.


How you address a potentially catastrophic risk depends on definitions of each likelihood and consequence. If something catastrophic is only likely to occur very rarely (e.g. war disrupting the supply chain) you might take your chances.


A problem with calculating is that the risk rating table shown isn't symmetric, so moderate x unlikely (3 x 2) = "Low" does not equal Minor x Possible (2 x 3) = "tolerable". I didn't ask how this table was determined - I probably should. But more generally, if you go back to my first example of Country x Product --> Price, that might not be calculated. There might be dozens of countries and thousands of products. Each country's pricing might be at the whim of the local marketing manager.


Could this be done using a composite key?


tblRiskRating
----
Key LikelihoodFK
Key ConsequenceFK
RiskRating text


TestRiskRegister.zip

[Micron]

Didn't notice the grid wasn't symmetrical. A modification of the table I proposed would probably work, perhaps as either a composite primary or index. For the life of me, I don't see what people have against a composite primary key and no one's ever answered why not (instead, they advocate a composite index, which is not the same thing).
What we all would agree on, I'm sure, is that you should NOT use lookup fields in your tables, and you have lots of them. You might want to think about that while I hit the hay. Maybe Google 'ms access lookup fields' and zero in on the negative stuff, not how to create them. It might be daylight where you are, but it's after 1 AM here, so I'm done for now. I'll re-read your last post when I stop rubbing my eyes and get back to you.

[Micron]

How you address a potentially catastrophic risk depends on definitions of each likelihood and consequence. If something catastrophic is only likely to occur very rarely (e.g. war disrupting the supply chain) you might take your chances.

Agreed, but IMHO, if the consequence or impact is catastrophic, by definition of the word (calamity, death, permanent loss, widespread destruction, etc. etc.) it should never be accepted as being tolerable. Let's say the impact of a particular failure mode is death or injury (brakes or some safety device failure). The idea that this could be tolerable is a non-starter. Perhaps you are only referring to financial impacts because your business is commerce (in which case, it could be argued that catastrophic might not really apply). Mine was manufacturing maintenance. But I digress, and it is your FMEA, not mine.

To get back to the original question where you refer to Excel functions, in Access, DLookup is one way of grabbing a value from a domain (table or query) as long as there's a relationship between the value you want to retrieve and the criteria you want to apply. A query is another way. At this point, if I re-read the entire thread, I see more than one issue here:
- how to retrieve a related value (your original question)
- how to structure your tables and their relationships (such as comments about composite fields or indexes)
- how to design these tables (as noted by me, you will likely regret using lookup fields for this)
for one source of info as to why; http://access.mvps.org/access/lookupfields.htm

So this might be an opportunity to split this into more than one thread, depending on what you decide to do from here. That is, one for your db schema (tables and relationships) and design support (words not to use, naming conventions, normalization) and whatever you need that after that. If you want to proceed as is, I'm happy to point you to the DLookup function. Find the Domain Aggregate functions section on this page https://www.techonthenet.com/access/functions/
and a detailed look at its usage http://allenbrowne.com/casu-07.html

[Godfrey]

Hi Micron,

Thanks for all of the above. There's a lot to take in. This is not mission critical for me, but I want to solve it just for curiosity. In the short term I'll just keep using Excel, but I'm sure a database should be a better solution than a spreadsheet due to the many-to-many relationship between risk causes and mitigations. I'll read up, keep plugging away and will post back if I find something that works.

Kind Regards.

[ssanfu]

I've been trying to follow this thread.

I did make some modifications to the tables...... Is this anywhere close to what you want?