Search 2 tables for same criteria

[Tal]

Hello,

So, I am building a database for a charity with some odd and specific needs.
The one that I can't figure out at the moment is:

Donations are made by the 1st party (Donor) and acknowledgment is sent to a
second party (Recipients) but often the Recipient is also an existing donor.

So, I have 3 tables Donors and Recipients and Donations
I would like a field in the donations form that will search the Donors table
and Recipient table for the same information, ie. Last Name and then pull up
the relevant address information.
To further complicate matters Donors have a One to Many relationship with
their addresses.

If I could just get started with how to make a textbox that will search the
2 tables that would be great.

Many thanks,

Tal

 

[Allen Browne]

Combine the Donors and Recipients into one table of Clients.

There is no point having 2 tables with effectively the same fields in each
of them. There is even less point in having to enter someone into both
tables (duplicate data), and then having to maintain both copies in sync.

So, your Donation table with have fields such as:
DonationID primary key
DonationDate date/time
DonorID foreign key to Client table
RecipientID foreign key to Client table

Since you need to create 2 relationships from Donation to Client, add the
Client table to the Relationships window a second time. Access will alias
the 2nd one as Client_1. You can now create a relation from
Client_1.ClientID to Donation.RecipientID (in addition to the relation from
Client.ClientID to Donation.DonorID.)