Strapped Currency

  • Thread starter Thread starter margaret
  • Start date Start date
M

margaret

I have a a query I'm going to use for a report that figures cash. For
instance, if a seller comes in with 20 $20's, 15 1's, 10 5's, etc. it will
tell us how much cash that seller has. I need to know after all sellers are
counted how much currency we now have. But I need it figured in rolls and
straps. for instance, 100 of each currency will make a bank strap. If I
have 1075 ones, I need the query to figure that I have 10 straps and 75 ones
left. I will be running a report from this query. Can it be done?
 
If the table is like:


Seller BillValue Quantity

1010 20 20
1010 15 1
1010 10 5



then, to get the total, by seller:

SELECT seller, SUM(billValue * quantity) AS totalValue
FROM tableName
GROUP BY seller


and to get the total, by bill value:


SELECT billValue, SUM(quantity) As totalQuantity
FROM tableName
GROUP BY billValue


In the report, you can use:

(totalQuantity \ 100) & " strap(s) and " & (totalQuantity MOD 100)

to get the number of straps and the number of bills (not making a strap).

Can also compute billValue * totalQuantity to get the total value for this
bill value.



Vanderghast, Access MVP
 
So one transaction can consist of several denominations, and one
denomination turns up in many transactions. That's a classic many-to-many
relation, so you will need tables like this:

Denomination table, with one field:
- DenomID Currency primary key.

Transaction table, with fields:
- TransID AutoNumber primary key
- TransDate Date/Time when
- ClientID relates to the primary key of the client (seller?)
table.

TransactionDetail table, with fields:
- TransDetailID AutoNumber primary key
- TransID which transaction this row belongs to
- DenomID what denomination
- Quantity how many of this denomination in this transaction.

To interface this, use a main form bound to the Transaction table, with a
subform bound to the TransactionDetail table. The subform will be in
continuous form view, so you can enter several rows in a transaction. Use a
combo box for selecting the denomination ($2, $5, $10, etc). In the Form
Footer, add a text box with Control Source of:
=Sum([DenomID] * [Quantity])
As soon as each row is saved, Access will update this transaction total for
you.

At the end of the day, you then know how many of each denomination you
received, so you can box them us as needed.
 
This is a bit of a different angle on what has been posted already.

With ones you can use something like this as a calculated query column to
determine the number of straps:
OnesStraps: Int([Ones]/100)
For the number of ones, in another calculated column:
OnesExtra: [Ones] Mod 100
If the value of all ones is 1075, the first expression will return 10, and
the second one will return 75.
If the number of ones is less than 100 this expression will return that
number. By way of example, look at this expression (using an actual number
for demonstration):
OnesExtra: 90 Mod 100
This will return 90.

From my tests it seems this will work whether [Ones] is a currency field or
the number of ones (a number field). For other denominations you will need
to do math if you are storing the total currency value. For instance, for
fives:
FivesStraps: Int(([Fives]/5)/100)
FivesExtra: ([Fives]/5) Mod 100
..
 
Back
Top