DMIN in MS Access Query

Question

My db is used to calculate commissions due to a salesperson. The commission percentage is tiered so that it increases as the total sales increase. For example, a salesperson is paid 2% if they hit $100 in sales, 3% if they hit $500, 4% if they hit $1000, etc.

The db has two tables used to calculate a salesperson's commission.

• Sales records all the sales made and has two fields: [Salesperson] which identifies who made the sale and Value which logs the total amount of the sale.
• CommissionRates has a tiered commission structure based on the total amount sold in a time period and has two fields: [Max Amount] which identifies the upper limit for a rate tier and [Rate] which gives the percentage due when total sales are above the related [Max Amount].

I need to create a query (or vba output to a table) combining these two tables that returns the earned commission amount per salesperson, calculated total sales in a given time period multiplied by the maximum rate for sales achieved.

I'm stumped. I've tried several ways to use a DMIN function but repeatedly get an "unknown" response when executing the query. The code following code works perfectly when I replace Sum([Sales].[value]) with a fixed number, but fails with the "unknown" error using the variable.

Sum([Sales].[Value])*DMin("Rate","CommissionRates","[Max_Amount] >=Sum([Sales].[Value])"

Any suggestions are greatly appreciated.

Answer 1

Looks like a variable concatenation error. Try this:

DMin("Rate","CommissionRates","[Max_Amount] >= " & Sum([Sales].[Value]))