If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need to modify discount expression
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","PeopleNumbe r=" & [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 |
#2
|
|||
|
|||
Need to modify discount expression
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","PeopleNu mber=" & [PeopleNumber] & " And " & [Income] & "[MaxIncome]"),0) I am not sure if that is what you want without seeing the underlying qry. Damon "rpbsr" wrote in message ... 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","PeopleNumbe r=" & [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 |
#3
|
|||
|
|||
Need to modify discount expression
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 "Damon Heron" wrote: 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","PeopleNu mber=" & [PeopleNumber] & " And " & [Income] & "[MaxIncome]"),0) I am not sure if that is what you want without seeing the underlying qry. Damon "rpbsr" wrote in message ... 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","PeopleNumbe r=" & [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 |
#4
|
|||
|
|||
Need to modify discount expression
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" wrote: 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","PeopleNumbe r=" & [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 |
#5
|
|||
|
|||
Need to modify discount expression
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 "boblarson" wrote: 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" wrote: 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","PeopleNumbe r=" & [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 |
#6
|
|||
|
|||
Need to modify discount expression
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","PeopleNu mber=" & [PeopleNumber] & " And " & [Income] & "[MaxIncome]"),1) '================================================= === John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County '================================================= === rpbsr wrote: 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 "boblarson" wrote: 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" wrote: 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","PeopleNumbe r=" & [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 |
#7
|
|||
|
|||
Need to modify discount expression
That did the trick John, thank you!!
"John Spencer" wrote: 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","PeopleNu mber=" & [PeopleNumber] & " And " & [Income] & "[MaxIncome]"),1) '================================================= === John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County '================================================= === rpbsr wrote: 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 "boblarson" wrote: 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" wrote: 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","PeopleNumbe r=" & [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 |
Thread Tools | |
Display Modes | |
|
|