Suggestion for Structural Approach?

[CS]

I am designing a db for a small non-profit, and am fairly new to Access.
The NP receivea donations in three ways 1) at events, 2) through members
(not at events), and 3) through miscellaneous donation from non-members (not
at events). Currently, the structure is:

ContactTB - ContactID, and blah,blah,blah about each
EventsTB - EventID, and blah, blah, blah about each
ParticipationTB -
Participant ID, ContactID, Event ID, and DonationAmnt made at each
participation and blah . . .
MemberTB - MemberID, ContactID, and blah,blah about each
MemberDonationsTB - MemberID, DonationDate, DonationAmnt made outside of
events

I have an EventsForm, with a continuous subform to enter all the
Participants for each event and their donations at that event. (More about
why I stuck their donations in the ParticipantT in a moment), and a
MemberForm that does the same with a subform from MemberDonations.

Now I realize I have these miscellaneous donations and need another table
for those. I realize that I could query from the three donation sources to
create reports (for example, for a Contacts year-end report of donations),
but am wondering if that's a good approach or a poor one. I have considered
a general DonationsTB, with items from Participation, MemberDon, and MiscDon
posting to these from various forms, but here's my problem -- (and why I put
the event donations in ParticipantT) -- the Participants subform (on main
EventF) cannot have a subform and stay continuous, but I have been racking
my brains how to place a Donations subform on the main EventF that was
continuous AND remained reliably linked to the Participant subform.

Am considering something like:
DonationTB - DonationID, ContactID, DonationSource (could be event, member
or misc), Donation Amnt
I would then remove donation info from the others tables

I suspect that putting all the Donations to a single table will serve me
better structurally than querying from three, but want to know if my logic
in that is correct, and if so, how to make sure that two subforms on a main
form remain true to one another, faithfully posting Participant #1s donation
to the correct ContactID to the DonationsAmnt field.

Thanks in advance for any help. Apologies offered if this is truly stupid,
obvious, or posted a million times and I just didn't find it.
Carol

I need the end user to easily record the details of the event, include ALL
the participants who attended, and include any donations they made at these
events.

 

[Larry Daugherty]

Your assumptions about a separate table for Donations being
structurally more correct is, indeed, correct.

If your schema is sound then the tables on which your subforms are
based probably aren't related to each other. So long as they are each
properly related to the upper table then all is well.

As you have probably inferred, identifying and defining the entities
involved whether they be tangible things or concepts is the first
major step in analyzing your application. Those are the main tables.

If you're an Access newbie I'd say you are an advanced Access newbie.

HTH

 

[CS]

Thanks much, Larry, for responding.

Perhaps you can help me with my dillema a bit further ( it is not so
much a problem with my understanding of how to store, query, and report
the data, but of my end user having the right info in front of them so
that they can update the various tables correctly and easily, and
building the forms for that). I will have many monkeys entering data,
and want to make this as easy for them as possible.

I want the end user to be able to enter the Event Info on the main
form, _then_ enter the participants registered, attending, etc. in the
Participation sub-form, AND enter the donation that this _specific_
participant made -- in another sub-form that posts to the DonationsTB,
if possible. The participants subform has a combo box that populates
the ContactID foreign key in ParticipantsTB -- I don't want the user to
have to select this again in the Donation subform -- I would like the
Donation subform to link up with the already selected ContactID on the
Participants sub so that the user doesn't end up selecting this twice.
Any hints?

Thanks for the compliment, -- but if you think I'm an advanced newbie,
watch the smoke curl out of my ears when I even _contemplate_ the term
"BeforeUpdate" (let's just say you might want to get a wet cloth to
cover your mouth and nose). And guess what -- for me, validation is
the next step. Arggh!
Thanks again,
Carol

 

[Pat Hartman]

I would use a donation table to record all donations.
tblDonation:
DonationID (autonumber primary key)
ContactID (foreign key to tblContact)
EventID (foreign key to tblEvent) - optional since not all donations are
made at events.
DonationAmt
etc.