Microsoft Access Child Record with Multiple Parents: Getting information on corresponding parents.

Question

Hi all,

I am currently working on an Access Database for tracking some donation information.

To give you a gist of how I have set it up, I have a parent table for Donors who make donations that are tracked in a child table called Donations. Each donation may be associated with a campaign or an event, each of which has separate tables. Thus, each donation record serves as a child for donors, events and campaigns.

While tracking a donor's attendance at an event can be done manually via a Donor-Event junction table, I am interested in two things.

1: I would like to display a list of all the donors that have made donations associated with that event. I can get the donations associated with an event but am unsure how to map those event donations to extract matching donor information from the donor table.

2: I am interested in populating the Event-Donor Junction with the donors that have made donations (as they are bound to have attended the event) and thus allowing the end-user to add in additional attendees as they see fit (basically donors that attended but did not end up making a donation).

Looking forward to any help/suggestions from your end.

Answer 1

, only the donor name is recorded in donations (a foreign key from the DONORS table in the Donations table).

First names are not good for a key field as you can have duplicate names. Your tablers should look like this:

tblDonors

DonorID PK Autonumber

DonorFirstname

DonorLastname

other into about donors

tblCampaignsandEvents

CampEventID (PK Autonumber)

CampEventTypeID (FK) (EitherEvent or Campaign etc)

CampEventTitle

other info (You might need separate tables for info unique to campaigns or events.)

tblDonations

DonationID (PK Autonumber)

DonorID (FK)

CampEventID (FK)

DonateDate

Donation

other info

to get a listing of donors for a specific campaign or event, you  join all three tables And list donor info for a specific CampEventID

Answer 2

In a query join the donations table to donors using the foreign key to donor primary key will pull donations with the donors information.  You can include event criteria if you want.

Answer 3

I do have a foreign key field in the donation table. My question is how to use that foreign key to generate a list of donors. I am currently able to create a list of donations for that event but would like to get additional information for each specific donor (which is available in the DONORS table), rather than their donations. Currently, only the donor name is recorded in donations (a foreign key from the DONORS table in the Donations table).

Answer 4

I would not have a Donor-Event table but have a foreign key field in the Donation table for Event.

Answer 5

Hi Dianna,

As you question is related to further usage of Access database. We will move this thread to Office>Access>Microsoft Office Programming>Office 2016 category for giving you professional support. This is the specific channel which handles this kind of questions. Meanwhile, if it’s convenient, you may share a sample of your database in this thread. So, other community members can join the discussion and give their suggestions.

Thanks for your understanding.

Tim