I have a report based on a query. This report takes the total amount of currency entered and divides into the appropriate number of straps/loose bills the user should have. The following are the currency values:
Currency Value of strap
$100.00 $2000
$50.00 $1000
$20.00 $500
$10.00 $250
$5.00 $100
$1.00 $20
This means if the total amount of $100 bills = $16,400 the user should have 8 straps = $16,000 and 4 loose bills = $400.
Here is the SQL behind the query. As you can see I have over complicated the math behind this, which I beleive is causeing the inconsistant results.
Any help would be greatly appreciated, as this calculation works for one day, and provides less than perfect results for day #2
Many thanks as always,
Currency Value of strap
$100.00 $2000
$50.00 $1000
$20.00 $500
$10.00 $250
$5.00 $100
$1.00 $20
This means if the total amount of $100 bills = $16,400 the user should have 8 straps = $16,000 and 4 loose bills = $400.
Here is the SQL behind the query. As you can see I have over complicated the math behind this, which I beleive is causeing the inconsistant results.
Any help would be greatly appreciated, as this calculation works for one day, and provides less than perfect results for day #2
Code:
SELECT Sum(NZ([100CHIPTOT],0)+NZ([50CHIPTOT],0)+NZ([25CHIPTOT],0)+NZ([5CHIPTOT],0)+NZ([1CHIPTOT],0)) AS chiptot, Int(Sum([100ENTERED]*100/2000)) AS 100count, Sum([twocount]*2) AS TOTAL2, Sum(nz([MUTILATED],0)) AS TOTMUT, Sum(nz([conterfeit],0)) AS TOTCFT, Sum(Round([TWOCOUNT]*2)) AS 2value, Int(Sum([100ENTERED]*100/2000))*2000 AS 100value, Int(Sum([100entered]))-[100value]/100 AS 100Lcount, Int(Sum([10ENTERED]*10))-[10value] AS 10Lvalue, Int(Sum([20ENTERED]*20))-[20value] AS 20Lvalue, Int(Sum([1ENTERED]*1))-[1value] AS 1Lvalue, Int(Sum([5ENTERED]*5))-[5value] AS 5Lvalue, Int(Sum([50ENTERED]*50))-[50value] AS 50Lvalue, Int(Sum([100ENTERED]*100))-[100value] AS 100Lvalue, Int(Sum([1entered]))-[1value]/1 AS 1Lcount, Int(Sum([5entered]))-[5value]/5 AS 5Lcount, Int(Sum([10entered]))-[10value]/10 AS 10Lcount, Int(Sum([20entered]))-[20value]/20 AS 20Lcount, Int(Sum([50entered]))-[50value]/50 AS 50Lcount, Int(Sum([50ENTERED]*50/500)) AS 50count, Int(Sum([50ENTERED]*50/500))*500 AS 50value, Int(Sum([20ENTERED]*20/500)) AS 20count, Int(Sum([20ENTERED]*20/500))*500 AS 20value, Int(Sum([10ENTERED]*10/250)) AS 10count, Int(Sum([10ENTERED]*10/250))*250 AS 10value, Int(Sum([5ENTERED]*5/100)) AS 5count, Int(Sum([5ENTERED]*5/100))*100 AS 5value, Int(Sum([1ENTERED]*1/20)) AS 1count, Int(Sum([1ENTERED]*1/20))*20 AS 1value
FROM SOFTCOUNT
WHERE (((SOFTCOUNT.INSERTDATE) Between CDate(CLng(Date()-1)+CDbl(#12/30/1899 8:0:0#)) And CDate(CLng(Date())+CDbl(#12/30/1899 8:0:0#))));
Many thanks as always,