Hi Charles
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)
CurName (text)
- the long name for the currency ("Euro", "US Dollar", "CFA Franc BEAC",
etc)
CurSymbol (text)
- the symbol for the given currency ("$", "£", "¥", "Fr", etc)
CurExchRate (numeric, double)
- the current exchange rate from the given currency to your base
currency
(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
TrnDate (date/time)
- 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
TrnAmount (currency)
- the amount in the *original* currency
(sorry, I think I left this important one out yesterday!)
TrnCurrency (text(3))
- 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
Charles Bourdette said:
Graham,
Thank you for helping me. I'm still confused. I'm a fisrt time user of the
newsgroup and don't know if it's good thing to send attached file but I'm
sending you a sample spreadsheet i'm using to records my transactions. If
you can give me advice I will really appreciate.
Thank you in advance for any help.
----- Original Message -----
From: "Graham Mandeno" <
[email protected]>
Newsgroups: microsoft.public.access.tablesdbdesign
Sent: Wednesday, December 10, 2008 12:17 AM
Subject: Re: need help with a database
Hi Charles
It seems you need a Transactions table with fields such as:
TransID (autonumber, primary key)
TransDate (date/time)
SenderID (related to Senders table)
BeneficiaryID (related to Beneficiaries table)
CurrencyUsed (dollars or Euro)
ExchangeRate (1 for dollars, whatever for Euro)
CommissionRate (8% or 10%)
TransStatus (in progress, complete, etc)
You probably want another table for Currencies with:
CurrencyCode (USD or EUR or ... [primary key])
CurrencyName
CurrencySymbol
ExchangeRate
CommissionRate
That way, the CurrencyUsed field above would contain a CurrencyCode
value,
and when the currency is selected for a transaction, the ExchangeRate and
CommissionRate fiends can be populated from the current values in the
Currencies table.
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
Charles Bourdette said:
Hello, I'm trying to create a database to keep track of my
customers'transactions and don't know which table to choose. Here is the
business process. A person sends money to another either in Euro or
Dollars. If it's in euro, i use an exchange rate to convert it in
dollars
and for each currency there is a commission rate in dollars ( 8% for
euros and 10% for dollars).After that the dollar amount is deposited to
the beneficiary account or paid in cash. Already created the beneficiary
table ( with id,name, telephone, account number, bank) and a table
sender
( with name, telephone,). I want to know at the end the amount received
by a customer, the currency, the rate applied ( if in euro), the
commission receveid and when. Thanks in advance.
