denomination breakdown

[Daron]

Is it possible to use SQL to take a field, and break it down by
denominations?

I would like to take a field, and then break this out into the number
of bills($100's, $50's, etc) I would need:
$469.32 =
4x$100
1x$50
1x$10
1x$5
4x$1
etc.

[paii, Ron]

SELECT [tblCash].[Amount], [tblCash].[Amount]\100 AS N100, [N100] & "x$100"
AS Expr1, ([tblCash].[Amount]-[N100]*100)\50 AS N50, [n50] & "x$50" AS
Expr2, ([tblCash].[Amount]-[N100]*100-[N50]*50)\10 AS N10, [n10] & "x$10" AS
Expr3, ([tblCash].[Amount]-[N100]*100-[N50]*50-[n10]*10)\5 AS n5, [n5] &
"x$5" AS Expr4, ([tblCash].[Amount]-[N100]*100-[N50]*50-[n10]*10-[n5]*5)\1
AS n1, [n1] & "x$1" AS Expr5,
CInt(([tblCash].[Amount]-[N100]*100-[N50]*50-[n10]*10-[n5]*5-[n1])*100) AS C
FROM [tblCash];

"Daron" <Da**********@gmail.comwrote in message
news:11**********************@u30g2000hsc.googlegr oups.com...

Is it possible to use SQL to take a field, and break it down by
denominations?

I would like to take a field, and then break this out into the number
of bills($100's, $50's, etc) I would need:
$469.32 =
4x$100
1x$50
1x$10
1x$5
4x$1
etc.

[CDMAPoster]

On May 15, 2:44 pm, Daron <[e-mail]:

Is it possible to use SQL to take a field, and break it down by
denominations?

I would like to take a field, and then break this out into the number
of bills($100's, $50's, etc) I would need:
$469.32 =
4x$100
1x$50
1x$10
1x$5
4x$1
etc.

This is an interesting problem. To solve the problem in complete
generality is difficult because all the Denomination values need to be
known in order to be confident that the correct remaining amount is
calculated. I believe that for the example you give (100, 50, 10, 5,
1, ...) that this is not the case and that it is sufficient to know
only the previous Denomination in order to produce the
breakdowns :-). Also, I didn't test the limits beyond the example I
give.

tblDenomination
DID AutoNumber
Denomination Double
DenominationName Text
DID Denomination DenominationName
1 100 C-note
2 50 Fiddy
3 10 Sawbuck
4 5 Five
5 1 Buck
6 0.5 Half Dollar
7 0.1 Dime
8 0.05 Nickel
9 0.01 Penny

tblI
ID AutoNumber
I Long
ID I
1 1
2 2
....
18 18

tblBreakdown
BID AutoNumber
BreakdownAmount Currency
BID BreakdownAmount
1 $1,234,469.32

qryMakeChange:
SELECT (SELECT A.Denomination FROM tblDenomination AS A WHERE A.DID =
tblI.I) AS Denomination, Nz((SELECT A.Denomination FROM
tblDenomination AS A WHERE A.DID = tblI.I - 1), 0) AS
PrevDenomination, Int(tblBreakdown.BreakdownAmount / (SELECT
A.Denomination FROM tblDenomination AS A WHERE A.DID = tblI.I - 1))
AS X, Int(tblBreakdown.BreakdownAmount / (SELECT A.Denomination FROM
tblDenomination AS A WHERE A.DID = tblI.I)) AS Y, BreakdownAmount - Y
* Denomination As Remainder, BreakdownAmount - Nz(X, 0) *
PrevDenomination As PrevRemainder, (PrevRemainder - Remainder) /
Denomination AS HowMany, (SELECT A.DenominationName FROM
tblDenomination AS A WHERE A.DID = tblI.I) AS DenominationName FROM
tblBreakdown, tblI WHERE tblI.I <= (SELECT Count(*) FROM
tblDenomination) ORDER BY BID, I;

!qryMakeChange:
Denomination PrevDenomination X Y Remainder PrevRemainder HowMany
DenominationName
1000 0 Null 123 $4,469.32 $1,234,469.32 123 10G
5000 10000 123 246 $4,469.32 $4,469.32 0 5G
1000 5000 246 1234 $469.32 $4,469.32 4 Grand
500 1000 1234 2468 $469.32 $469.32 0 Five Hundy
100 500 2468 12344 $69.32 $469.32 4 C-note
50 100 12344 24689 $19.32 $69.32 1 Fiddy
10 50 24689 123446 $9.32 $19.32 1 Sawbuck
5 10 123446 246893 $4,32 $9.32 1 Five
1 5 246893 1234469 $0.32 $4.32 4 Buck
0.5 1 1234469 2468938 $0.32 $0.32 0 Half Dollar
0.1 0.5 2468938 12344693 $0.02 $0.32 3 Dime
0.05 0.1 12344693 24689386 $0.02 $0.02 0 Nickel
0.01 0.05 24689386 123446932 $0.00 $0.02 2 Penny

If tblBreakdown has more than one amount, the query should show each
breakdown consecutively. Note that the breakdown is incorrect if the
DID values aren't consecutive or if, say, quarters are added to the
mix. I may come back to this problem later.

James A. Fortune
CD********@FortuneJames.com

[CDMAPoster]

On May 19, 10:50 pm, [e-mail] wrote:

On May 15, 2:44 pm, Daron <[e-mail]:

Is it possible to use SQL to take a field, and break it down by
denominations?

I would like to take a field, and then break this out into the number
of bills($100's, $50's, etc) I would need:
$469.32 =
4x$100
1x$50
1x$10
1x$5
4x$1
etc.

This is an interesting problem. To solve the problem in complete
generality is difficult because all the Denomination values need to be
known in order to be confident that the correct remaining amount is
calculated. I believe that for the example you give (100, 50, 10, 5,
1, ...) that this is not the case and that it is sufficient to know
only the previous Denomination in order to produce the
breakdowns :-). Also, I didn't test the limits beyond the example I
give.

tblDenomination
DID AutoNumber
Denomination Double
DenominationName Text
DID Denomination DenominationName
1 100 C-note
2 50 Fiddy
3 10 Sawbuck
4 5 Five
5 1 Buck
6 0.5 Half Dollar
7 0.1 Dime
8 0.05 Nickel
9 0.01 Penny

tblI
ID AutoNumber
I Long
ID I
1 1
2 2
...
18 18

tblBreakdown
BID AutoNumber
BreakdownAmount Currency
BID BreakdownAmount
1 $1,234,469.32

qryMakeChange:
SELECT (SELECT A.Denomination FROM tblDenomination AS A WHERE A.DID =
tblI.I) AS Denomination, Nz((SELECT A.Denomination FROM
tblDenomination AS A WHERE A.DID = tblI.I - 1), 0) AS
PrevDenomination, Int(tblBreakdown.BreakdownAmount / (SELECT
A.Denomination FROM tblDenomination AS A WHERE A.DID = tblI.I - 1))
AS X, Int(tblBreakdown.BreakdownAmount / (SELECT A.Denomination FROM
tblDenomination AS A WHERE A.DID = tblI.I)) AS Y, BreakdownAmount - Y
* Denomination As Remainder, BreakdownAmount - Nz(X, 0) *
PrevDenomination As PrevRemainder, (PrevRemainder - Remainder) /
Denomination AS HowMany, (SELECT A.DenominationName FROM
tblDenomination AS A WHERE A.DID = tblI.I) AS DenominationName FROM
tblBreakdown, tblI WHERE tblI.I <= (SELECT Count(*) FROM
tblDenomination) ORDER BY BID, I;

!qryMakeChange:
Denomination PrevDenomination X Y Remainder PrevRemainder HowMany
DenominationName
1000 0 Null 123 $4,469.32 $1,234,469.32 123 10G
5000 10000 123 246 $4,469.32 $4,469.32 0 5G
1000 5000 246 1234 $469.32 $4,469.32 4 Grand
500 1000 1234 2468 $469.32 $469.32 0 Five Hundy
100 500 2468 12344 $69.32 $469.32 4 C-note
50 100 12344 24689 $19.32 $69.32 1 Fiddy
10 50 24689 123446 $9.32 $19.32 1 Sawbuck
5 10 123446 246893 $4,32 $9.32 1 Five
1 5 246893 1234469 $0.32 $4.32 4 Buck
0.5 1 1234469 2468938 $0.32 $0.32 0 Half Dollar
0.1 0.5 2468938 12344693 $0.02 $0.32 3 Dime
0.05 0.1 12344693 24689386 $0.02 $0.02 0 Nickel
0.01 0.05 24689386 123446932 $0.00 $0.02 2 Penny

If tblBreakdown has more than one amount, the query should show each
breakdown consecutively. Note that the breakdown is incorrect if the
DID values aren't consecutive or if, say, quarters are added to the
mix. I may come back to this problem later.

James A. Fortune
[e-mail]

I had four more Denominations when I ran this test. Also, the first
line from !qryMakeChange should have started with 10000.

James A. Fortune
CD********@FortuneJames.com