Dog club table design

[rivrlane]

I'm designing a database for a dog club. I started off with something
like

tblMembers
- autoindex
- name, address, phone. etc
- 2008 payment amount
- 2008 check number
- 2008 other accounting info

When I did the queries for the various reports, it seemed like the
changes from year to year would be messy. A better approach seemed
like

tblMemebers
- autoindex
- name address, phone, etc

tbl2008Account
- currentYr amount
- checkNumber
- other accounting fields

The assumption is that there would be a 2009 table for the next years
data.

This works great from the standpoint of my queries and reports. I can
move to a new year with just a few changes. But I can't quite figure
out how to get the two databases properly linked. I understand that
this is a one-to-one linkage, but I'm not clear what to use for the
2008Account key, nor what relationship settings to use.

Any help would be appreciated!

Greg

 

[Jeanette Cunningham]

Hi,
There is a sample membership database that you can use with the book
Microsoft Office Access 2003 by John L Viescas. I bought this book myself a
couple of years ago and I can highly recommend it. I have no ulterior profit
motive in suggesting this book.
It is a very easy to use book ( in my opinion) and worth much more than its
dollar value.

Jeanette Cunningham

 

[John W. Vinson]

I'm designing a database for a dog club. I started off with something
like

tblMembers
- autoindex
- name, address, phone. etc
- 2008 payment amount
- 2008 check number
- 2008 other accounting info

Then you started off WRONG...

When I did the queries for the various reports, it seemed like the
changes from year to year would be messy.

and realized it. Good on ya.

A better approach seemed
like

tblMemebers
- autoindex
- name address, phone, etc

tbl2008Account
- currentYr amount
- checkNumber
- other accounting fields

The assumption is that there would be a 2009 table for the next years
data.

That's STILL wrong.

You just need tblAccount with fields

MemberID <Long Integer, linked to the autoindex of tblMembers>
PaymentYear <Integer> or, better, PaymentDate |<Date/Time>
Amount
CheckNumber
<etc>

Short answer: *NEVER* store data in tablenames or in fieldnames. If you find
that you have a table with 2008, or a field with 2008, in its name (and the
2008 refers to the year) you are on the wrong path. Store data *in fields in
the table* - not in the structure of the table!


John W. Vinson [MVP]

 

[rivrlane]

Sorry, but that doesn't accomplish what I need. I need the data from
ALL years, not just the current one. I need the check from the 2008
payment, the 2009 payment, etc.

Thanks anyway.

Greg

 

[rivrlane]

Thanks for the reply!

Which of his many books, specifically? There seem to be several
series. One is "running...." another is "inside out".

Greg

 

[John W. Vinson]

Sorry, but that doesn't accomplish what I need. I need the data from
ALL years, not just the current one. I need the check from the 2008
payment, the 2009 payment, etc.

That is exactly and precisely what my suggestion will do.

There will be a record in the table for the 2008 payment.

There will be another record in the table for the 2009 payment.

There will hopefully eventually be a third record in the table for the 2010
payment.

You're using a relational database - *use it as a relational database*, by
storing data in records in tables! You do NOT need another table for each
payment!!!

John W. Vinson [MVP]

 

[Klatuu]

Pay attention to what John is telling you. This is as normal as it gets. It
is a common transaction table that contains information about a transaction.
It doesn't matter when the payment is made, all payments, dispbursals, and
adjustments go into the table. To do it your way would only make more work.
So what if we want to capture monthly data? Do we need a table for each month?

Imagine if a bank had to create new tables for its transactions each year?

 

[rivrlane]

You're still missing what my needs are. The DATE of the check is
meaningless. We can receive memberships for 2008 as early as sometime
in 2006 and we continue to receive them right up until November 2008.
The actual membership begins the first weekend of 2008 and ends the
last weekend of 2008, regardless of when it was paid. "2008" isn't a
DATE for us, it's a season designation. All AKC dog clubs work this
way, more or less. It is perfectly reasonable to recieve a payment
for 2008 one week and 2009 the next from the same person.

 

[John W. Vinson]

You're still missing what my needs are. The DATE of the check is
meaningless. We can receive memberships for 2008 as early as sometime
in 2006 and we continue to receive them right up until November 2008.
The actual membership begins the first weekend of 2008 and ends the
last weekend of 2008, regardless of when it was paid. "2008" isn't a
DATE for us, it's a season designation. All AKC dog clubs work this
way, more or less. It is perfectly reasonable to recieve a payment
for 2008 one week and 2009 the next from the same person.

Fine. Include a field (Number/Integer Datatype) in the table for
MembershipYear. This field would be independent of the date the check was paid
(a point which may be obvious to AKC members but was not to me from your
previous posts).

You're still missing *my* point:

You do NOT NEED ANOTHER TABLE for every membership year.

You just need another *record* in the table for each member for every
membership year.

John W. Vinson [MVP]

 

[Pat Hartman]

The one field that is missing from this discussion is the MemberID which I
am assuming is the autoindex field in tblMembers. You need the MemberID in
the tblAccount so that you can relate a member to his accounting data. John
has given you good advice on how to structure payments but we need a little
more information. The tblAccount will need a primary key. It can be an
autonumber or it can be a natural key. The question that needs to be
answered is do you have a natural key that is suitable as the primary key
for this table? If a member makes only one payment a year, then a
possibility might be a composite key of MemberID and MembershipYear.
However if members may make multiple payments or you need to record other
transactions then this would not be appropriate. Also the "other accounting
info" is a little scary. Are you sure that you don't need a transaction
code so that you can manage a "list" of financial actions by a member?

 

[Brucels]

Greg,

I developed and maintain a membership database for an international group of
approx. 400 members. I can assure you that John is advising you appropriately.

My database has three basic tables:
1. Members names and personal info
2. Members professional interests (multiple per member)
3. Members annual dues payments (multiple per member)

Table 3 has fields for a secondary key (the member's ID #), check date, name
on the check, check amount, date entered, and dues "season" (2007-2008, for
example).

I have created a form with table 1 information and subforms in datasheet
view for tables two and three. It really works well for data review and
entry. I can send you a copy, if you are interested in seeing the structure.

Best,
Bruce