Welcome to the forum.
If you print one award per scholarship, it makes sense to me that you would have one record per scholarship on a "Scholarship" Table. This table could be linked to the People table by one to many or many to many relationships as appropriate (one person has multi scholarships...does one scholarship go to many people?)
You would then print from the records on the Scholarship table, instead of the People table, drawing pertinent info from the People table onto the layout as appropriate.
Did I oversimplify your need?
Thank you for your reply.... Yes, one company may donate several scholarships. For example: Bixby Medical Center Auxillary donates 21 scholarships that range from $500 to $900 each.
good and necessary supporting info, but I didn't understand the answer to the question that I asked...
As I understand so far, you have one person who can get multi-scholarships
you have one company that can give multi-scholarships
No problem so far...
do you have one scholarship that goes to many people?
Do you have one scholarship that came from many companies?
If the answer to these last two questions is "No", then you have two one-to-many relationships. In this case, put your Scholarship Table as Parent Table, and have your Recipient Table and Provider Table support it. When you print, print from the scholarship table and you're home free.
If the answer to either of the last two questions was "Yes", then you have at least one many-to-many relationship which is more complicated.
Let us know...
No, I do not have one scholarship that goes to multiple people. And no, I do not have one scholarship from multiple companies.
But, Jane Doe could win the Kelly Carter Scholarship, the Meyers Foundation Scholarship, and the John Dawson Scholarship....I'm confused on the best way to design the database to efficiently run the certificates.
So, should I create a 'recipient' table and a 'scholarship' table.....then create a record for each scholarship (example: 21 records for the 21 Bixby Medical Center Auxillary Scholarships) in the 'scholarship' table and then pull the recipient info in and print the certificates from there?
I sincerely apologize if this is confusing!!
You're not confusing...you're doing fine.
Table 1 = Scholarships. One record per scholarship awarded. Holds Scolarship specific Info (amount, award date, scholarship name, etc.)
Table 2 = Recipient. One record per unique recipient. Holds recipient specific info (Name, Add., Phone, etc.) Related to Scholarship table via uniqueID1.
Table 3 = Donor. One record per unique donor. Holds donor specific info (Name, Add., Phone, Contact person, etc.) Related to Scholarship table via uniqueID2.
When you go to print, find the Scholoarships you want to print on a layout based on the Scholarship table which also holds the appropriate fields from the related tables such as RecipientName, DonorName, etc.
This is what you're after. Did I give enough info to move forward or should I describe more step-by-step?
I'm sorry, I do need more step-by-step.......Thank you so much for all of your help.
I know how to create the tables and stuff, it's creating the relationships that I'm stuck on.
Sorry, one more question. With the directions you gave me with the 3 different tables, how do I tie in what recipient won which scholarship on the certificates? I can't remember if I mentioned what was needed on the certificates......they will have the recipient name and what scholarship they won.
Table1 = Scholarships. It would have a field called "UniqueID1" or "JoinToRecipient" and another field called "UniqueID2" or "JoinToDonor".
Table 2 = Recipient. It would have a field called "UniqueID1" or "JoinToScholarship"
Table 3 = Donor. It would have a field called "UniqueID2" or "JoinToScholarship"
Under File>Define/Manage Database, click on the relationship tab and you'll see your three tables graphically. Drag Scholarship::JoinToRecipient to Recipient::JoinToScholarship and you'll see a relationship line appear.
Do the same for joining Scholarship::JoinToDonor to Donor::JoinToScholarship.
Now base your award certificate layout on the Scholarship table (Layout>LayoutSetup), populate it with fields from Recipient (Name, Address, etc.), with fields from Donor (Corporation, Award name, contact person, etc.) and from the Scholarship table itself (amount, date, etc.) as you'd like the award to look.
Next, walk yourself through making value lists using the help menus. Look specifically at making value lists based on field contents. Make a value list "Donor" based on Donor::JoinToScholarship showing only values from second field Donor::Corporation. Make another Value list "Recipient" based on Recipient::JoinToScholarship showing only values from second field Recipient::Name.
Put the LINKING fields on your layout. Set the LINKING fields in your award layout to be dropdowns based on these value lists. Set the LINKING fields as "do not print selected objects" under Format>Set sliding&Printing.
Now, when you enter an award, you'll choose a recipient by name, a donor by name, and all the rest of the fields from the other tables should populate for themselves. And when you print, the linking fields won't print and clutter up the pretty certificate.
Take your time, walk it through step by step. It's confusing the first time, but when you've got it...it will seem simple.
Let us know how you do!
You are a genius!! Thank you so very much for all of your help. I truly appreciate your time and patience with me :smileyhappy: