User Interface to select Individual or Company Donors

[croberts]

Hello,
I'm working on a church donations database. A donor can be an
individual or a business. tblIndividual and tblBusiness reside in an
external membership database. I've created tblDonor with DonorID pk,
DonorTypeID (1=individual, 3=business), PersonLinkID (fk to
tblIndividual) and CompanyLinkID (fk to tblBusiness). Records are
continually added to the tables in the membership database that may or
may not ever be donors, so tblDonor only contains records for those
who are actually donors. I want to store the DonorID in the db with
each contribution. When the bookkeeper enters a contribution, I'm
trying to make it as simple as possible to select or add the donor.
What I'm thinking is that she begins typing the donor name in the
combo, checking displayed address where there are multiples of the
same name. If this is a repeat donor, the Donor ID is stored in the
field and she moves on. If this is a new donor but they are recorded
in the membership database, selecting the name appends a row in
tblDonor with the DonorType-determined by the table the name comes
from, and the foreign key in the appropriate field, and the form is
updated with the new DonorID. If this is a new donor with no record in
the membership db, a form opens to enter all the applicable info then
she is returned to the main form with the new DonorID populated. But I
seem to be spinning trying to get this to work and thinking I'm making
it more difficult than it needs to be?

Since existing member-non donors don't have a DonorID, I used an
unbound combo to display the results of the union query. DonorID,
DonorTypeID and pk for each individual or business are hidden columns.
If the donor ID is empty, the code appends tblDonor with data from the
hidden columns. But then I need to display the name in a bound field.
I've tried stacking the bound and unbound fields and toggling
visibility and setfocus, but keep getting 'Access can't set focus to
that field' [Access, you can't set focus and you can't tell me why??!!-
oh, pardon my grumbling ...] Then I'll need to use NotInList to trap
the entries who are new to donors and to membership-but I can't use
NotInList on an Unbound combo, can I?.

At this point I've very much confused myself and would so much
appreciate anyone who could help me sort this out.

 

[Allen Browne]

The ideal solution would be to put everyone in the one Donor table --
individuals and businesses. This gives you a simple DonorID as primary key,
which can then be a foreign key to your table of donations. The donor table
can still have a DonorTypeID field to indicate which type it is.

If that's not possible, you have several issues to address in addition to
the ones you mentioned. If tblIndividual and tblBusiness reside in different
database files to your donor table, you cannot enforce referential
integrity. Therefore it is possible that your donor table will have issues
such as:
- a DonorID that has been deleted from the other database files;
- a different donor could then be assigned that unused DonorID in the other
databases.

If the idea of putting persons and corporate entities all in the one table
is new, here's a bit more about that:
People in households and companies - modelling human relationships
at:
http://allenbrowne.com/AppHuman.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello,
I'm working on a church donations database. A donor can be an
individual or a business. tblIndividual and tblBusiness reside in an
external membership database. I've created tblDonor with DonorID pk,
DonorTypeID (1=individual, 3=business), PersonLinkID (fk to
tblIndividual) and CompanyLinkID (fk to tblBusiness). Records are
continually added to the tables in the membership database that may or
may not ever be donors, so tblDonor only contains records for those
who are actually donors. I want to store the DonorID in the db with
each contribution. When the bookkeeper enters a contribution, I'm
trying to make it as simple as possible to select or add the donor.
What I'm thinking is that she begins typing the donor name in the
combo, checking displayed address where there are multiples of the
same name. If this is a repeat donor, the Donor ID is stored in the
field and she moves on. If this is a new donor but they are recorded
in the membership database, selecting the name appends a row in
tblDonor with the DonorType-determined by the table the name comes
from, and the foreign key in the appropriate field, and the form is
updated with the new DonorID. If this is a new donor with no record in
the membership db, a form opens to enter all the applicable info then
she is returned to the main form with the new DonorID populated. But I
seem to be spinning trying to get this to work and thinking I'm making
it more difficult than it needs to be?

Since existing member-non donors don't have a DonorID, I used an
unbound combo to display the results of the union query. DonorID,
DonorTypeID and pk for each individual or business are hidden columns.
If the donor ID is empty, the code appends tblDonor with data from the
hidden columns. But then I need to display the name in a bound field.
I've tried stacking the bound and unbound fields and toggling
visibility and setfocus, but keep getting 'Access can't set focus to
that field' [Access, you can't set focus and you can't tell me why??!!-
oh, pardon my grumbling ...] Then I'll need to use NotInList to trap
the entries who are new to donors and to membership-but I can't use
NotInList on an Unbound combo, can I?.

At this point I've very much confused myself and would so much
appreciate anyone who could help me sort this out.

 

[croberts]

Thank you, Allen, I really appreciate your response. I had read your
article on modeling human relationships and did consider merging the
individual and business tables into one as a result. I'm not convinced
that approach would fit this scenario because there is much
information tracked about a person that is not applicable to a
business (DOB, Marital status, membership status, etc.) But even if it
does, it would have to be a gradual shift (adding the IsCorporate
field and working through all of the forms, reports and queries to
filter on that field before merging the tables) and their priority is
getting this accounting database finished in time to do the 2010
budget. I've inherited these databases and they are a mess! The
current accounting database has a button the bookkeeper manually
clicks that runs an action query to match first and last names in the
accounting database against names in tblIndividual and imports any
that are missing!!! and she also manually enters people in the
accounting db as well as businesses because there is no link to them.
Just the inconsistencies of Jim Smith in one db and James Smith in
another are a nightmare. So in a sense, I need to bandaid this until I
have a chance to clean it up right. (As an example, last week I moved
the work phone and cell phone fields from tblIndividual into a linked
tblPhone, missed updating the query for 1 form (which happened to be
the first one the secretary tried and decided the database didn't work
at all because it displayed the error that it couldn't find "Work
NO" (yes, spaces in field names) even though the form did then display
with the tblPhone subform...

So, I need some method of allowing the bookkeeper to add individual or
business donors that should link to the information to tblIndividual
or tblBusiness if they exist or create a new record if they don't.
(There are apparently many who donate but never fill out info for the
membership db.) I don't want her to have to go through 2 lookups,
first to see if they already have a donor record, then to see if they
have a member record. Do you have any thought how I might implement
this?

Thank you so much,
Cathy Roberts

The ideal solution would be to put everyone in the one Donor table --  
individuals and businesses. This gives you a simple DonorID as primary key,
which can then be a foreign key to your table of donations. The donor table
can still have a DonorTypeID field to indicate which type it is.

If that's not possible, you have several issues to address in addition to
the ones you mentioned. If tblIndividual and tblBusiness reside in different
database files to your donor table, you cannot enforce referential
integrity. Therefore it is possible that your donor table will have issues
such as:
- a DonorID that has been deleted from the other database files;
- a different donor could then be assigned that unused DonorID in the other
databases.

If the idea of putting persons and corporate entities all in the one table
is new, here's a bit more about that:
    People in households and companies - modelling human relationships
at:
   http://allenbrowne.com/AppHuman.html
--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hello,
I'm working on a church donations database. A donor can be an
individual or a business. tblIndividual and tblBusiness reside in an
external membership database. I've created tblDonor with DonorID pk,
DonorTypeID (1=individual, 3=business), PersonLinkID (fk to
tblIndividual) and CompanyLinkID (fk to tblBusiness). Records are
continually added to the tables in the membership database that may or
may not ever be donors, so tblDonor only contains records for those
who are actually donors. I want to store the DonorID in the db with
each contribution. When the bookkeeper enters a contribution, I'm
trying to make it as simple as possible to select or add the donor.
What I'm thinking is that she begins typing the donor name in the
combo, checking displayed address where there are multiples of the
same name. If this is a repeat donor, the Donor ID is stored in the
field and she moves on. If this is a new donor but they are recorded
in the membership database, selecting the name appends a row in
tblDonor with the DonorType-determined by the table the name comes
from, and the foreign key in the appropriate field, and the form is
updated with the new DonorID. If this is a new donor with no record in
the membership db, a form opens to enter all the applicable info then
she is returned to the main form with the new DonorID populated. But I
seem to be spinning trying to get this to work and thinking I'm making
it more difficult than it needs to be?

Since existing member-non donors don't have a DonorID, I used an
unbound combo to display the results of the union query. DonorID,
DonorTypeID and pk for each individual or business are hidden columns.
If the donor ID is empty, the code appends tblDonor with data from the
hidden columns. But then I need to display the name in a bound field.
I've tried stacking the bound and unbound fields and toggling
visibility and setfocus, but keep getting 'Access can't set focus to
that field' [Access, you can't set focus and you can't tell me why??!!-
oh, pardon my grumbling ...] Then I'll need to use NotInList to trap
the entries who are new to donors and to membership-but I can't use
NotInList on an Unbound combo, can I?.

At this point I've very much confused myself and would so much
appreciate anyone who could help me sort this out.- Hide quoted text -

- Show quoted text -

 

[Allen Browne]

My gut feeling is that it will take a great deal more work in the long term
to create something that does only part of the job now, go through the
frustrations of trying to make that work, and then create it properly for
the long term. Personally, I could not be bothered to go to that effort of
doing it twice.

But, if you want to do it that way anyway your form for entering donations
would have some unbound controls:
- cboDonorTypeID (1 = indiv; 3 = business)
- txtDonorName unbound text box (or perhaps 2 for individuals)
- lstDonorMatch unbound list box to display matches.
In the AfterUpdate event of the first 2, you run code to set the RowSource
of the list box so it displays donors of the right type, with the matching
name.

Your logic now branches 3 ways:
a) DonorID exists
Assign it as the DonorID for the current (new) record in your donation form.

b) Person/business found, but no donorid:
OpenRecordset() on tblDonor, add the DonorTypeID and either PersonLinkID or
CompanyLinkID, get the new DonorID, and assign it to the current (new)
record in your donation form.)

c) No match at all:
Add the new person/business and get the new id value.
Add the new donor to tblDonor
Assign the new DonorID to the form.

Note that the list box's RowSource will be a SQL statement that uses an
outer join between tblIndividual or tblBusiness and tblDonor. This will give
you a null DonorID if the person/company is not already a donor.

This still won't catch issues like your James Smith versus Jim Smith or Mr J
Smith. You could add more complexity to parse the names and define synomyms
or use fuzzy algorithms like Soundex().

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you, Allen, I really appreciate your response. I had read your
article on modeling human relationships and did consider merging the
individual and business tables into one as a result. I'm not convinced
that approach would fit this scenario because there is much
information tracked about a person that is not applicable to a
business (DOB, Marital status, membership status, etc.) But even if it
does, it would have to be a gradual shift (adding the IsCorporate
field and working through all of the forms, reports and queries to
filter on that field before merging the tables) and their priority is
getting this accounting database finished in time to do the 2010
budget. I've inherited these databases and they are a mess! The
current accounting database has a button the bookkeeper manually
clicks that runs an action query to match first and last names in the
accounting database against names in tblIndividual and imports any
that are missing!!! and she also manually enters people in the
accounting db as well as businesses because there is no link to them.
Just the inconsistencies of Jim Smith in one db and James Smith in
another are a nightmare. So in a sense, I need to bandaid this until I
have a chance to clean it up right. (As an example, last week I moved
the work phone and cell phone fields from tblIndividual into a linked
tblPhone, missed updating the query for 1 form (which happened to be
the first one the secretary tried and decided the database didn't work
at all because it displayed the error that it couldn't find "Work
NO" (yes, spaces in field names) even though the form did then display
with the tblPhone subform...

So, I need some method of allowing the bookkeeper to add individual or
business donors that should link to the information to tblIndividual
or tblBusiness if they exist or create a new record if they don't.
(There are apparently many who donate but never fill out info for the
membership db.) I don't want her to have to go through 2 lookups,
first to see if they already have a donor record, then to see if they
have a member record. Do you have any thought how I might implement
this?

Thank you so much,
Cathy Roberts

The ideal solution would be to put everyone in the one Donor table --
individuals and businesses. This gives you a simple DonorID as primary
key,
which can then be a foreign key to your table of donations. The donor
table
can still have a DonorTypeID field to indicate which type it is.

If that's not possible, you have several issues to address in addition to
the ones you mentioned. If tblIndividual and tblBusiness reside in
different
database files to your donor table, you cannot enforce referential
integrity. Therefore it is possible that your donor table will have
issues
such as:
- a DonorID that has been deleted from the other database files;
- a different donor could then be assigned that unused DonorID in the
other
databases.

If the idea of putting persons and corporate entities all in the one
table
is new, here's a bit more about that:
People in households and companies - modelling human relationships
at:
http://allenbrowne.com/AppHuman.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hello,
I'm working on a church donations database. A donor can be an
individual or a business. tblIndividual and tblBusiness reside in an
external membership database. I've created tblDonor with DonorID pk,
DonorTypeID (1=individual, 3=business), PersonLinkID (fk to
tblIndividual) and CompanyLinkID (fk to tblBusiness). Records are
continually added to the tables in the membership database that may or
may not ever be donors, so tblDonor only contains records for those
who are actually donors. I want to store the DonorID in the db with
each contribution. When the bookkeeper enters a contribution, I'm
trying to make it as simple as possible to select or add the donor.
What I'm thinking is that she begins typing the donor name in the
combo, checking displayed address where there are multiples of the
same name. If this is a repeat donor, the Donor ID is stored in the
field and she moves on. If this is a new donor but they are recorded
in the membership database, selecting the name appends a row in
tblDonor with the DonorType-determined by the table the name comes
from, and the foreign key in the appropriate field, and the form is
updated with the new DonorID. If this is a new donor with no record in
the membership db, a form opens to enter all the applicable info then
she is returned to the main form with the new DonorID populated. But I
seem to be spinning trying to get this to work and thinking I'm making
it more difficult than it needs to be?

Since existing member-non donors don't have a DonorID, I used an
unbound combo to display the results of the union query. DonorID,
DonorTypeID and pk for each individual or business are hidden columns.
If the donor ID is empty, the code appends tblDonor with data from the
hidden columns. But then I need to display the name in a bound field.
I've tried stacking the bound and unbound fields and toggling
visibility and setfocus, but keep getting 'Access can't set focus to
that field' [Access, you can't set focus and you can't tell me why??!!-
oh, pardon my grumbling ...] Then I'll need to use NotInList to trap
the entries who are new to donors and to membership-but I can't use
NotInList on an Unbound combo, can I?.

At this point I've very much confused myself and would so much
appreciate anyone who could help me sort this out.- Hide quoted text -