Limit on number of tables?

[jiaozp]

1) Is there a limit on number of tables in one Access file?

2) What's the best way to organize the following information in tables? My
current thinking would involve numberous tables (close to 400 or so)

Table 1:
Club Name
Address
Contact Person

Table 2:
Donor Name
Age
Occupation

I also need to show for each Club, all the existing donors and amt donated.
I have A LOT of clubs and am thinking of creating a table for each club. It
is the simpliest way for me to maintain this database since we get
information for a single club at a time. Is this the best way to do this? Is
there a limit on number of tables?


Thanks,

Jiaozp

 

[Rick B]

You should have one table for your clubs and one table for your donors.
Just like you show below. You should NOT have a separate table for each
club.

You should consider several other issues in your donor table. First, I
would not store name in one field. I'd store "title", "FirstName",
LastName", and "Middle". That will allow you to print the whole name, print
in "lastname, firstname" format, Address letters, etc. You will also be
able to sort reports by last name more easily.

Secondly, you should not store "AGE". That is a moving target. You should
store the person's birthdate (a set date). You can calculate their age in
your forms, reports, and queries when you need to know how old they are.

You would also likely need a third table to track the actual donations.
This table should include the ClubNumber and the Donor Number (unique keys
that you need to add to your other two tables.) It will also include a
donation amount and date. Each donation will have a separate entry in the
table. If one donor sends two checks for a particular club, then there
would be two entries in your donation table. If one donor sends two checks
for "club A" and three checks for "club b", then this would result in five
entries in your donations table.


TblClubs

ClubNumber
ClubName
ClubAdd1
ClubAdd2
ClubCity
ClubState
ClubZIP
ClubPhone
ClubFax
etc.
TblDonors

TblDonations
ClubNo (related to TblClubs)
DonorNo (related to TblDonors)
DonationAmount
DonationDate
DonationComment
etc.

 

[Jerry Whittle]

400 tables? No way. You should be able to do what you describe in under 10
tables.

A table of Clubs with club info like address, name, etc.

Possibly a table of Club contacts if a club could have more than one contact.

A Donors table as a donor could be a member of more than one club. Do NOT
put in the donor's age. Rather put in their birthdate or birthyear and
compute their age as needed.

A bridging or linking table that links the Club with Donors. This table
could also include the amount and date of the donation so that the database
could be used for more than one year.

I highly suggest getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceding any further
on this database.

 

[John Vinson]

2) What's the best way to organize the following information in tables? My
current thinking would involve numberous tables (close to 400 or so)

Three tables. Maybe four. Probably more later but no more than three
for what you've described!

Check out some of the links at

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101


John W. Vinson[MVP]