Help needed with a range function

[Adriano85]

Hi guys. First of all, sorry for the vague description in the title. I have no idea what the name is for the function I'm looking for..

Bascily this is what I'm looking for;

I have a table with the number of sales and the bonus that belongs to that number of sales. I don't have a record for every possible number of sales so I'm using ranges and the bonus in Euro's.

I also have a query that shows the number of sales per week by each sales team.

What function do I need to use to show the bonus (from the bonus table) behind the number of sales in the query? Since I only have ranges of sales in the bonus table I'm looking for a function that can estimate to which range the number of sales in the query belongs.

Many thanks for your help guys!

 

[John Big Booty]

I suspect that the Between...And Operator will do what you are looking for.

 

[jzwp22]

If I understand what you are after, you may want to look at the Partition() function. The function can be used in control sources (forms/reports) but it is probably best used in a query.

 

[Pat Hartman]

Typically when we create queries, we use equi-joins i.e. the = sign but SQL allows other operators. So instead of:
Where tbl1.fld1 = tbl2.fld1

you can use something like:

Where tbl1.fld1 >= tbl2.fld1 And tbl1.fld1 <= tbl2.fld2

However, once you change the join type to non-equi-join, Access can no longer display the query with the QBE so you will need to work in SQL view.

 

[Adriano85]

Thanks for your help guys. Unfortunately this is not what i'm looking for.
Between.. and.. needs to be manually amended once bonus fees change and this is not the best solution. I think I did not explain everything good enough for you guys to help me.

What I'm looking for is something like a vlookup.

Those are the steps I take to get the info I need:

step one: run a query that shows SalesTeam, SalesWeek, SalesNumber, Bonus (SalesNumber*BonusAmount)
The problem for me is the BonusAmount. Those amounts are in a seperate table so the function needs to pick the right bonus amount based on the number of sales.

step two: append the results from the query into a table that shows all weeks, teams, number of sales and total bonus received. This table will not contain duplicates since there is only one team per week allowed.

If someone has a better idea how to solve this I would really appreciate it.

Many thanks!

Adrian

 

[Pat Hartman]

What I'm looking for is something like a vlookup

Did you look at the join I suggested.

 

[John Big Booty]

Also check out the various Domain Aggregation Functions.

 

[Adriano85]

Did you look at the join I suggested.

Hi Pat. Yes I tried but it did not brought me where I wanted to be. Not because your advice is not correct but because I don't have enough knowledge about this to realize what I want.
Thanks for your input anyway.