Look up values from a table with scales

[ram]

HI

I have two tables one with sales production the other with a payout scale.
What I would like to do is build a query that has the agent number Sales
Units and Quarterly Dollars Earned.

Thanks for any help

tblSales_production
Agent_Number Sales Units
1 98.48
2 125.63
3 65.05
4 65.8
5 69.72
6 71.39
7 170
8 97.05
9 88.72

tblPayoutScale
Quarterly_Sales_Units Quarterly_Dollars_Earned
75 300
90 600
100 700
110 800
120 900
130 1000
140 1100
150 1200
160 1300
170 1400


NewQuery
Agent_Number Sales Units Quarterly_Dollars_Earned
1 98.48 600
2 125.63 900
3 65.05 0
4 65.8 0
5 69.72 0
6 71.39 0
7 170 1400
8 97.05 600
9 88.72 300

 

[Fred]

Not enough here to go on.

Start by defining (at least for yourself, if not for us) what type of entity
constitutes a record in each of your tables. I don't know your data, but
for example a guess on your first table might be "quarterly daily sales unit
totals for individual salespersons.

Next, I suspect that once you do this for the second table you will notice
that you are missing a field in the second table. If those records relate
to a particular sales person, then you need an agent number field in there
too.

 

[KARL DEWEY]

tblPayoutScale needs a second field to make a range, high and low.
See theis query --
SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

[Travel]
Name Mileage
joe 650
dd 100
ll 10000
p 10

[RateTable-Mileage]
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521

 

[John Spencer]

I think the following MAY work for you.

SELECT Agent_Number
, [Sales Units]
, Nz((SELECT Max(Quarterly_Dollars_Earned)
FROM tblPayoutScale
WHERE Quarterly_Sales_Unit<=tblSales_Production.[Sales Units]),0) AS
Quarterly_Dollars_Earned
FROM tblSales_Production

You could add one more record to tblPayoutScale with zero in both
fields. Then you could simplify the query a bit to

SELECT Agent_Number
, [Sales Units]
, (SELECT Max(Quarterly_Dollars_Earned)
FROM tblPayoutScale
WHERE Quarterly_Sales_Unit<=tblSales_Production.[Sales Units]) AS
Quarterly_Dollars_Earned
FROM tblSales_Production

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[ram]

Thank you this was very helpful.

tblPayoutScale needs a second field to make a range, high and low.
See theis query --
SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

[Travel]
Name Mileage
joe 650
dd 100
ll 10000
p 10

[RateTable-Mileage]
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521
--
KARL DEWEY
Build a little - Test a little

HI

I have two tables one with sales production the other with a payout scale.
What I would like to do is build a query that has the agent number Sales
Units and Quarterly Dollars Earned.

Thanks for any help

tblSales_production
Agent_Number Sales Units
1 98.48
2 125.63
3 65.05
4 65.8
5 69.72
6 71.39
7 170
8 97.05
9 88.72

tblPayoutScale
Quarterly_Sales_Units Quarterly_Dollars_Earned
75 300
90 600
100 700
110 800
120 900
130 1000
140 1100
150 1200
160 1300
170 1400


NewQuery
Agent_Number Sales Units Quarterly_Dollars_Earned
1 98.48 600
2 125.63 900
3 65.05 0
4 65.8 0
5 69.72 0
6 71.39 0
7 170 1400
8 97.05 600
9 88.72 300

 

[ram]

Thank you this was very helpful

I think the following MAY work for you.

SELECT Agent_Number
, [Sales Units]
, Nz((SELECT Max(Quarterly_Dollars_Earned)
FROM tblPayoutScale
WHERE Quarterly_Sales_Unit<=tblSales_Production.[Sales Units]),0) AS
Quarterly_Dollars_Earned
FROM tblSales_Production

You could add one more record to tblPayoutScale with zero in both
fields. Then you could simplify the query a bit to

SELECT Agent_Number
, [Sales Units]
, (SELECT Max(Quarterly_Dollars_Earned)
FROM tblPayoutScale
WHERE Quarterly_Sales_Unit<=tblSales_Production.[Sales Units]) AS
Quarterly_Dollars_Earned
FROM tblSales_Production

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

HI

I have two tables one with sales production the other with a payout scale.
What I would like to do is build a query that has the agent number Sales
Units and Quarterly Dollars Earned.

Thanks for any help

tblSales_production
Agent_Number Sales Units
1 98.48
2 125.63
3 65.05
4 65.8
5 69.72
6 71.39
7 170
8 97.05
9 88.72

tblPayoutScale
Quarterly_Sales_Units Quarterly_Dollars_Earned
75 300
90 600
100 700
110 800
120 900
130 1000
140 1100
150 1200
160 1300
170 1400


NewQuery
Agent_Number Sales Units Quarterly_Dollars_Earned
1 98.48 600
2 125.63 900
3 65.05 0
4 65.8 0
5 69.72 0
6 71.39 0
7 170 1400
8 97.05 600
9 88.72 300

 

[ram]

Thanks for your reply

With your suggestion and the other two post I have the problem resolved