Simple Database

[John]

Hi,
I have a simple database where I have a number of members of a club and
I want to record their personal details and their membership fees and other
expenses as they pay them. I have created two tables, Members and Payments
and need to know how to connect them correctly. I assume I am looking at a
"one to many" relationship. I have allowed Access generate the primary keys
in both tables. The fields are as follows:

Members Payments

ID Member
ID
Member ID Members Name
Title Payment
ID
First Name Fee Amount
Last Name Card Type
Add1 Date
Due(From Class and Frequency)
Add2 Date Paid
Phone Card Holder
Memb Category Notes
Payment Method
Frequency
Class

I need to know how to join the two table so that data from the Members table
automatically populates the payments fields as soon as I type in the Members
ID. Am I designing this thing right or should I start over.?
TIA John

 

[John Vinson]

I need to know how to join the two table so that data from the Members table
automatically populates the payments fields as soon as I type in the Members
ID. Am I designing this thing right or should I start over.?
TIA John

Well, you don't need to start over - but you should back up a bit.

It is NOT appropriate to put any member information other than the
MemberID in the Members table. There is no benefit to storing that
information redundantly! Access is a *relational* database system; you
can see the member information in assoication with the payment
information by creating a Query joining the two tables, or by using a
Form for the Member table with a Subform for the payments for that
member.

John W. Vinson[MVP]

 

[John]

Hi John,
but I had imagined that having a members table meant that I was
only inputting the members personal details once.The payments table is for
payment transactions as they occur.O.K. I can see that it is not necessary
to duplicate the data in both tables........so Member ID is all that is
required in the payments table, but from the point of view of the imputer it
is good to be able to see the member details as the information is being
input......would you agree?

----- Original Message -----
From: "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com>
Newsgroups: microsoft.public.access.gettingstarted
Sent: Saturday, January 29, 2005 2:37 AM
Subject: Re: Simple Database

 

[John Vinson]

Hi John,
but I had imagined that having a members table meant that I was
only inputting the members personal details once.

Exactly. you input them once, and store them once.

The payments table is for
payment transactions as they occur.O.K. I can see that it is not necessary
to duplicate the data in both tables........so Member ID is all that is
required in the payments table, but from the point of view of the imputer it
is good to be able to see the member details as the information is being
input......would you agree?

Absolutely. That's why you would use the powerful tools which Access
provides - Forms. You would NOT enter data directly into table
datasheets.

Instead, you would use a Form based on the Members table - which has
all of the member information plainly visible; with a Subform based on
the Payments table. The Subform master/child link field relationship
will maintain the MemberID link between the tables; the user need not
even *see* the memberID, much less need to enter it.

John W. Vinson[MVP]

 

[Todd Shillam]

John,

When you create a table, you want to create a table to store information
about a group of objects that share the same attributes (or fields). For
example, you could build a table for cars. The attributes (your fields)
could be make, model, year, seating capacity. In contrast, you want to avoid
creating a table that would store data about two or more groups of objects,
such as people and cars--you would separate the tables. Note: Objects can be
anything, including events, such as your payments table. Your payments table
is storing information about payment events (or instance)--just think about
the fields associated with a payment event. Ok...let me think....there's
date, time, fee...what else can you think of?

Looks like you could normalize the database in a couple other places, such
as CardType and CardHolder--get some good practice here and build a couple
more small tables for CardHolder and CardType.

===========
tblCardHolders
===========
CardHolderID (Autonumber) As PrimaryKey
CardHolder
CardTypeID


===========
tblCardTypes
===========
CardTypeID (Autonumber) As PrimaryKey
CardType

Best regards,

Todd

 

[John]

Thanks loads guys,....I am starting to get the picture.John