Windows 7 - need help with a database

Asked By Charles Bourdette on 09-Dec-08 03:43 PM
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.




Graham Mandeno replied on 09-Dec-08 05:17 PM
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
Graham Mandeno replied on 10-Dec-08 04:56 PM
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 replied on 11-Dec-08 06:29 AM
Graham,
Thank you for all your help. I had taken a course on Microsoft Access and
they only taught me how to use access tools and functions but nothing about
database modeling. This why i was strugggling to define my tables entity and
model them. I can see you're very proficient about modeling. Is it possible
to teach me about modeling or recommend some readings so that I will be able
to do things myself. I'm very willing to learn about modeling cause it's the
basics before putting your hands into access.
I have followed your blueprint and things seems to work fine. I really
appreciate your help.
Graham Mandeno replied on 11-Dec-08 03:15 PM
Hi Charles

I'm glad you're making good progress.  You are obviously a fast learner :-)

In my opinion, most Access courses are taught the wrong way around.  I don't
see how you can start to use Access effectively until you understand the
basics of data modelling and database design.

I can recommend a very good general book to get you started with the basics
of database design.  It is "Designing Effective Database Systems" by Rebecca
Riordan.  More info here: http://www.amazon.com/dp/0321290933

For a good book on going forward with your application development, try
http://www.amazon.com/dp/0735620393

And of course, you can always come back here with more questions :-)
--
Good Luck  :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand