It is generally frowned upon to attach files to newsgroup postings.
Nevertheless, your spreadsheet was helpful :-)
You said that you already have tables for Senders and Beneficiaries. Each
of these tables must have a unique primary key - let's say they are named
SndID and BnfID respectively, and that they are autonumbers (automatically
generated long integer numbers).
I'm suggesting that you add two more tables - Currencies and Transactions.
Currencies contains information about the currencies in which donations may
be made. Let's say it contains the following fields:
CurCode (text(3), primary key)
- the 3-letter international currency code (EUR, USD, XAF, etc)
- the long name for the currency ("Euro", "US Dollar", "CFA Franc BEAC",
- the symbol for the given currency ("$", "£", "¥", "Fr", etc)
CurExchRate (numeric, double)
- the current exchange rate from the given currency to your base
(for the base currency, this would be 1)
CurCommRate (numeric, double)
- the commission rate for this currency
[note that this belongs here only if the commission rate is directly
dependent on the currency]
Now, your transaction table needs the following fields:
TrnID (autonumber, primary key)
- may be used to uniquely identify transactions
- the date of the transaction
TrnSender (numeric, long)
- the SndID value from the related record in the Senders table
TrnBeneficiary (numeric, long)
- the BnfID value from the related record in the Beneficiaries table
- the amount in the *original* currency
(sorry, I think I left this important one out yesterday!)
- the 3-letter currency code of the original currency
TrnExchRate (numeric, double)
- the exchange rate to apply for this transaction
TrnCommRate (numeric, double)
- the commission rate to apply for this transaction
Notice that there are no fields in this table for data such as:
- sender's name
- sender's telephone
- beneficiary's name and other details
All these data items can be obtained in a query by "joining" the
Transactions table to the Senders and Beneficiaries tables.
Also, notice that there are no fields for:
- amount converted to base currency
- amount of commission
- total amount
All these can be *calculated* in a query (as, I am sure, you are calculating
them in your spreadsheet).
The only data items that are duplicated in the Transactions table are the
exchange rate and the commission rate, because these can presumably change
over time. It is therefore important to store in the transaction record the
values that apply to that particular transaction.
Note that these tables are *related*. After you have created the tables,
you should open the Relationships Window and create relationships between:
SndID and TrnSender
BnfID and TrnBeneficiary
CurCode and TrnCurrency
Having created your related tables, you can then create queries and forms
and reports to manage your data.
Try never to use tables and queries for entering and editing data. Observe
the following rules:
- Tables are for *storing* data - nothing else!
- Queries are for selecting, assembling, and sorting data for viewing
and/or editing, but NOT for the actual process of viewing and editing.
- Forms are for viewing and/or editing data
- Reports are for printing data.
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand