A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

need help with a database



 
 
Thread Tools Display Modes
  #1  
Old December 9th, 2008, 09:43 PM posted to microsoft.public.access.tablesdbdesign
Charles Bourdette
external usenet poster
 
Posts: 3
Default need help with a database

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.

  #2  
Old December 9th, 2008, 11:17 PM posted to microsoft.public.access.tablesdbdesign
Graham Mandeno
external usenet poster
 
Posts: 593
Default 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" wrote in message
...
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.



  #3  
Old December 10th, 2008, 12:48 PM posted to microsoft.public.access.tablesdbdesign
Charles Bourdette
external usenet poster
 
Posts: 3
Default need help with a database

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"
Newsgroups: microsoft.public.access.tablesdbdesign
Sent: Wednesday, December 10, 2008 12:17 AM
Subject: 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" wrote in message
...
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.





  #4  
Old December 10th, 2008, 10:56 PM posted to microsoft.public.access.tablesdbdesign
Graham Mandeno
external usenet poster
 
Posts: 593
Default need help with a database

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" wrote in message
...
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"
Newsgroups: microsoft.public.access.tablesdbdesign
Sent: Wednesday, December 10, 2008 12:17 AM
Subject: 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" wrote in message
...
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.







  #5  
Old December 11th, 2008, 12:29 PM posted to microsoft.public.access.tablesdbdesign
Charles Bourdette
external usenet poster
 
Posts: 3
Default need help with a database

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.

"Charles Bourdette" wrote in message
...
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.


  #6  
Old December 11th, 2008, 09:15 PM posted to microsoft.public.access.tablesdbdesign
Graham Mandeno
external usenet poster
 
Posts: 593
Default need help with a database

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 he http://www.amazon.com/dp/0321290933

For a good book on going forward with your application development, try
"Building Microsoft Access Applications " by John Viescas:
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


"Charles Bourdette" wrote in message
...
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.

"Charles Bourdette" wrote in message
...
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.




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:17 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.