Multiple Last Names

I'm bulding a database to track donations for a non-profit organization.
Many of the donors have two last names (ie. husband Smith, wife Jones). I've
built the table using fields: 1st First, 1st Last, 2nd First, and 2nd Last,
however when I attempted to do a report (labels), I had to do two separate
querys: 1. to get all donors with one last name; and, 2. to get all donors
with two last names.

Any suggestions? I feel like this is going to be an issue

Normalize your data structure. Something like:

tblDonor
DonorKey
DonorSinceDate
OtherDonorSpecificInformation

tblPerson
PersonKey
DonorKey -- links back to tblDonor
SurName
GivenName
BirthDate
Gender
OtherPersonSpecificInformation


"bcg" wrote in message
...
I'm bulding a database to track donations for a non-profit organization.
Many of the donors have two last names (ie. husband Smith, wife Jones).
I've
built the table using fields: 1st First, 1st Last, 2nd First, and 2nd
Last,
however when I attempted to do a report (labels), I had to do two separate
querys: 1. to get all donors with one last name; and, 2. to get all donors
with two last names.

Any suggestions? I feel like this is going to be an issue

On 8 Aug, 19:22, "B. Edwards" wrote:
Normalize your data structure. Something like:

tblDonor
DonorKey
DonorSinceDate
OtherDonorSpecificInformation

tblPerson
PersonKey
DonorKey -- links back to tblDonor
SurName
GivenName
BirthDate
Gender
OtherPersonSpecificInformation

Normalize your data structure. A table either models an entity type or
a relationship between entities but not both. DonorKey is not an
attribute of a person.

At its simplest this could involve an entity table for donors and a
relationship table, say, donor accounts to model associations between
donors. I think there may be more entity types, though e.g.

Persons (identifier, person-specific attributes);
Donors (identifier from the Persons table, donor-specific attributes);
DonorAccounts (identifier, account-specific attributes);
DonorAccountMembership (identifier from the DonorAccounts table,
identifier from the Donors table, account membership -specific
attributes);

What about corporate donors? CRM (prospects, suspects, lapses, etc)?
Temporal? As ever, the aim is to make things as simple as possible but
no simpler.

Jamie.

--

Jamie,
Can you describe to me the difference between the DonorAdcount an
DonorAccountMembership tables, please? I don't really understand the
difference.
Thanks.
--
Dave Hargis, Microsoft Access MVP


"Jamie Collins" wrote:

On 8 Aug, 19:22, "B. Edwards" wrote:
Normalize your data structure. Something like:

tblDonor
DonorKey
DonorSinceDate
OtherDonorSpecificInformation

tblPerson
PersonKey
DonorKey -- links back to tblDonor
SurName
GivenName
BirthDate
Gender
OtherPersonSpecificInformation

Normalize your data structure. A table either models an entity type or
a relationship between entities but not both. DonorKey is not an
attribute of a person.

At its simplest this could involve an entity table for donors and a
relationship table, say, donor accounts to model associations between
donors. I think there may be more entity types, though e.g.

Persons (identifier, person-specific attributes);
Donors (identifier from the Persons table, donor-specific attributes);
DonorAccounts (identifier, account-specific attributes);
DonorAccountMembership (identifier from the DonorAccounts table,
identifier from the Donors table, account membership -specific
attributes);

What about corporate donors? CRM (prospects, suspects, lapses, etc)?
Temporal? As ever, the aim is to make things as simple as possible but
no simpler.

Jamie.

--

On 9 Aug, 16:02, Klatuu wrote:
Jamie,
Can you describe to me the difference between the DonorAdcount an
DonorAccountMembership tables, please? I don't really understand the
difference.
Thanks.

The 'account' is the name I'm using to describe the association of one
or more (or perhaps zero) donors and the DonorAccountMembership is the
donors who are currently (or perhaps have been) members of that
account. I wouldn't assume that an account ceases to exist because it
currently has no members (normalization issue called 'update anomaly'
I think) but note this isn't my domain.

Jamie.

--

Thanks, I think I understand it this way.
An Account is a fund set up by the agency for specific purposes (normal in a
NFP environment).

Account Membership, then, is a list of donors which have subscribed to
contribute to the fund whether the have in the past or future or not.

Is that correct?
--
Dave Hargis, Microsoft Access MVP


"Jamie Collins" wrote:

On 9 Aug, 16:02, Klatuu wrote:
Jamie,
Can you describe to me the difference between the DonorAdcount an
DonorAccountMembership tables, please? I don't really understand the
difference.
Thanks.

The 'account' is the name I'm using to describe the association of one
or more (or perhaps zero) donors and the DonorAccountMembership is the
donors who are currently (or perhaps have been) members of that
account. I wouldn't assume that an account ceases to exist because it
currently has no members (normalization issue called 'update anomaly'
I think) but note this isn't my domain.

Jamie.

--

"Klatuu" wrote:

Thanks, I think I understand it this way.
An Account is a fund set up by the agency for specific purposes (normal in a
NFP environment).

Account Membership, then, is a list of donors which have subscribed to
contribute to the fund whether the have in the past or future or not.

Is that correct?

As I said, I have no knowledge in this domain and don't know the OP's
business requirements to set a reasonable scope on the entities (it's really
easy to blow entity types out of all proportion to end up with 30 tables when
three would do g).

Using a domain I do know, a one or more legal persons (a natural person or
an organisation) can be the owner one or more shares issued by an
incorporated body; this relationship is an entity in its own right and has
attributes (e.g. beneficial owner). AFAIK there is no official term to
describe the relationship between the owners (if anyone reading this knows of
one I'd love to learn it) so I picked the word 'account' out of thin air.

Jamie.

--