Query produces mixed results 1

[mikelev]

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

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,

 

[rac2]

...
Int(Sum([10ENTERED]*10))-[10value] AS 10Lvalue,
Int(Sum([10entered]))-[10value]/10 AS 10Lcount,
Int(Sum([10ENTERED]*10/250)) AS 10count,
Int(Sum([10ENTERED]*10/250))*250 AS 10value
...

So if I claimed I had 97 10-dollar bills; you expect I handed you 970/250 = 3 straps plus 22 loose bills?

That is
10ENTERED = 97
10count = 3
10Lcount = 22

10value = 750
10Lvalue = 220

Correct?

 

[mikelev]

Many thanks Rac, work like a charm. Amazing what a fresh set of eyes can do.

A star for your time!

 

[mikelev]

I am hoping someone can help me figure this 1 issue out.

The response RAC2 works great except for the following:

Int(Sum([20ENTERED]*20/500)) AS 20count, Int(Sum([20ENTERED]*20/500))*500 AS 20value, Int(Sum([20entered]))-[20value]/20 AS 20Lcount, Int(Sum([20ENTERED]*20))-[20value] AS 20Lvalue,

For some reason the query shows: (for 550 $20.00's)
20Count 21
20Value $10,500
20LCount 25
20LValue $500


Should be:
20Count 22
20Value $11,000
20LCount 0
20LValue $0.00


The same code works fine for all other denominations except $20's.

Thanks in advance!

 

[PCLewis]

I have only glanced at this in passing but perhaps my advice may help:

I note that you are using Int() with a SUM() inside it. Please note that the calculation ([20ENTERED]*20/500 will most likely result in several fractions. So by summing these fractions together you will not always get the result you expect.

I think you could either replace your / sign with the \ sign or move the Int

Sum(Int([20ENTERED]*20/500)) 

or 

Int(Sum([20ENTERED]*20\500))

in fact, is the same as:
Sum([20ENTERED]*20\500)

Either option should see that you eliminate any compound fraction addition.