An answer to this would do me alot of good!!!

  • Thread starter Thread starter Ted Rogers
  • Start date Start date
T

Ted Rogers

I have been asked by my (new) boss to try and solve a problem for him. I'll
paste his question below and try to outline the table structure. I haven't
the first clue where to start but an answer would earn me loads of brownie
points!

Thank you so much,

Ted

I have a table of car dealers who based upon the amount of business they do
can earn monthly or quarterly (depending on dealer) bonuses set at different
levels. For instance, if they sell between £0 and £50,000 they earn 1%, if
they sell 50,001 and 100,000 they earn 1.5% and if they sell over 100,001
they earn 2.0%. I need to create a query which reports the bonus payments
due for each dealership. I've managed to set up a query to calculate the
amount of sales but I then need to calculate the bonus payments. I need to
run this query each month. Can this be done within the current structure
using a query? Do I have to change the structure? Can it only be done
writing SQL statements?

tblDealers:
DealerNumber
DealerName
BonusID

tblContracts:
DealerNumber
DealerName
Date
ContractNumber
AmountFinanced
PlanCode

tblStandardBonus:
BonusID
BonusPercentage
MinVolume
MaxVolume
BonusType

Relationships:
TbleContracts (DealerNumber) to tblDealers (DealerNumber) One to Many
 
you should be able to make this query without writing ANY sql
statements.. I mean.. Access has an awesome query designer.. it's all
drag and drop; no coding necessary

-aaron
 
I have been asked by my (new) boss to try and solve a problem for him. I'll
paste his question below and try to outline the table structure. I haven't
the first clue where to start but an answer would earn me loads of brownie
points!
Click to expand...

A "non equi join" query will do the trick here. It's a bit beyond the
capabilities of the query grid, but it's easy enough to do in the SQL
window.

Take your current totals query calculating the total volume ( or total
sales); add tblStandardBonus to the query window, with *no* join line.

As a criterion on the total sales figure, put
= [MinVolume] AND <= [MaxVolume]
Click to expand...

(or use > or < if appropriate, depending on where your min and max
volume cut off).

John W. Vinson[MVP]
 
Back
Top