Solved

MS Access Relationship

Posted on 2007-04-07
11
347 Views
Last Modified: 2013-11-24
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
0
Comment
Question by:shoris
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 3

Expert Comment

by:AbhishekSharma
Comment Utility
What is this ID_NumberPk in both table...in customer table it looklike customer id...what it in transaction table..
0
 

Author Comment

by:shoris
Comment Utility
in the transaction its Transaction_ID
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
Comment Utility
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
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
 
LVL 3

Expert Comment

by:AbhishekSharma
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
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
 

Author Comment

by:shoris
Comment Utility
wow.. all that made sense.. amazingly.. How can i list the cardinality from these tables?
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
what is cardinality?
0
 
LVL 11

Expert Comment

by:flipz
Comment Utility
The cardinality is one-to-many. One customer can have many payments.
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
Access has a tool called the Documenter

Tools/Analyze/Documenter

It will print out all the relationships within your database.
0
 
LVL 5

Expert Comment

by:rmaranhao
Comment Utility
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now