MS Access Relationship

Based on my customer and transaction database.. Can anyone make sense of the relationships bases on this table?
Customer
 
ID_NumberPK      Name      Address      City      State/Province      Postal Code      Country      Telephone      Account Balance
1      Lisa Manny      3900 Lewis Dr.      Novi      Mi      48377-       United States      (248) 564-6353      $3,500.00
2      Sara Johen      7638 Madson St.      Livonia      Mi      48326-       United States      (248) 474-7473      $5,000.00
3      Nathan Jones      16th Street      Northville      Mi      48327-       United States      (248) 848-4774      $4,500.00
 
Transaction
 
ID_NumberPK      Date of transaction      Description of transaction      Quantity of transaction      Money amount of transaction
1      2/2/2007      Cheque      2      $3,500.00
2      3/1/2007      Cash      3      $2,000.00
3      1/18/2007      Charge      2      $1,500.00
1      2/1/2007      Cheque      1      $1,000.00
2      1/2/2005      Cash      2      $1,575.00
shorisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AbhishekSharmaCommented:
What is this ID_NumberPk in both table...in customer table it looklike customer id...what it in transaction table..
0
shorisAuthor Commented:
in the transaction its Transaction_ID
0
jefftwilleyCommented:
Customer Lisa Manny, who lives at 3900 Lewis dr. Novi Mi. 48377 US Phone 248-564-6353 has apparently made two payments. The first on 2/1/2007 in the form of a Check #1 in the amount of $1000.00, then again on 2/2/2007 with Check #2 in the amount of $3500.00

So the relationship is One to Many Customer to Transaction Where the PRimary Key in customer is the Foreign Key in Transaction.

?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jefftwilleyCommented:
You would do well to add another ID field in your transaction table that's an Autonumber to give your records a unique value. Chances are, the ID number in that table now is a simple Number...as it should be and is used as the FK in the relationship.
J
0
AbhishekSharmaCommented:
If it possible, I suggest you to break transaction table in two parts....one is main table and other sub table...

Main table...
Transaction id (PK)
Customer id (FK)
Date of transaction
Quantity of transaction
Amount of Transaction

Sub Table
ID_NumberPK      
Description of transaction
Money amount of transaction
0
jefftwilleyCommented:
We really don't have enough information to give you an informed interpretation. Generally....when payments are being made, they go against a total. This total would reduce with each payment. Now...if this database is for something like Donations..then that Total would be accumulative, and you wouln't need another table to store that...you just calculate it in a query/Form or report. What if your customer has 2 accounts, then they payment would have to be applied to a specific account number.

So truth is...we don't know
0
shorisAuthor Commented:
wow.. all that made sense.. amazingly.. How can i list the cardinality from these tables?
0
jefftwilleyCommented:
what is cardinality?
0
flipzCommented:
The cardinality is one-to-many. One customer can have many payments.
0
jefftwilleyCommented:
Access has a tool called the Documenter

Tools/Analyze/Documenter

It will print out all the relationships within your database.
0
rmaranhaoCommented:
Try to run this:
(create a new query, got to SQL mode and paste the statement below)


select * from customer, transaction where customer.ID_NumberPK =transaction.ID_NumberPK  order by customer.ID_NumberPK


Sorry if the command is not New Query in english, in portuguese it's "Nova Consulta".
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.