MS Access Relationship

Posted on 2007-04-07
Last Modified: 2013-11-24
Based on my customer and transaction database.. Can anyone make sense of the relationships bases on this table?
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
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
Question by:shoris
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +2

Expert Comment

ID: 18869638
What is this ID_NumberPk in both customer table it looklike customer id...what it in transaction table..

Author Comment

ID: 18869641
in the transaction its Transaction_ID
LVL 34

Accepted Solution

jefftwilley earned 500 total points
ID: 18869648
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.

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

LVL 34

Expert Comment

ID: 18869655
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 it should be and is used as the FK in the relationship.

Expert Comment

ID: 18869662
If it possible, I suggest you to break transaction table in two 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
Description of transaction
Money amount of transaction
LVL 34

Expert Comment

ID: 18869687
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 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

Author Comment

ID: 18869692
wow.. all that made sense.. amazingly.. How can i list the cardinality from these tables?
LVL 34

Expert Comment

ID: 18869722
what is cardinality?
LVL 11

Expert Comment

ID: 18869804
The cardinality is one-to-many. One customer can have many payments.
LVL 34

Expert Comment

ID: 18869851
Access has a tool called the Documenter


It will print out all the relationships within your database.

Expert Comment

ID: 18870084
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".

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question