Google Answers Logo
View Question
 
Q: database concepts ( Answered 5 out of 5 stars,   2 Comments )
Question  
Subject: database concepts
Category: Computers > Software
Asked by: fahd-ga
List Price: $5.00
Posted: 26 Sep 2002 20:16 PDT
Expires: 26 Oct 2002 20:16 PDT
Question ID: 69561
Please help me understand a way to Create a Microsoft Access database
with 2 tables. The first table is a customer file with the following
data (some data may require more than one field):
ID number, Name, Address, Telephone, and Account Balance
The second table is a transaction file with the following data:
ID number, Date of transaction, Description of transaction, Quantity
of transaction, and Money amount of transaction
The key field for both tables is the ID number.
Enter data for a minimum of 3 customers into the customer file.
Establish Relationships and Cardinality in the relationships tab in
Access.
Answer  
Subject: Re: database concepts
Answered By: morningstar2000-ga on 27 Sep 2002 20:37 PDT
Rated:5 out of 5 stars
 
Dear fahd - 

   Thanks for the question.   I have done some work within access and
I feel that I might be able to help you out here.  What you are asking
is relatively simple but since I can not attach a database file here I
will have to explain it as best I can in words.   If by chance I miss
your mark please feel free to ask for clairification and I will do so.

  First off you must create the two desired tables.  You can do this
by either the wizard or the design view screens.   In table one you
will enter:
  Under Field Name
  - ID Number
  - Name
  - Address
  - Phone
  - Account Balance

In Table 2 you will enter:
  - ID Number
  - Transaction Date
  - Transaction Description
  - Transaction Quantity
  - Transaction Money

   After completing the two tables you will then go into the
relationship view screen.  Link the Customer ID in a one to many
relationship.  When you open the Customer table (table 1) a plus sign
will be displayed beside each record.  Clicking on that plus will drop
down all transactions assocaited with this customer.

  I will keep tabs on this question to ensure that this helped in
getting your database established.

Thanks for the opprotunity, 
 
     morningstar

Request for Answer Clarification by fahd-ga on 27 Sep 2002 21:41 PDT
thank you for the info. 
please help me create a sample table I have not done one before.

Clarification of Answer by morningstar2000-ga on 28 Sep 2002 12:15 PDT
Dear Fahd -  
   
    See my comments below for the clairification I accidentally put it
in the wrong area.

Thank you, 
Morningstar

Request for Answer Clarification by fahd-ga on 01 Oct 2002 17:34 PDT
for the last part of my question, what do I need to do to establish
Relationships and Cardinality in the relationships tab in Access.

Truly,

F.Ch.

Clarification of Answer by morningstar2000-ga on 01 Oct 2002 18:27 PDT
Dear Fahd - 
   
   I am not sure I understand.   Steps 8 - 11 are the relationship and
cardinality portion.

8.  Go to "Tools" and select "Relationships". 
 
9.  In the window that appears "Add" both tables. 
 
10.  You will see two small boxes in the window before you one called
"Customers" and one called "Transactions".  Click on "ID" in the
"Customers" box and drag it "ID" in the "Transactions" box.
 
11.  A small window will appear called "Edit relationships".  Click on
"Join Type" and Click "create".  Close relationships window and it
will ask you if you want to save it.  Click "Yes".


This will create the relationship and cardinality between the two
tables based on Customer ID.   Once completed you can enter data into
table two(transactions).  Once there is data in there you will be able
to use table one to see all transactions for each customer.   You just
click on the + sign in the far left hand field and it will show it to
you.

Please clarify which portion of this is not working for you.  Also
what version of Microsoft Access are you using?  I use Access 2000 so
the fields may look slightly different for you.

Let me know, 

Morningstar

Request for Answer Clarification by fahd-ga on 02 Oct 2002 10:02 PDT
thank you for all your help 
can you please help me with the following related question I have posted
about relateing the two tables through the ID number.  Create data
entry views with pick lists and data entry error checking for the
customer file and the transaction file.  Have the account balance in
the customer file be updated by transactions in the transaction file. 
Enter at least 3 transactions for each of a minimum of 3 customers and
check the functionality of the database. and Create a query that
orders the customer file by account balance in descending order.

truly

f.ch.

Clarification of Answer by morningstar2000-ga on 02 Oct 2002 11:20 PDT
Dear fahd - 
  
   This seems to be a completely different question.  I would be happy
to help you with it if you desire but it would need to be posted as a
new question.   I would like to point out however that the pricing on
your questions is low.  Also when you post the new question please
list the version of Access you are running this will help a researcher
guide you more accurately through the process of setting up views,
queries, reports, etc.   I appreciate your business and look forward
to serving you more in the future.

Morningstar

Request for Answer Clarification by fahd-ga on 03 Oct 2002 10:16 PDT
Once agian thank you for all your help.
The question is already posted as a new question, while it is related
to this question. I hope for your responce to it since you have helped
me on the first part of sitting the tables.

Truly

F.Ch.

Request for Answer Clarification by fahd-ga on 07 Oct 2002 20:33 PDT
Please let me know what I am doing wrong?
no matter what I do I still do not see the Small Plus Signs beside each record
but I have all data in place and both tables are created.

Truly,

f.ch.

Clarification of Answer by morningstar2000-ga on 14 Oct 2002 14:16 PDT
Dear F-ch - 

   I appoligize for not getting back to you.  I had thought this was
closed out.  If you could let me know which version of Access are you
running?  and at which point are we getting the hiccup.  I have
several versions of Access available but it would be helpful to use
the same one you have.   So I will keep my eye on this question again.

Thank you
morningstar
fahd-ga rated this answer:5 out of 5 stars
I would like to thank you for all your help 
your step-by-step answer was helpfull and easy to give me good understanding

Truly,
f.ch.

Comments  
Subject: Re: database concepts
From: morningstar2000-ga on 28 Sep 2002 09:12 PDT
 
Hi,

	Since I am unable to create attachments and post them here I will try
and walk you through the process.

1.  Open access and create a "File" and Select "New".  A window will
appear and you should select "Database". Click "Okay".

2.  Select the directory where you want the file to be saved and name
it (eg. CustomerTransactions) Click "Create".

3.  In the window that appears, click on "Create Table in Design View"

4.  On each row enter a field and the type data it should hold.  The
types of data can be accessed by a pull down menu in that block of the
form.
	Examples:
	ID	(Autonumber)
	Name	(Text)
	Address	(Text)
	Phone	(Text)
	Balance	(Currency)

5.  Right Click on "ID" and select "Primary Key"

6.  Close table and when asked if you would like to save it click
"Yes" and Name it "Customers"

7.  Repeat steps 1-6 to build the second table using the following
field info. Name this table "Transactions" But do NOT make ID the
primary key.
	ID		(Number)
	TransDate	(Date)
	TransDescription(Memo)
	TransQty	(Currency)
	TransMoney	(Currency)

8.  Go to "Tools" and select "Relationships".

9.  In the window that appears "Add" both tables.

10.  You will see two small boxes in the window before you one called
"Customers" and one called "Transactions".  Click on "ID" in the
"Customers" box and drag it "ID" in the "Transactions" box.

11.  A small window will appear called "Edit relationships".  Click on
"Join Type" and Click "create".  Close relationships window and it
will ask you if you want to save it.  Click "Yes".

	Now when you open the Customers table there will be small plus signs
beside each record.  Clicking on the plus sign will drop down all the
transactions for this customer.

Hopefully this clarifies any problems you were having.

Regards,
Morningstar
Subject: Re: database concepts
From: fahd-ga on 09 Oct 2002 13:26 PDT
 
Please 
can you help me
I have three more posted Questions under the subject of Database
Concepts
I would like to hear from you about them since your answer to this
question was very helpful.

Truly,
f.ch.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy