Need to modify discount expression

[rpbsr]

I have a discount table based on Income and PeopleNumber (below) that works
with a query and DLookUp expression to produce good results for two
discounts. I can't figure how to return a zero discount when applicable based
on these fields.

=DLookUp("DiscountAmt","qryDiscounts","PeopleNumber=" & [PeopleNumber] & "
And " & [Income] & "<[MaxIncome]")

DiscountID DiscountAmt PeopleNumber MaxIncome
1 0.75 8 $49,050.00
2 0.75 7 $46,050.00
3 0.75 6 $43,100.00
4 0.75 5 $40,100.00
5 0.75 4 $37,150.00
6 0.75 3 $33,450.00
7 0.75 2 $29,700.00
8 0.75 1 $26,000.00
9 0.5 8 $78,450.00
10 0.5 7 $73,700.00
11 0.5 6 $68,950.00
12 0.5 5 $64,200.00
13 0.5 4 $59,450.00
14 0.5 3 $53,500.00
15 0.5 2 $47,550.00
16 0.5 1 $41,600.00

 

[rpbsr]

Damon,
My query is below:
SELECT tblDiscounts.DiscountAmt, tblDiscounts.PeopleNumber,
tblDiscounts.MaxIncome
FROM tblDiscounts
GROUP BY tblDiscounts.DiscountAmt, tblDiscounts.PeopleNumber,
tblDiscounts.MaxIncome
ORDER BY tblDiscounts.PeopleNumber, tblDiscounts.MaxIncome;

For ease with other calculations, the "DiscountAmt" is actually (1-the
actual discount). What I can't seem to get is a series to produce a
DiscountAmt of 1(or no discount), which results from a similar series with
incomes GREATER THAN the $41,600 - $78,450 range. I also would want ensure no
null values.

I appreciate your help and ideas.
Robert

If your qryDiscounts doesn't return fields with zero discount, then it will
give you a null value. So
you could change your dlookup to:
=nz(DLookUp("DiscountAmt","qryDiscounts","PeopleNumber=" & [PeopleNumber] &
"
And " & [Income] & "<[MaxIncome]"),0)

I am not sure if that is what you want without seeing the underlying qry.

Damon

I have a discount table based on Income and PeopleNumber (below) that works
with a query and DLookUp expression to produce good results for two
discounts. I can't figure how to return a zero discount when applicable
based
on these fields.

=DLookUp("DiscountAmt","qryDiscounts","PeopleNumber=" & [PeopleNumber] & "
And " & [Income] & "<[MaxIncome]")

DiscountID DiscountAmt PeopleNumber MaxIncome
1 0.75 8 $49,050.00
2 0.75 7 $46,050.00
3 0.75 6 $43,100.00
4 0.75 5 $40,100.00
5 0.75 4 $37,150.00
6 0.75 3 $33,450.00
7 0.75 2 $29,700.00
8 0.75 1 $26,000.00
9 0.5 8 $78,450.00
10 0.5 7 $73,700.00
11 0.5 6 $68,950.00
12 0.5 5 $64,200.00
13 0.5 4 $59,450.00
14 0.5 3 $53,500.00
15 0.5 2 $47,550.00
16 0.5 1 $41,600.00

 

[boblarson]

Should be:

=DLookUp("DiscountAmt","qryDiscounts","[PeopleNumber]=" & [PeopleNumber] & "
And [Income] < " & [MaxIncome])

--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[rpbsr]

Bob,
Thanks, but perhaps I didn't explain myself well. This works fine for the
tblDiscount data shown, i.e., the discounts are provided when the incomes are
less than those in the corresponding income field. The problem is that I
don't have a way to return a DiscountAmt of 1 for incomes that exceed the
upper level incomes, for example:

PeopleNumber Income DiscountAmt
1 >41600 1
2 >47550 1
3 >53500 1
4 >59450 1...
8 >78450 1

I've tried to guess at a series of incomes that use the above "income less
than" expression, but that's not reliable. Please let me know if I'm not
making myself clear or if this is the wrong approach.

Thanks,
Robert

Should be:

=DLookUp("DiscountAmt","qryDiscounts","[PeopleNumber]=" & [PeopleNumber] & "
And [Income] < " & [MaxIncome])

--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

I have a discount table based on Income and PeopleNumber (below) that works
with a query and DLookUp expression to produce good results for two
discounts. I can't figure how to return a zero discount when applicable based
on these fields.

=DLookUp("DiscountAmt","qryDiscounts","PeopleNumber=" & [PeopleNumber] & "
And " & [Income] & "<[MaxIncome]")

DiscountID DiscountAmt PeopleNumber MaxIncome
1 0.75 8 $49,050.00
2 0.75 7 $46,050.00
3 0.75 6 $43,100.00
4 0.75 5 $40,100.00
5 0.75 4 $37,150.00
6 0.75 3 $33,450.00
7 0.75 2 $29,700.00
8 0.75 1 $26,000.00
9 0.5 8 $78,450.00
10 0.5 7 $73,700.00
11 0.5 6 $68,950.00
12 0.5 5 $64,200.00
13 0.5 4 $59,450.00
14 0.5 3 $53,500.00
15 0.5 2 $47,550.00
16 0.5 1 $41,600.00

 

[John Spencer]

IF I understand your requirement you want to return 1 when no discount
amount is found (the max income is exceeded) or peopleNumber is not in
the table.

=NZ(DLookUp("DiscountAmt","qryDiscounts","PeopleNumber=" &
[PeopleNumber] & "
And " & [Income] & "<[MaxIncome]"),1)

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

Bob,
Thanks, but perhaps I didn't explain myself well. This works fine for the
tblDiscount data shown, i.e., the discounts are provided when the incomes are
less than those in the corresponding income field. The problem is that I
don't have a way to return a DiscountAmt of 1 for incomes that exceed the
upper level incomes, for example:

PeopleNumber Income DiscountAmt
1 >41600 1
2 >47550 1
3 >53500 1
4 >59450 1...
8 >78450 1

I've tried to guess at a series of incomes that use the above "income less
than" expression, but that's not reliable. Please let me know if I'm not
making myself clear or if this is the wrong approach.

Thanks,
Robert

Should be:

=DLookUp("DiscountAmt","qryDiscounts","[PeopleNumber]=" & [PeopleNumber] & "
And [Income] < " & [MaxIncome])

--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

I have a discount table based on Income and PeopleNumber (below) that works
with a query and DLookUp expression to produce good results for two
discounts. I can't figure how to return a zero discount when applicable based
on these fields.

=DLookUp("DiscountAmt","qryDiscounts","PeopleNumber=" & [PeopleNumber] & "
And " & [Income] & "<[MaxIncome]")

DiscountID DiscountAmt PeopleNumber MaxIncome
1 0.75 8 $49,050.00
2 0.75 7 $46,050.00
3 0.75 6 $43,100.00
4 0.75 5 $40,100.00
5 0.75 4 $37,150.00
6 0.75 3 $33,450.00
7 0.75 2 $29,700.00
8 0.75 1 $26,000.00
9 0.5 8 $78,450.00
10 0.5 7 $73,700.00
11 0.5 6 $68,950.00
12 0.5 5 $64,200.00
13 0.5 4 $59,450.00
14 0.5 3 $53,500.00
15 0.5 2 $47,550.00
16 0.5 1 $41,600.00

 

[rpbsr]

That did the trick John, thank you!!

IF I understand your requirement you want to return 1 when no discount
amount is found (the max income is exceeded) or peopleNumber is not in
the table.

=NZ(DLookUp("DiscountAmt","qryDiscounts","PeopleNumber=" &
[PeopleNumber] & "
And " & [Income] & "<[MaxIncome]"),1)

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

Bob,
Thanks, but perhaps I didn't explain myself well. This works fine for the
tblDiscount data shown, i.e., the discounts are provided when the incomes are
less than those in the corresponding income field. The problem is that I
don't have a way to return a DiscountAmt of 1 for incomes that exceed the
upper level incomes, for example:

PeopleNumber Income DiscountAmt
1 >41600 1
2 >47550 1
3 >53500 1
4 >59450 1...
8 >78450 1

I've tried to guess at a series of incomes that use the above "income less
than" expression, but that's not reliable. Please let me know if I'm not
making myself clear or if this is the wrong approach.

Thanks,
Robert

Should be:

=DLookUp("DiscountAmt","qryDiscounts","[PeopleNumber]=" & [PeopleNumber] & "
And [Income] < " & [MaxIncome])

--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________


:

I have a discount table based on Income and PeopleNumber (below) that works
with a query and DLookUp expression to produce good results for two
discounts. I can't figure how to return a zero discount when applicable based
on these fields.

=DLookUp("DiscountAmt","qryDiscounts","PeopleNumber=" & [PeopleNumber] & "
And " & [Income] & "<[MaxIncome]")

DiscountID DiscountAmt PeopleNumber MaxIncome
1 0.75 8 $49,050.00
2 0.75 7 $46,050.00
3 0.75 6 $43,100.00
4 0.75 5 $40,100.00
5 0.75 4 $37,150.00
6 0.75 3 $33,450.00
7 0.75 2 $29,700.00
8 0.75 1 $26,000.00
9 0.5 8 $78,450.00
10 0.5 7 $73,700.00
11 0.5 6 $68,950.00
12 0.5 5 $64,200.00
13 0.5 4 $59,450.00
14 0.5 3 $53,500.00
15 0.5 2 $47,550.00
16 0.5 1 $41,600.00