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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|