If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Limit on number of tables?
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 |
#2
|
|||
|
|||
Limit on number of tables?
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 DonorNumber DonorSalutation DonorFirstName DonorLastName DonorBirthdate DonorAdd1 DonorAdd2 DonorCity etc. TblDonations ClubNo (related to TblClubs) DonorNo (related to TblDonors) DonationAmount DonationDate DonationComment etc. -- Rick B "jiaozp" wrote in message ... 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 |
#3
|
|||
|
|||
Limit on number of tables?
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. -- Jerry Whittle Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "jiaozp" wrote: 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 |
#4
|
|||
|
|||
Limit on number of tables?
On Wed, 19 Apr 2006 07:49:01 -0700, jiaozp
wrote: 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/conrad...abaseDesign101 John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
page number positioning and format | tinaa | Page Layout | 3 | February 4th, 2006 12:38 AM |
Formula to limit the size of a number result in a cell? | Pheasant Plucker® | General Discussion | 3 | February 2nd, 2006 11:14 AM |
Report with set number of rows per page | Ken from Chicago | General Discussion | 7 | January 4th, 2006 02:42 PM |
Need Subforms? | AccessRookie | Using Forms | 7 | April 8th, 2005 09:30 AM |
Limit to Number of Tables | T'Kai | Database Design | 1 | June 8th, 2004 03:44 PM |