Name order streamlining

[Stephanie]

Tom,
Thanks for the reply. This application is for a non-profit organization for
which I'm on the board. Obviously, Access is not something I do particularly
well but we are trying to get this new db in place (it's still very much a
work in progress). No promotion, no raise, no profit involved.

I sense that we are still having a miscommunication. PrimaryFamilyMember is
actually a new field that I have tried to put in place solely to add members
"bucks" together. It isn't used for any other reason.

SignificantOtherID was designed specifically to indicate if a member was
married (or the significant other) of another member. All of the reports
that use SignificantOtherID, simply link the two members together. If a
member does not have a significant other that is a member, SignificantOtherID
is null. I'm not sure how this would lead to corrupted data, but I'd
appreciate your help.

The only issue I have is adding bucks together. In order to do that, I feel
that I need to have the "couple" listed in a consistent order. I haven't
been able to do that with SignificantOtherID. That's really all I'm trying
to do and haven't yet resolved. Thanks.

Dear Stephanie:

When you have two columns in one table that absolutely MUST agree with one
another you need a solution that easily prevents them from disagreeing, or
you need to unify them in one column. While I do not know what your
reasoning is for never having SignificantOtherID = ContactID, there is then
only one alternative I can see, and that is to have SignificantOtherID be
NULL. Either approach is acceptable, as you can enforce referential
integrity as needed, and both can be tested. So, if you choose the NULL
approach, as I think you are saying, you have my agreement and approval.
However, my position is that there is no difference. Your reason for not
wanting SignificantOtherID = ContactID is, if it is a technically valid
problem, would then be an objection to having it be NULL.

It's probably my math background speaking here. In set theory, this works
the same way. We had nulls, and we had self-references. Either can be
defined to be the rule. The only thing important is to pick one way or the
other and stick with it, enforce it.

Keeping PrimaryFamilyMember is an important requirement for any legacy
portions of your system. Making it invisible, setting it according to the
current state of SignificantOtherID, and making sure it is set appropriately
any time the row is added or updated will be a perfectly functional change
to make. Whether you eventually remove it or not is probably optional. But
if you continue to have it and maintain it, there's some chance it will
eventually be corrupted.

In my experience, there is sometimes a need to access the data to fix things
through the table access, or through scratch queries. Anyone, perhaps not
you, who must do so in future months or years, and may not be aware there
are two columns in the table that MUST agree in this way is a pitfall to
them. If they screw up the database by setting a few PrimaryFamilyMember
values, or manually adding some new ones, then a big problem can ensue. So,
I find this to be a less than "clean" alternative, but a very appropriate
intermediate step. Keeping it around temporarily until you have found and
changed existing software to use SignificantOtherID instead of
PrimaryFamilyMember is part of a contiguous and very tidy development
process on a database that is in use.

I intended to suggest you use some query work I believe I already posted, at
least in fragmental form, to immediately determine where the data is
currently "corrupted" in this way. This would allow users to find and
repair these till there are none any more. It would also train users not to
misuse the software while it still shows the PrimaryFamilyMember check box.
You cannot make this invisible until all such errors are removed. The
upgrade to set it automatically and then making it invisible could then be
taken. Putting a tempory test in the form that warns when it is wrong is
good, too. I don't know if it will be practical as the ONLY way to see if
there are any corruptions remaining. Will someone be scrolling through
every member in the form, with a test for the corruption? When you want to
know if it is time to install the new form, with PrimaryFamilyMember
disappearing, will you also step through every member? Or, would you rather
run a query that quickly and automatically finds and displays them? I
suppose if the table is small, the former is fine. For me, the query is a 3
minute job to create, and answers the question in a second. I wouldn't be
without that, and would consider the test in the form. It's a lot more
programming time, and may or may not be as effective.

I've enjoyed working on this with you (if you'll allow me to include myself
in the "credits" for your project as "gaffer"). I sense we're close to a
conclusion, and that's satisfying as well. I'd still like to hear at least
one time how it has worked out. Did you get a raise and/or promotion?

Tom Ellison

Tom,
Thanks for the reply. I had John Doe listed twice because I had 2 people
tied to him through SignificantOtherID just to see what happened.
Unfortunatley, I did have Scott Doe (John's son) also listed as the
PrimaryFamilyMember so I can see the data integrity issues.

However, I absolutely do not want a contact to be their own significant
other. ContactID=SignificantOtherID is not acceptable.

To me the best method seems to be leaving both SignificantOtherID and
PrimaryFamilyMember intact. I'd like to have PrimaryFamilyMember default
as
"yes" but be invisible. When SignificantOtherID is populated, have
PrimaryFamilyMember become visible with a message box asking if this
individual will be the primary family member, and if not to uncheck the
box
so that there is only one primary in the family- maybe a reminder msg box
indicating that there should only be one primary in a family membership.
Could be user error, but there you have it. There is going to have to be
some manual intervention. Is this acceptable? Can it be done? Could you
please help me with the code? Thanks.

Dear Stephanie:

I feel your pain.

Any time your database has 2 ways to represent the same thing, there is
the
danger that the data will have rows that represent that same thing in
contradictory ways. It is my interpretation of a certain rule of
database
design that this must be avoided. Early in this thread I tried to point
this out when I was recommending you drop the Boolean (yes/no)
PrimaryFamilyNumber. I now recommend you search to find out how
prevalent
this problem is. The problem occurs whenever PrimaryFamilyMember is
False
and the SignificantOtherID = ContactID, or when PrimaryFamilyMember is
True
and SignificantOtherID <> ContactID. Do you agree with that?

Assuming you do, you should be able to derive the value now in
PrimaryFamilyMember by testing whether SignificantOtherID = ContactID.
Do
you see now that this theme has permiated what I wrote you previously?
And
do you see that this is at the core of your problem? I think you do, and
the light has just come on. Good!

Obviously, then, this is why my solution has not been working for you.
These are inconsistencies in the data that I expected all along may cause
just this problem!

I don't intend to "rub it in, now" but I am feeling a bit vindicated in
my
opinions expressed earlier. Mostly, I'm just glad the light has come on
for
you! However, with the data in its current state, neither of us can
proceed
to perform what you originally requested, which was to sort everything so
John and Mary are sorted adjacent, with John first. I'm just trying here
to
keep the target in mind.

You should not eliminate the PrimaryFamilyMember at this time, not until
you
have made SignivicantOtherID to be consistent with it. I believe it is
likely that in most, if not all, cases where the inconsistency exists
PrimaryFamilyMember has been set correctly and SignificantOtherID needs
to
be changed. Do you agree?

I do not know any better way to work this out than to prepare the query I
gave above to show all these inconsistencies so someone can go to work on
them to eliminate the problem, in most cases by setting the correct
SignificantOtherID. Once this is repaired, you can quit using
PrimaryFamilyMember in all new work, and you can remove it from your
form.
You do not have to remove it from your table, indeed, you should not do
this
until you have removed any existing code that still uses
PrimaryFamilyMember. Doing so would break that code.

The other thing needed would be to modify your form(s) that allow adding
new
members, or changing SignificantOtherID, so that the value of
PrimaryFamilyMember is set correctly and automatically whenever such
changes
are made. I believe the BeforeUpdate event is adequate for this.

Why John shows up twice is the next problem to address. I'm thinking
this
is a JOIN problem in there somewhere. That means finding in which of the
related tables are there 2 rows matching the relationship specified in
the
JOIN . . . ON portions of your query. My first guess is DuesLineItem.
Do
you have two of these for John's ContactID. If so, do you want to show
both
of them, only one, or aggregate them? These are the only simple choices.
The answer will depend on the context of the uses you intend for this
query.
So, that will be up to you.

I hope I'm not suddenly seeming to be overbearing. Actually I'm quite
pleased that you've discovered what I have been trying to say for a
while.
The problem is one I expected from early on. My poor powers of
persuasion
and explanation just couldn't convey what I needed to say, but you found
it
out for yourself in spite of this. Good Job! I'm happy for you.

Well, the pressure to answer my questions is only in that they are a
prerequisite to having my help, if help it is. If it is helping, I'm
satisfied that you think so. You also have the option to start a new
thread
and ask your questions again, asking me to butt out. Others here are
quite
capable of steering you through this maze. I would never bear a grudge
if
you did so. Anyway, I hope the pressure is the result of your desire to
obtain my assistance, and that's the natural price for it. Unless I'm
informed, I can't really help. I ask the questions I need to ask in
order
to try to be of assistance. You answer them only if you are finding that
assistance worth your time, effort, and frustration. So, no hard
feelings
either way!

Or, I ask questions to try to get you to look in the right place to find
the
source of your difficulty. That was probably my motivation in this case.
Looking at the questions in my previous post, were they not exactly the
ones
needed to guide you to the problem you have now discovered? Were they
not
what led you to your epiphany? I'm hoping so, that I may have played a
small part in guiding you to where you are now, which is exactly where I
wanted you to be, given the assumptions I have had all along.

Tom Ellison


Tom,
The pressure to answer your questions correctly! And execellent
question
it
is, because I think I see the problem.
Susie's BaseName is Susie Anderson, but her SignificantOtherID points
to
herself and not John Doe.
John's BaseName is John Doe, and his SignificantOtherID points to
himself
and not Susie Anderson.
And strangley, John Doe shows up in the query results twice- each time
pointing to himself as the SignficantOtherID.

I know that this query below uses PrimaryFamilyMember which we are
trying
to
do away with in favor of SignificantOtherID, but it does bring back the
correct BaseName, ContactID and SignificantOtherID (if that helps):
SELECT Nz(c1.NickName,c1.FirstName) & " " & c1.LastName AS [Member
Name],
IIf(PrimaryFamilyMember,[Member Name],(SELECT Nz(c2.[NickName],
c2.[FirstName]) & " " & c2.[LastName] FROM Contacts c2 WHERE
c2.ContactID =
c1.SignificantOtherID)) AS [Base Name], C1.FirstName, C1.LastName,
C1.ContactID, C1.SignificantOtherID
FROM (PaymentTypes INNER JOIN (DuesItemType INNER JOIN (Contacts AS C1
INNER
JOIN (DuesLineItem INNER JOIN tblDuesRates ON DuesLineItem.DuesRateID =
tblDuesRates.DuesRateID) ON C1.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) ON
PaymentTypes.TypeID = DuesLineItem.TypeID) INNER JOIN MemberCategory ON
C1.MemberCategoryID = MemberCategory.MemberCategoryID;

Couldn't I just make PrimaryFamilyMember default to "yes" and be
invisible
unless SignificantOtherID is not null, and then show
PrimaryFamilyMember
so
the user could uncheck as appropriate? Thanks for your help!

:

Dear Stephanie:

So, what did Susie's BaseName turn out to be? Is her
SignificantOtherID
NULL? Is it set to the ContactID of John?

Please carefully confirm your facts. I need to know whether there is
a
problem in the code, and what that might be, or if the data is not as
expected. I could probably offer some adjustment with this knowledge,
but I
don't have the database in front of me, as you do. I must rely on you
to
thoroughly and carefully investigate the cause of your difficulty.
All I
can do is give hints!

Tom Ellison


Tom,
I apologize for the delayed response and appreciate you hanging in
there!

I have this query that I've called DuesByMemberBase:
SELECT IIf(SignificantOtherID Is Null,c1.[LastName] & ", " &
Nz(c1.NickName,c1.FirstName),(SELECT c2.LastName & ", " &
Nz(c2.NickName,
c2.FirstName)
FROM Contacts c2 WHERE c2.ContactID = c1.SignificantOtherID)) AS
[BaseName]
FROM Contacts AS c1;

This does bring back a Base Name for each person. However, I
expected
Susie
Anderson's BaseName to be John Doe, because I want to be able to add

 

[Tom Ellison]

Dear Stephanie:

We're after the record for longest thread. Fine with me!

Best I can tell, I have been responding to your original request
consistently. At the same time, you continue to remain on that same track
as well. That would seem to be pretty much the definition of
miscommunication.

The only way you can properly associate together the "family" members is by
SignificantOtherID. I have tried to illustrate how PrimaryFamilyMember does
not aid in doing this. If PrimaryFamilyMember is set to false, but the
SignificantOtherID is not set, then that does nothing to associate the
person with the correct "family". I have stated that PrimaryFamilyMember
should, indeed MUST be the same thing as a test on SignificantOtherID. When
SOID (abbreviating it now) is null, then that person is the Primary. When
SOID is not null, then that person is the Primary. In addition, when a
person's MemberID is the SOID of any other person in the table, then that
person's SOID has to be null. If someone is the Primary for another, then
they cannot have someone else as their primary. Can you agree with that?

Building the database so this is always true, and so users cannot destroy
this delicate requirement, would be the goal of my efforts. The first step
is to repair any damage you have now, supported by queries that report any
exceptions to the above. Simultaneously, a new design for the form to do
this can be arranged.

If these things are not done, then the database will almost certainly
continue to have inconsistencies that make this task impossible. This is
not a simple task, but it is one I've faced and, hopefully, conquered more
than once.

If you wish another opinion than mine, start another thread. I know a
number of those who are expert and post here, the MVPs. I expect that
almost any of them will tell you the same thing, but probably in other
words. That alone may be helpful.

Creating and maintaining a self referencing table can be one of the better
challenges in database construction. I suggest you review our
communications and see if you have a grasp on this now. If not, you really
should get another opinion. Perhaps someone else will explain it better, or
at least differently.

I'm very sorry that I haven't been more helpful to you, but I do not regret
the attempt. My motivation is to get you the help I'm convinced you need,
whether it is I who do it, or someone else. It's your choice, and if you
want to continue with me, I'll keep it up, but there's nothing I can think
of which I haven't included already. If you ask a specific question, I'll
respond. If you wish a review of the primary facts, which I thought I had
provided, I'll do so again.

Just let me know where you want to go next. I'll help if I can.

Tom Ellison

Tom,
Thanks for the reply. This application is for a non-profit organization
for
which I'm on the board. Obviously, Access is not something I do
particularly
well but we are trying to get this new db in place (it's still very much a
work in progress). No promotion, no raise, no profit involved.

I sense that we are still having a miscommunication. PrimaryFamilyMember
is
actually a new field that I have tried to put in place solely to add
members
"bucks" together. It isn't used for any other reason.

SignificantOtherID was designed specifically to indicate if a member was
married (or the significant other) of another member. All of the reports
that use SignificantOtherID, simply link the two members together. If a
member does not have a significant other that is a member,
SignificantOtherID
is null. I'm not sure how this would lead to corrupted data, but I'd
appreciate your help.

The only issue I have is adding bucks together. In order to do that, I
feel
that I need to have the "couple" listed in a consistent order. I haven't
been able to do that with SignificantOtherID. That's really all I'm
trying
to do and haven't yet resolved. Thanks.

Dear Stephanie:

When you have two columns in one table that absolutely MUST agree with
one
another you need a solution that easily prevents them from disagreeing,
or
you need to unify them in one column. While I do not know what your
reasoning is for never having SignificantOtherID = ContactID, there is
then
only one alternative I can see, and that is to have SignificantOtherID be
NULL. Either approach is acceptable, as you can enforce referential
integrity as needed, and both can be tested. So, if you choose the NULL
approach, as I think you are saying, you have my agreement and approval.
However, my position is that there is no difference. Your reason for not
wanting SignificantOtherID = ContactID is, if it is a technically valid
problem, would then be an objection to having it be NULL.

It's probably my math background speaking here. In set theory, this
works
the same way. We had nulls, and we had self-references. Either can be
defined to be the rule. The only thing important is to pick one way or
the
other and stick with it, enforce it.

Keeping PrimaryFamilyMember is an important requirement for any legacy
portions of your system. Making it invisible, setting it according to
the
current state of SignificantOtherID, and making sure it is set
appropriately
any time the row is added or updated will be a perfectly functional
change
to make. Whether you eventually remove it or not is probably optional.
But
if you continue to have it and maintain it, there's some chance it will
eventually be corrupted.

In my experience, there is sometimes a need to access the data to fix
things
through the table access, or through scratch queries. Anyone, perhaps
not
you, who must do so in future months or years, and may not be aware there
are two columns in the table that MUST agree in this way is a pitfall to
them. If they screw up the database by setting a few PrimaryFamilyMember
values, or manually adding some new ones, then a big problem can ensue.
So,
I find this to be a less than "clean" alternative, but a very appropriate
intermediate step. Keeping it around temporarily until you have found
and
changed existing software to use SignificantOtherID instead of
PrimaryFamilyMember is part of a contiguous and very tidy development
process on a database that is in use.

I intended to suggest you use some query work I believe I already posted,
at
least in fragmental form, to immediately determine where the data is
currently "corrupted" in this way. This would allow users to find and
repair these till there are none any more. It would also train users not
to
misuse the software while it still shows the PrimaryFamilyMember check
box.
You cannot make this invisible until all such errors are removed. The
upgrade to set it automatically and then making it invisible could then
be
taken. Putting a tempory test in the form that warns when it is wrong is
good, too. I don't know if it will be practical as the ONLY way to see
if
there are any corruptions remaining. Will someone be scrolling through
every member in the form, with a test for the corruption? When you want
to
know if it is time to install the new form, with PrimaryFamilyMember
disappearing, will you also step through every member? Or, would you
rather
run a query that quickly and automatically finds and displays them? I
suppose if the table is small, the former is fine. For me, the query is
a 3
minute job to create, and answers the question in a second. I wouldn't
be
without that, and would consider the test in the form. It's a lot more
programming time, and may or may not be as effective.

I've enjoyed working on this with you (if you'll allow me to include
myself
in the "credits" for your project as "gaffer"). I sense we're close to a
conclusion, and that's satisfying as well. I'd still like to hear at
least
one time how it has worked out. Did you get a raise and/or promotion?

Tom Ellison

Tom,
Thanks for the reply. I had John Doe listed twice because I had 2
people
tied to him through SignificantOtherID just to see what happened.
Unfortunatley, I did have Scott Doe (John's son) also listed as the
PrimaryFamilyMember so I can see the data integrity issues.

However, I absolutely do not want a contact to be their own significant
other. ContactID=SignificantOtherID is not acceptable.

To me the best method seems to be leaving both SignificantOtherID and
PrimaryFamilyMember intact. I'd like to have PrimaryFamilyMember
default
as
"yes" but be invisible. When SignificantOtherID is populated, have
PrimaryFamilyMember become visible with a message box asking if this
individual will be the primary family member, and if not to uncheck the
box
so that there is only one primary in the family- maybe a reminder msg
box
indicating that there should only be one primary in a family
membership.
Could be user error, but there you have it. There is going to have to
be
some manual intervention. Is this acceptable? Can it be done? Could
you
please help me with the code? Thanks.



:

Dear Stephanie:

I feel your pain.

Any time your database has 2 ways to represent the same thing, there
is
the
danger that the data will have rows that represent that same thing in
contradictory ways. It is my interpretation of a certain rule of
database
design that this must be avoided. Early in this thread I tried to
point
this out when I was recommending you drop the Boolean (yes/no)
PrimaryFamilyNumber. I now recommend you search to find out how
prevalent
this problem is. The problem occurs whenever PrimaryFamilyMember is
False
and the SignificantOtherID = ContactID, or when PrimaryFamilyMember is
True
and SignificantOtherID <> ContactID. Do you agree with that?

Assuming you do, you should be able to derive the value now in
PrimaryFamilyMember by testing whether SignificantOtherID = ContactID.
Do
you see now that this theme has permiated what I wrote you previously?
And
do you see that this is at the core of your problem? I think you do,
and
the light has just come on. Good!

Obviously, then, this is why my solution has not been working for you.
These are inconsistencies in the data that I expected all along may
cause
just this problem!

I don't intend to "rub it in, now" but I am feeling a bit vindicated
in
my
opinions expressed earlier. Mostly, I'm just glad the light has come
on
for
you! However, with the data in its current state, neither of us can
proceed
to perform what you originally requested, which was to sort everything
so
John and Mary are sorted adjacent, with John first. I'm just trying
here
to
keep the target in mind.

You should not eliminate the PrimaryFamilyMember at this time, not
until
you
have made SignivicantOtherID to be consistent with it. I believe it
is
likely that in most, if not all, cases where the inconsistency exists
PrimaryFamilyMember has been set correctly and SignificantOtherID
needs
to
be changed. Do you agree?

I do not know any better way to work this out than to prepare the
query I
gave above to show all these inconsistencies so someone can go to work
on
them to eliminate the problem, in most cases by setting the correct
SignificantOtherID. Once this is repaired, you can quit using
PrimaryFamilyMember in all new work, and you can remove it from your
form.
You do not have to remove it from your table, indeed, you should not
do
this
until you have removed any existing code that still uses
PrimaryFamilyMember. Doing so would break that code.

The other thing needed would be to modify your form(s) that allow
adding
new
members, or changing SignificantOtherID, so that the value of
PrimaryFamilyMember is set correctly and automatically whenever such
changes
are made. I believe the BeforeUpdate event is adequate for this.

Why John shows up twice is the next problem to address. I'm thinking
this
is a JOIN problem in there somewhere. That means finding in which of
the
related tables are there 2 rows matching the relationship specified in
the
JOIN . . . ON portions of your query. My first guess is DuesLineItem.
Do
you have two of these for John's ContactID. If so, do you want to
show
both
of them, only one, or aggregate them? These are the only simple
choices.
The answer will depend on the context of the uses you intend for this
query.
So, that will be up to you.

I hope I'm not suddenly seeming to be overbearing. Actually I'm quite
pleased that you've discovered what I have been trying to say for a
while.
The problem is one I expected from early on. My poor powers of
persuasion
and explanation just couldn't convey what I needed to say, but you
found
it
out for yourself in spite of this. Good Job! I'm happy for you.

Well, the pressure to answer my questions is only in that they are a
prerequisite to having my help, if help it is. If it is helping, I'm
satisfied that you think so. You also have the option to start a new
thread
and ask your questions again, asking me to butt out. Others here are
quite
capable of steering you through this maze. I would never bear a
grudge
if
you did so. Anyway, I hope the pressure is the result of your desire
to
obtain my assistance, and that's the natural price for it. Unless I'm
informed, I can't really help. I ask the questions I need to ask in
order
to try to be of assistance. You answer them only if you are finding
that
assistance worth your time, effort, and frustration. So, no hard
feelings
either way!

Or, I ask questions to try to get you to look in the right place to
find
the
source of your difficulty. That was probably my motivation in this
case.
Looking at the questions in my previous post, were they not exactly
the
ones
needed to guide you to the problem you have now discovered? Were they
not
what led you to your epiphany? I'm hoping so, that I may have played
a
small part in guiding you to where you are now, which is exactly where
I
wanted you to be, given the assumptions I have had all along.

Tom Ellison


Tom,
The pressure to answer your questions correctly! And execellent
question
it
is, because I think I see the problem.
Susie's BaseName is Susie Anderson, but her SignificantOtherID
points
to
herself and not John Doe.
John's BaseName is John Doe, and his SignificantOtherID points to
himself
and not Susie Anderson.
And strangley, John Doe shows up in the query results twice- each
time
pointing to himself as the SignficantOtherID.

I know that this query below uses PrimaryFamilyMember which we are
trying
to
do away with in favor of SignificantOtherID, but it does bring back
the
correct BaseName, ContactID and SignificantOtherID (if that helps):
SELECT Nz(c1.NickName,c1.FirstName) & " " & c1.LastName AS [Member
Name],
IIf(PrimaryFamilyMember,[Member Name],(SELECT Nz(c2.[NickName],
c2.[FirstName]) & " " & c2.[LastName] FROM Contacts c2 WHERE
c2.ContactID =
c1.SignificantOtherID)) AS [Base Name], C1.FirstName, C1.LastName,
C1.ContactID, C1.SignificantOtherID
FROM (PaymentTypes INNER JOIN (DuesItemType INNER JOIN (Contacts AS
C1
INNER
JOIN (DuesLineItem INNER JOIN tblDuesRates ON
DuesLineItem.DuesRateID =
tblDuesRates.DuesRateID) ON C1.ContactID = DuesLineItem.ContactID)
ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) ON
PaymentTypes.TypeID = DuesLineItem.TypeID) INNER JOIN MemberCategory
ON
C1.MemberCategoryID = MemberCategory.MemberCategoryID;

Couldn't I just make PrimaryFamilyMember default to "yes" and be
invisible
unless SignificantOtherID is not null, and then show
PrimaryFamilyMember
so
the user could uncheck as appropriate? Thanks for your help!

:

Dear Stephanie:

So, what did Susie's BaseName turn out to be? Is her
SignificantOtherID
NULL? Is it set to the ContactID of John?

Please carefully confirm your facts. I need to know whether there
is
a
problem in the code, and what that might be, or if the data is not
as
expected. I could probably offer some adjustment with this
knowledge,
but I
don't have the database in front of me, as you do. I must rely on
you
to
thoroughly and carefully investigate the cause of your difficulty.
All I
can do is give hints!

Tom Ellison


Tom,
I apologize for the delayed response and appreciate you hanging
in
there!

I have this query that I've called DuesByMemberBase:
SELECT IIf(SignificantOtherID Is Null,c1.[LastName] & ", " &
Nz(c1.NickName,c1.FirstName),(SELECT c2.LastName & ", " &
Nz(c2.NickName,
c2.FirstName)
FROM Contacts c2 WHERE c2.ContactID = c1.SignificantOtherID)) AS
[BaseName]
FROM Contacts AS c1;

This does bring back a Base Name for each person. However, I
expected
Susie
Anderson's BaseName to be John Doe, because I want to be able to
add