CODEWITHSUNDEEP
sqlms-access
Reggie
Reggie

Reputation: 3

Microsoft Access Multiple Criteria based on another table

I am working with a database in MS Access. I have a table(Table A) with different categories (criteria) I have another table (Table B), where I have to pull values from Table A based on two categories, (year and amount). For example, From table B, the cost is $15,000, so we go to table A and find the contingency from year 2018 which falls between $0-$20,000 and report a contingency of 25%.

Is there a way to go about this? I've been racking my brain trying to use nested "IIF" and "AND" functions but i can't figure it out

Table A

Table B

Upvotes: 0

Views: 1182

Answers (3)

Parfait
Parfait

Reputation: 107567

Consider a DLookUp in an UPDATE query. The domain aggregate is needed for query to be updateable.

UPDATE tableB b
SET b.Contingency = DLookUp("CONTINGENCY", "tableA", "[C_YEAR] = " & b.[C_YEAR] &
                            " AND [MIN_VALUE] <= " & b.[COST] & " AND [MAX_VALUE] >= " &  b.[Cost])

Upvotes: 0

Zack
Zack

Reputation: 2341

It looks like the contingency rates for all the years are the same:

  • 25% for values between $0 and $20,000
  • 15% for values between $20,001 and $200,000
  • 10% for values between $200,000 and $100,000

Is this always the case, or just based on the sample data you're using?

Is it possible you'll have data where the cost is > $100,000,000? If so, how should the data be handled?

I'm just wondering if there's not a better way to represent your contingency rate rules. Otherwise, I'd agree with Rene about joining the tables and adding a WHERE condition to get the rates you need. I'd also add that, since you'll always be pulling the rate from the query, you don't need an actual field on Table B to store the contingency rate.

Upvotes: 0

Rene
Rene

Reputation: 1093

Add both tables to a query. Join on C_YEAR. Use BETWEEN AND to grab the appropriate range hence the appropriate contingency. Something like:

SELECT tableA.CONTINGENCY
FROM tableA INNER JOIN tableB ON tableA.C_YEAR = tableB.C_YEAR
WHERE tableB.COST BETWEEN tableA.MIN_VALUE AND tableA.MAX_VALUE;

Upvotes: 1

Related Questions