Name order streamlining

[Stephanie]

Hi.
I have a query that's sloppy, and I'd like to revise the name order.
I have members that can be spouses. If there is a member spouse, I'd like
their names to appear in a particular order: John Doe and Susie Anderson
(rather than Susie Anderson and John Doe).
Originally, I was relying on SignificantOtherID to determine order. But have
since added a PrimaryFamilyMember field. The spouse with the
PrimaryFamilyMember as Yes should appear first. I'd appreciate any help in
streamlining what I have strung together. Thanks!

SELECT Nz(c1.NickName,c1.FirstName) & " " & c1.LastName AS [Member Name],
C1.PrimaryFamilyMember, (SELECT Nz(c2.[NickName],c2.[FirstName]) & " " &
c2.[LastName] AS [SONm] FROM Contacts c2 WHERE c2.ContactID =
Nz(c1.SignificantOtherID,-9999)) AS [SO Name], C1.SignificantOtherID,
(IIf(Len(Trim([SO Name]) & "")=0,Trim([Member Name]), Trim([Member Name] & "
& " & [SO Name]))) AS JointName

 

[Tom Ellison]

Dear Stephanie:

You want the names in a specific order. Is that always husband, then wife?

Is the data structured to ensure you can tell which is which? The
SignificantOtherID column, do both the husband and wife have that, linked
both ways?

To see them together, you would need to sort on something like this, if
possible:

Member Name Base Name PrimaryFamilyMember
John Doe John Doe -1 (yes)
Susie Anderson John Doe 0 (no)

From the query you showed, try this:

SELECT [Member Name], [Member Name] AS BaseName,
PrimaryFamilyMember
FROM YourQuery
WHERE PrimaryFamilyMember <> 0
UNION ALL
SELECT [Member Name], [SO Name], 0
FROM YourQuery
WHERE [SO Name] <> -9999

Then, select from the UnionQuery (or name it something else):

SELECT *
FROM UnionQuery
ORDER BY BaseName, PrimaryFamilyMember

That should put them together the way you want. However, wouldn't it be
possible to have accidentally made both of them Primary? That would screw
up the order, and I think would duplicate them. Wouldn't it be fun to write
a query to detect those?

I think that having the BaseName in the table would be useful. It might be
less complex in the long run. See what I mean?

Tom Ellison

 

[Stephanie]

Tom,
This is really an extension of the balance forward issue that you helped me
with!
I'm trying to find a way to report on the dues for the year and the
"associated" payments (those for that same year).

The SignificantOtherID appears on each of the spouse member's record. The
PrimaryFamilyMember could be the husband or wife. So the JointName could be
Susie Anderson (primary) & John Doe.

Users could forget to enter SignificantOtherID on one or both records, and
forget or duplicate PrimaryFamilyMember. So I don't really see how having
another field (BaseName) to mess up helps, but I'd appreciate your
enlightenment. As well as help with a query that would let me know if I have
these type of issues.

Basically, with the query I have I thought that if I could get the JointName
in the correct order, I could sort a report by JointName and Year. I
actually like the syntax I have for JointName. But I'm not so good a series
of queries (short attention span, and all).

The first step is to get the JointName for member spouses where the
PrimaryFamilyMember flag is Yes, in the name order of Primary & "Secondary".
Here's what I have:
SELECT Year([DateCreated]) AS [Date Year], Nz(c1.NickName,c1.FirstName) & "
" & c1.LastName AS [Member Name], C1.PrimaryFamilyMember, (SELECT
Nz(c2.[NickName],c2.[FirstName]) & " " & c2.[LastName] AS [SONm] FROM
Contacts c2 WHERE c2.ContactID = Nz(c1.SignificantOtherID,-9999)) AS [SO
Name], C1.SignificantOtherID, DuesLineItem.Reference, DuesLineItem.Amount,
DuesLineItem.DateRemitted, DuesLineItem.DateCreated, C1.LastName,
C1.FirstName, PaymentTypes.Type, DuesItemType.DuesItemType,
tblDuesRates.DuesAmount, (IIf(Len(Trim([SO Name]) & "")=0,Trim([Member
Name]),Trim([Member Name] & " & " & [SO Name]))) AS JointName
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
WHERE PrimaryFamilyMember <> 0;

Then, I suppose I need to get those records where the PrimaryFamilyMember is
No, but in the JointName order that I want. This statement gave me fits:
SELECT [Member Name], [SO Name], 0
What is it suppose to do?

Last, I'll need to get the members without member spouses
(SignificantOtherID null) into the mix.

Thanks for the help!

Dear Stephanie:

You want the names in a specific order. Is that always husband, then wife?

Is the data structured to ensure you can tell which is which? The
SignificantOtherID column, do both the husband and wife have that, linked
both ways?

To see them together, you would need to sort on something like this, if
possible:

Member Name Base Name PrimaryFamilyMember
John Doe John Doe -1 (yes)
Susie Anderson John Doe 0 (no)

From the query you showed, try this:

SELECT [Member Name], [Member Name] AS BaseName,
PrimaryFamilyMember
FROM YourQuery
WHERE PrimaryFamilyMember <> 0
UNION ALL
SELECT [Member Name], [SO Name], 0
FROM YourQuery
WHERE [SO Name] <> -9999

Then, select from the UnionQuery (or name it something else):

SELECT *
FROM UnionQuery
ORDER BY BaseName, PrimaryFamilyMember

That should put them together the way you want. However, wouldn't it be
possible to have accidentally made both of them Primary? That would screw
up the order, and I think would duplicate them. Wouldn't it be fun to write
a query to detect those?

I think that having the BaseName in the table would be useful. It might be
less complex in the long run. See what I mean?

Tom Ellison

Hi.
I have a query that's sloppy, and I'd like to revise the name order.
I have members that can be spouses. If there is a member spouse, I'd like
their names to appear in a particular order: John Doe and Susie Anderson
(rather than Susie Anderson and John Doe).
Originally, I was relying on SignificantOtherID to determine order. But
have
since added a PrimaryFamilyMember field. The spouse with the
PrimaryFamilyMember as Yes should appear first. I'd appreciate any help
in
streamlining what I have strung together. Thanks!

SELECT Nz(c1.NickName,c1.FirstName) & " " & c1.LastName AS [Member Name],
C1.PrimaryFamilyMember, (SELECT Nz(c2.[NickName],c2.[FirstName]) & " " &
c2.[LastName] AS [SONm] FROM Contacts c2 WHERE c2.ContactID =
Nz(c1.SignificantOtherID,-9999)) AS [SO Name], C1.SignificantOtherID,
(IIf(Len(Trim([SO Name]) & "")=0,Trim([Member Name]), Trim([Member Name] &
"
& " & [SO Name]))) AS JointName

 

[Tom Ellison]

Dear Stephanie:

I recognized the first name as being familiar. I certainly remember the
balance forward discussion. But, I hadn't tied the two together. Sorry,
but I answer several questions a day on the average, and it can become a bit
of a blur.

The additional column for base name could be quite invisible to users. It
would be derived. As such, it would technically be a violation of the rules
for normalization, but we sometimes allow for this in designs when there is
good reason for it, and a controlled way of implementing it. You can
implement it at the query level if you prefer. The difference would be
performance. Since you need to order on BaseName, you'd be building the
index every time you query. For now, let's assume it is derived and not
stored.

So, let's start by deriving it. When the row is a PrimaryFamilyMember, it's
the name in the current row. When not a PrimaryFamilyMember, it's the name
in another row designated by the SignificantOtherID. So:

SELECT IIf(PrimaryFamilyNumber,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
FROM Contacts T

This funtions on the basis that, when a row is not a PrimaryFamilyMember, it
MUST have a SignificantOtherID. It would be a good idea to test this
assumption to see how often this fails in your current data:

SELECT *
FROM Contacts
WHERE NOT PrimaryFamilyMember
AND SignificantOtherID IS NULL

Am I correct that this should not happen? If it is happening a lot, then
I'd like to make a suggestion. You can build the table so this cannot
happen. Here's how I'd suggest doing that.

I'm proposign that you not allow SignificantOtherID to be NULL. When the
row is a PrimaryFamilyMember, put the ContactID in the SignificantOtherID.
The row then points to itself. That's logically reasonable, isn't it? It's
saying, "I am the PrimaryFamilyMember in this Family, so the
SignificantOther is ME." In fact, this obviates the need for the
PrimaryFamilyMember column. A PrimaryFamilyMember is a row where ContactID
= SignificantOtherID.

This design is a "self-referencing table" style. That's probably the
correct way to do this, but it makes for a different set of rules in some
ways. For example, it creates sets of interrelated rows. A rule must be
observed for integrity of this. There should be exactly one
PrimaryFamilyMember in each set. Not more, not less. That member must be
the only one designated as the PrimaryFamilyMember.

How can you best enforce these rules? Eliminating the PrimaryFamilyMember
column (eventually, not initially!) will help. That's just one less thing
to be enforced. The other rule is that every row that is referenced by
another row must be a PrimaryFamilyMember, initally with the
PrimaryFamilyMember column set to True, and eventually with ContactID =
SignificantOtherID.

This second rule can be enforced! During data entry, the
PrimaryFamilyMember row must be put in first, right? Well, there would be a
combo box of Members in which to select who the SignificantOther is. This
combo box should be created so it lists ONLY the PrimaryFamilyMembers in the
table. However, when entering a new member in the table, that row would not
be listed.

What I would do is to have an unbound check box that appears on the form
only when entering a NewRecord. When this check box is checked, the combo
box for SignificantOther disappears from the form. The BeforeUpdate event
should test the check box. If it's checked, then set the combo box (which
is invisible, but still there) to a value of empty. Have the table
definition set the SignificantOtherID to a default value of the ContactID.
When the row is added, it will be set up as desired. The user's cannot
screw this up.

If a row is added without the box checked and without selecting a
SignificantOther, it will become a PrimaryFamilyMember, because the
SignificantOtherID will default to the ContactID of itself. The check box
is a good idea anyway, in case the user accidentally selects some
SignificantOther in the combo box, but means for it to be a
PrimaryFamilyMember. You can even clear the selection on the check box
click event when it changes to True.

Doing all you can do to prevent corruption of the data, even a "logical"
corruption like this, is a very positive step. When integrity is
questionable, a test for integrity should be built and run periodically.

I'm going to stop for now. This isn't finished, but maybe it's enough to
think about for a while. I may not be back to check on this till Sunday.
Enjoy!

Tom Ellison

Tom,
This is really an extension of the balance forward issue that you helped
me
with!
I'm trying to find a way to report on the dues for the year and the
"associated" payments (those for that same year).

The SignificantOtherID appears on each of the spouse member's record. The
PrimaryFamilyMember could be the husband or wife. So the JointName could
be
Susie Anderson (primary) & John Doe.

Users could forget to enter SignificantOtherID on one or both records, and
forget or duplicate PrimaryFamilyMember. So I don't really see how having
another field (BaseName) to mess up helps, but I'd appreciate your
enlightenment. As well as help with a query that would let me know if I
have
these type of issues.

Basically, with the query I have I thought that if I could get the
JointName
in the correct order, I could sort a report by JointName and Year. I
actually like the syntax I have for JointName. But I'm not so good a
series
of queries (short attention span, and all).

The first step is to get the JointName for member spouses where the
PrimaryFamilyMember flag is Yes, in the name order of Primary &
"Secondary".
Here's what I have:
SELECT Year([DateCreated]) AS [Date Year], Nz(c1.NickName,c1.FirstName) &
"
" & c1.LastName AS [Member Name], C1.PrimaryFamilyMember, (SELECT
Nz(c2.[NickName],c2.[FirstName]) & " " & c2.[LastName] AS [SONm] FROM
Contacts c2 WHERE c2.ContactID = Nz(c1.SignificantOtherID,-9999)) AS [SO
Name], C1.SignificantOtherID, DuesLineItem.Reference, DuesLineItem.Amount,
DuesLineItem.DateRemitted, DuesLineItem.DateCreated, C1.LastName,
C1.FirstName, PaymentTypes.Type, DuesItemType.DuesItemType,
tblDuesRates.DuesAmount, (IIf(Len(Trim([SO Name]) & "")=0,Trim([Member
Name]),Trim([Member Name] & " & " & [SO Name]))) AS JointName
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
WHERE PrimaryFamilyMember <> 0;

Then, I suppose I need to get those records where the PrimaryFamilyMember
is
No, but in the JointName order that I want. This statement gave me fits:
SELECT [Member Name], [SO Name], 0
What is it suppose to do?

Last, I'll need to get the members without member spouses
(SignificantOtherID null) into the mix.

Thanks for the help!

Dear Stephanie:

You want the names in a specific order. Is that always husband, then
wife?

Is the data structured to ensure you can tell which is which? The
SignificantOtherID column, do both the husband and wife have that, linked
both ways?

To see them together, you would need to sort on something like this, if
possible:

Member Name Base Name PrimaryFamilyMember
John Doe John Doe -1 (yes)
Susie Anderson John Doe 0 (no)

From the query you showed, try this:

SELECT [Member Name], [Member Name] AS BaseName,
PrimaryFamilyMember
FROM YourQuery
WHERE PrimaryFamilyMember <> 0
UNION ALL
SELECT [Member Name], [SO Name], 0
FROM YourQuery
WHERE [SO Name] <> -9999

Then, select from the UnionQuery (or name it something else):

SELECT *
FROM UnionQuery
ORDER BY BaseName, PrimaryFamilyMember

That should put them together the way you want. However, wouldn't it be
possible to have accidentally made both of them Primary? That would
screw
up the order, and I think would duplicate them. Wouldn't it be fun to
write
a query to detect those?

I think that having the BaseName in the table would be useful. It might
be
less complex in the long run. See what I mean?

Tom Ellison

Hi.
I have a query that's sloppy, and I'd like to revise the name order.
I have members that can be spouses. If there is a member spouse, I'd
like
their names to appear in a particular order: John Doe and Susie
Anderson
(rather than Susie Anderson and John Doe).
Originally, I was relying on SignificantOtherID to determine order. But
have
since added a PrimaryFamilyMember field. The spouse with the
PrimaryFamilyMember as Yes should appear first. I'd appreciate any
help
in
streamlining what I have strung together. Thanks!

SELECT Nz(c1.NickName,c1.FirstName) & " " & c1.LastName AS [Member
Name],
C1.PrimaryFamilyMember, (SELECT Nz(c2.[NickName],c2.[FirstName]) & " "
&
c2.[LastName] AS [SONm] FROM Contacts c2 WHERE c2.ContactID =
Nz(c1.SignificantOtherID,-9999)) AS [SO Name], C1.SignificantOtherID,
(IIf(Len(Trim([SO Name]) & "")=0,Trim([Member Name]), Trim([Member
Name] &
"
& " & [SO Name]))) AS JointName

 

[Stephanie]

Tom,
I have about 5 couples who are member spouses. SignificantOtherID is null
unless there is a member spouse, and PrimaryFamilyID is null unless there is
a member spouse. While the self-referencing table concept is interesting,
it's very complicated and I'm not really sure that the added value is worth
the effort.

At this point, all I really want to do, is have a query that returns the
names in a consistent order for member spouses. I'd perfer the
"PrimaryFamilyID = Yes" spouse to come first, but it doesn't really matter.
What does matter is that I consistently return: John Doe and Susie Anderson
and not Susie Anderson and John Doe
So that I can group my records.
Thanks.

Dear Stephanie:

I recognized the first name as being familiar. I certainly remember the
balance forward discussion. But, I hadn't tied the two together. Sorry,
but I answer several questions a day on the average, and it can become a bit
of a blur.

The additional column for base name could be quite invisible to users. It
would be derived. As such, it would technically be a violation of the rules
for normalization, but we sometimes allow for this in designs when there is
good reason for it, and a controlled way of implementing it. You can
implement it at the query level if you prefer. The difference would be
performance. Since you need to order on BaseName, you'd be building the
index every time you query. For now, let's assume it is derived and not
stored.

So, let's start by deriving it. When the row is a PrimaryFamilyMember, it's
the name in the current row. When not a PrimaryFamilyMember, it's the name
in another row designated by the SignificantOtherID. So:

SELECT IIf(PrimaryFamilyNumber,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
FROM Contacts T

This funtions on the basis that, when a row is not a PrimaryFamilyMember, it
MUST have a SignificantOtherID. It would be a good idea to test this
assumption to see how often this fails in your current data:

SELECT *
FROM Contacts
WHERE NOT PrimaryFamilyMember
AND SignificantOtherID IS NULL

Am I correct that this should not happen? If it is happening a lot, then
I'd like to make a suggestion. You can build the table so this cannot
happen. Here's how I'd suggest doing that.

I'm proposign that you not allow SignificantOtherID to be NULL. When the
row is a PrimaryFamilyMember, put the ContactID in the SignificantOtherID.
The row then points to itself. That's logically reasonable, isn't it? It's
saying, "I am the PrimaryFamilyMember in this Family, so the
SignificantOther is ME." In fact, this obviates the need for the
PrimaryFamilyMember column. A PrimaryFamilyMember is a row where ContactID
= SignificantOtherID.

This design is a "self-referencing table" style. That's probably the
correct way to do this, but it makes for a different set of rules in some
ways. For example, it creates sets of interrelated rows. A rule must be
observed for integrity of this. There should be exactly one
PrimaryFamilyMember in each set. Not more, not less. That member must be
the only one designated as the PrimaryFamilyMember.

How can you best enforce these rules? Eliminating the PrimaryFamilyMember
column (eventually, not initially!) will help. That's just one less thing
to be enforced. The other rule is that every row that is referenced by
another row must be a PrimaryFamilyMember, initally with the
PrimaryFamilyMember column set to True, and eventually with ContactID =
SignificantOtherID.

This second rule can be enforced! During data entry, the
PrimaryFamilyMember row must be put in first, right? Well, there would be a
combo box of Members in which to select who the SignificantOther is. This
combo box should be created so it lists ONLY the PrimaryFamilyMembers in the
table. However, when entering a new member in the table, that row would not
be listed.

What I would do is to have an unbound check box that appears on the form
only when entering a NewRecord. When this check box is checked, the combo
box for SignificantOther disappears from the form. The BeforeUpdate event
should test the check box. If it's checked, then set the combo box (which
is invisible, but still there) to a value of empty. Have the table
definition set the SignificantOtherID to a default value of the ContactID.
When the row is added, it will be set up as desired. The user's cannot
screw this up.

If a row is added without the box checked and without selecting a
SignificantOther, it will become a PrimaryFamilyMember, because the
SignificantOtherID will default to the ContactID of itself. The check box
is a good idea anyway, in case the user accidentally selects some
SignificantOther in the combo box, but means for it to be a
PrimaryFamilyMember. You can even clear the selection on the check box
click event when it changes to True.

Doing all you can do to prevent corruption of the data, even a "logical"
corruption like this, is a very positive step. When integrity is
questionable, a test for integrity should be built and run periodically.

I'm going to stop for now. This isn't finished, but maybe it's enough to
think about for a while. I may not be back to check on this till Sunday.
Enjoy!

Tom Ellison

Tom,
This is really an extension of the balance forward issue that you helped
me
with!
I'm trying to find a way to report on the dues for the year and the
"associated" payments (those for that same year).

The SignificantOtherID appears on each of the spouse member's record. The
PrimaryFamilyMember could be the husband or wife. So the JointName could
be
Susie Anderson (primary) & John Doe.

Users could forget to enter SignificantOtherID on one or both records, and
forget or duplicate PrimaryFamilyMember. So I don't really see how having
another field (BaseName) to mess up helps, but I'd appreciate your
enlightenment. As well as help with a query that would let me know if I
have
these type of issues.

Basically, with the query I have I thought that if I could get the
JointName
in the correct order, I could sort a report by JointName and Year. I
actually like the syntax I have for JointName. But I'm not so good a
series
of queries (short attention span, and all).

The first step is to get the JointName for member spouses where the
PrimaryFamilyMember flag is Yes, in the name order of Primary &
"Secondary".
Here's what I have:
SELECT Year([DateCreated]) AS [Date Year], Nz(c1.NickName,c1.FirstName) &
"
" & c1.LastName AS [Member Name], C1.PrimaryFamilyMember, (SELECT
Nz(c2.[NickName],c2.[FirstName]) & " " & c2.[LastName] AS [SONm] FROM
Contacts c2 WHERE c2.ContactID = Nz(c1.SignificantOtherID,-9999)) AS [SO
Name], C1.SignificantOtherID, DuesLineItem.Reference, DuesLineItem.Amount,
DuesLineItem.DateRemitted, DuesLineItem.DateCreated, C1.LastName,
C1.FirstName, PaymentTypes.Type, DuesItemType.DuesItemType,
tblDuesRates.DuesAmount, (IIf(Len(Trim([SO Name]) & "")=0,Trim([Member
Name]),Trim([Member Name] & " & " & [SO Name]))) AS JointName
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
WHERE PrimaryFamilyMember <> 0;

Then, I suppose I need to get those records where the PrimaryFamilyMember
is
No, but in the JointName order that I want. This statement gave me fits:
SELECT [Member Name], [SO Name], 0
What is it suppose to do?

Last, I'll need to get the members without member spouses
(SignificantOtherID null) into the mix.

Thanks for the help!

Dear Stephanie:

You want the names in a specific order. Is that always husband, then
wife?

Is the data structured to ensure you can tell which is which? The
SignificantOtherID column, do both the husband and wife have that, linked
both ways?

To see them together, you would need to sort on something like this, if
possible:

Member Name Base Name PrimaryFamilyMember
John Doe John Doe -1 (yes)
Susie Anderson John Doe 0 (no)

From the query you showed, try this:

SELECT [Member Name], [Member Name] AS BaseName,
PrimaryFamilyMember
FROM YourQuery
WHERE PrimaryFamilyMember <> 0
UNION ALL
SELECT [Member Name], [SO Name], 0
FROM YourQuery
WHERE [SO Name] <> -9999

Then, select from the UnionQuery (or name it something else):

SELECT *
FROM UnionQuery
ORDER BY BaseName, PrimaryFamilyMember

That should put them together the way you want. However, wouldn't it be
possible to have accidentally made both of them Primary? That would
screw
up the order, and I think would duplicate them. Wouldn't it be fun to
write
a query to detect those?

I think that having the BaseName in the table would be useful. It might
be
less complex in the long run. See what I mean?

Tom Ellison


Hi.
I have a query that's sloppy, and I'd like to revise the name order.
I have members that can be spouses. If there is a member spouse, I'd
like
their names to appear in a particular order: John Doe and Susie
Anderson
(rather than Susie Anderson and John Doe).
Originally, I was relying on SignificantOtherID to determine order. But
have
since added a PrimaryFamilyMember field. The spouse with the
PrimaryFamilyMember as Yes should appear first. I'd appreciate any
help
in
streamlining what I have strung together. Thanks!

SELECT Nz(c1.NickName,c1.FirstName) & " " & c1.LastName AS [Member
Name],
C1.PrimaryFamilyMember, (SELECT Nz(c2.[NickName],c2.[FirstName]) & " "
&
c2.[LastName] AS [SONm] FROM Contacts c2 WHERE c2.ContactID =
Nz(c1.SignificantOtherID,-9999)) AS [SO Name], C1.SignificantOtherID,
(IIf(Len(Trim([SO Name]) & "")=0,Trim([Member Name]), Trim([Member
Name] &
"
& " & [SO Name]))) AS JointName

 

[Tom Ellison]

Dear Stephanie:

A self-referencing table is not something I'm recommending, it is what you
already have. I'm not trying to get you to change that, but I did recommend
how to protect its integrity.

In order to do the sorting you said you want, you must have the BaseName
column I talked about. That's what sorts the family together. You can do
this in a table, but I'm not currently advocating that. That means you must
derive it in a query. I've showed how to do that. Whether you fix up the
table to protect data integrity or not, the BaseName approach is essential.
However, it will suffer from the types of data inconsistencies I warned
about, and for which I propose you query to see how bad the problem will be.

So, my response is not a recommendation to radically change your table
structure. I did propose to simplify it and then protect it. That's
optional, but it will affect how well the queries work. Simplifying the
table will result in an easier task of protecting the data integrity.
Having complete integrity in the data will mean the query I propose will
work better. The thing about adding BaseName to the actual table, instead
of deriving it, is so you can index on it. That's only a performance issue,
and we can forget that for now. Perhaps I shouldn't have mentioned that so
early on. If it isn't needed, then let's forget it and move on. We can
revisit it later if it becomes important.

I gave this query before to derive the BaseName:

SELECT IIf(PrimaryFamilyNumber,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
FROM Contacts T

I'll fix that slightly now:

SELECT IIf(PrimaryFamilyMember,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
AS BaseName,
PrimaryFamilyMember, NickName, FirstName, LastName,
ContactID, SignificantOtherID
FROM Contacts T

Here, I've changed PrimaryFamilyNumber to PrimaryFamilyMember (I must have
been tired), put an alias for BaseName, and added other columns so you can
see if this is working correctly. If it is, you can put this into your
existing query in the ORDER BY. Then all the family will be together in the
sort order. If you want to use it in a report, since reports do not follow
the sort order of the query, it will need to be a column in the query
results also, so the report can sort by it. Sort also by the
PrimaryFamilyMember so you can get the primary member first within each
family group.

Based on your original request, and ignoring for the moment where the data
is messed up, this will do the whole thing for you. The rest is just about
the problems created where the data is not consistent with what you require.

Tom Ellison

Tom,
I have about 5 couples who are member spouses. SignificantOtherID is null
unless there is a member spouse, and PrimaryFamilyID is null unless there
is
a member spouse. While the self-referencing table concept is interesting,
it's very complicated and I'm not really sure that the added value is
worth
the effort.

At this point, all I really want to do, is have a query that returns the
names in a consistent order for member spouses. I'd perfer the
"PrimaryFamilyID = Yes" spouse to come first, but it doesn't really
matter.
What does matter is that I consistently return: John Doe and Susie
Anderson
and not Susie Anderson and John Doe
So that I can group my records.
Thanks.

Dear Stephanie:

I recognized the first name as being familiar. I certainly remember the
balance forward discussion. But, I hadn't tied the two together. Sorry,
but I answer several questions a day on the average, and it can become a
bit
of a blur.

The additional column for base name could be quite invisible to users.
It
would be derived. As such, it would technically be a violation of the
rules
for normalization, but we sometimes allow for this in designs when there
is
good reason for it, and a controlled way of implementing it. You can
implement it at the query level if you prefer. The difference would be
performance. Since you need to order on BaseName, you'd be building the
index every time you query. For now, let's assume it is derived and not
stored.

So, let's start by deriving it. When the row is a PrimaryFamilyMember,
it's
the name in the current row. When not a PrimaryFamilyMember, it's the
name
in another row designated by the SignificantOtherID. So:

SELECT IIf(PrimaryFamilyNumber,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
FROM Contacts T

This funtions on the basis that, when a row is not a PrimaryFamilyMember,
it
MUST have a SignificantOtherID. It would be a good idea to test this
assumption to see how often this fails in your current data:

SELECT *
FROM Contacts
WHERE NOT PrimaryFamilyMember
AND SignificantOtherID IS NULL

Am I correct that this should not happen? If it is happening a lot, then
I'd like to make a suggestion. You can build the table so this cannot
happen. Here's how I'd suggest doing that.

I'm proposign that you not allow SignificantOtherID to be NULL. When the
row is a PrimaryFamilyMember, put the ContactID in the
SignificantOtherID.
The row then points to itself. That's logically reasonable, isn't it?
It's
saying, "I am the PrimaryFamilyMember in this Family, so the
SignificantOther is ME." In fact, this obviates the need for the
PrimaryFamilyMember column. A PrimaryFamilyMember is a row where
ContactID
= SignificantOtherID.

This design is a "self-referencing table" style. That's probably the
correct way to do this, but it makes for a different set of rules in some
ways. For example, it creates sets of interrelated rows. A rule must be
observed for integrity of this. There should be exactly one
PrimaryFamilyMember in each set. Not more, not less. That member must
be
the only one designated as the PrimaryFamilyMember.

How can you best enforce these rules? Eliminating the
PrimaryFamilyMember
column (eventually, not initially!) will help. That's just one less
thing
to be enforced. The other rule is that every row that is referenced by
another row must be a PrimaryFamilyMember, initally with the
PrimaryFamilyMember column set to True, and eventually with ContactID =
SignificantOtherID.

This second rule can be enforced! During data entry, the
PrimaryFamilyMember row must be put in first, right? Well, there would
be a
combo box of Members in which to select who the SignificantOther is.
This
combo box should be created so it lists ONLY the PrimaryFamilyMembers in
the
table. However, when entering a new member in the table, that row would
not
be listed.

What I would do is to have an unbound check box that appears on the form
only when entering a NewRecord. When this check box is checked, the
combo
box for SignificantOther disappears from the form. The BeforeUpdate
event
should test the check box. If it's checked, then set the combo box
(which
is invisible, but still there) to a value of empty. Have the table
definition set the SignificantOtherID to a default value of the
ContactID.
When the row is added, it will be set up as desired. The user's cannot
screw this up.

If a row is added without the box checked and without selecting a
SignificantOther, it will become a PrimaryFamilyMember, because the
SignificantOtherID will default to the ContactID of itself. The check
box
is a good idea anyway, in case the user accidentally selects some
SignificantOther in the combo box, but means for it to be a
PrimaryFamilyMember. You can even clear the selection on the check box
click event when it changes to True.

Doing all you can do to prevent corruption of the data, even a "logical"
corruption like this, is a very positive step. When integrity is
questionable, a test for integrity should be built and run periodically.

I'm going to stop for now. This isn't finished, but maybe it's enough to
think about for a while. I may not be back to check on this till Sunday.
Enjoy!

Tom Ellison

Tom,
This is really an extension of the balance forward issue that you
helped
me
with!
I'm trying to find a way to report on the dues for the year and the
"associated" payments (those for that same year).

The SignificantOtherID appears on each of the spouse member's record.
The
PrimaryFamilyMember could be the husband or wife. So the JointName
could
be
Susie Anderson (primary) & John Doe.

Users could forget to enter SignificantOtherID on one or both records,
and
forget or duplicate PrimaryFamilyMember. So I don't really see how
having
another field (BaseName) to mess up helps, but I'd appreciate your
enlightenment. As well as help with a query that would let me know if
I
have
these type of issues.

Basically, with the query I have I thought that if I could get the
JointName
in the correct order, I could sort a report by JointName and Year. I
actually like the syntax I have for JointName. But I'm not so good a
series
of queries (short attention span, and all).

The first step is to get the JointName for member spouses where the
PrimaryFamilyMember flag is Yes, in the name order of Primary &
"Secondary".
Here's what I have:
SELECT Year([DateCreated]) AS [Date Year], Nz(c1.NickName,c1.FirstName)
&
"
" & c1.LastName AS [Member Name], C1.PrimaryFamilyMember, (SELECT
Nz(c2.[NickName],c2.[FirstName]) & " " & c2.[LastName] AS [SONm] FROM
Contacts c2 WHERE c2.ContactID = Nz(c1.SignificantOtherID,-9999)) AS
[SO
Name], C1.SignificantOtherID, DuesLineItem.Reference,
DuesLineItem.Amount,
DuesLineItem.DateRemitted, DuesLineItem.DateCreated, C1.LastName,
C1.FirstName, PaymentTypes.Type, DuesItemType.DuesItemType,
tblDuesRates.DuesAmount, (IIf(Len(Trim([SO Name]) & "")=0,Trim([Member
Name]),Trim([Member Name] & " & " & [SO Name]))) AS JointName
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
WHERE PrimaryFamilyMember <> 0;

Then, I suppose I need to get those records where the
PrimaryFamilyMember
is
No, but in the JointName order that I want. This statement gave me
fits:
SELECT [Member Name], [SO Name], 0
What is it suppose to do?

Last, I'll need to get the members without member spouses
(SignificantOtherID null) into the mix.

Thanks for the help!




:

Dear Stephanie:

You want the names in a specific order. Is that always husband, then
wife?

Is the data structured to ensure you can tell which is which? The
SignificantOtherID column, do both the husband and wife have that,
linked
both ways?

To see them together, you would need to sort on something like this,
if
possible:

Member Name Base Name PrimaryFamilyMember
John Doe John Doe -1 (yes)
Susie Anderson John Doe 0 (no)

From the query you showed, try this:

SELECT [Member Name], [Member Name] AS BaseName,
PrimaryFamilyMember
FROM YourQuery
WHERE PrimaryFamilyMember <> 0
UNION ALL
SELECT [Member Name], [SO Name], 0
FROM YourQuery
WHERE [SO Name] <> -9999

Then, select from the UnionQuery (or name it something else):

SELECT *
FROM UnionQuery
ORDER BY BaseName, PrimaryFamilyMember

That should put them together the way you want. However, wouldn't it
be
possible to have accidentally made both of them Primary? That would
screw
up the order, and I think would duplicate them. Wouldn't it be fun to
write
a query to detect those?

I think that having the BaseName in the table would be useful. It
might
be
less complex in the long run. See what I mean?

Tom Ellison


Hi.
I have a query that's sloppy, and I'd like to revise the name order.
I have members that can be spouses. If there is a member spouse,
I'd
like
their names to appear in a particular order: John Doe and Susie
Anderson
(rather than Susie Anderson and John Doe).
Originally, I was relying on SignificantOtherID to determine order.
But
have
since added a PrimaryFamilyMember field. The spouse with the
PrimaryFamilyMember as Yes should appear first. I'd appreciate any
help
in
streamlining what I have strung together. Thanks!

SELECT Nz(c1.NickName,c1.FirstName) & " " & c1.LastName AS [Member
Name],
C1.PrimaryFamilyMember, (SELECT Nz(c2.[NickName],c2.[FirstName]) & "
"
&
c2.[LastName] AS [SONm] FROM Contacts c2 WHERE c2.ContactID =
Nz(c1.SignificantOtherID,-9999)) AS [SO Name],
C1.SignificantOtherID,
(IIf(Len(Trim([SO Name]) & "")=0,Trim([Member Name]), Trim([Member
Name] &
"
& " & [SO Name]))) AS JointName

 

[Stephanie]

Tom,
I'm so sorry- I didn't know you had a replied. At what a reply it was!
Thanks for the query help- that went a long way to get me where I wanted to
go. Base Name good! Parentheses slay me.

One question though on the query. Where there is a SignificantOtherID, I'd
like to be able to list the Significant Other (that way I can saw John Doe
and Susie Anderson have paid their family dues). However, every time I try
to list the other name as well, I run into the same issues I was having
before the Base Name solved many of the problems.

Here's the pertinent part of the existing query:

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

Is there a way to say if the Base Name is not the same as the Member Name,
list the Base Name and Member Name, else just the Base Name?

I'm still thinking about SignificantOtherID and PrimaryFamilyMember.
SignificantOther is used in many reports to show that the members are a
couple and for mailing labels. So I wouldn't was SignificantOtherID to point
back to a member.
PrimaryFamilyMember is used in displaying "Bucks". I can set the default as
"yes"...

Thanks for the reply- it was very helpful!

Dear Stephanie:

A self-referencing table is not something I'm recommending, it is what you
already have. I'm not trying to get you to change that, but I did recommend
how to protect its integrity.

In order to do the sorting you said you want, you must have the BaseName
column I talked about. That's what sorts the family together. You can do
this in a table, but I'm not currently advocating that. That means you must
derive it in a query. I've showed how to do that. Whether you fix up the
table to protect data integrity or not, the BaseName approach is essential.
However, it will suffer from the types of data inconsistencies I warned
about, and for which I propose you query to see how bad the problem will be.

So, my response is not a recommendation to radically change your table
structure. I did propose to simplify it and then protect it. That's
optional, but it will affect how well the queries work. Simplifying the
table will result in an easier task of protecting the data integrity.
Having complete integrity in the data will mean the query I propose will
work better. The thing about adding BaseName to the actual table, instead
of deriving it, is so you can index on it. That's only a performance issue,
and we can forget that for now. Perhaps I shouldn't have mentioned that so
early on. If it isn't needed, then let's forget it and move on. We can
revisit it later if it becomes important.

I gave this query before to derive the BaseName:

SELECT IIf(PrimaryFamilyNumber,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
FROM Contacts T

I'll fix that slightly now:

SELECT IIf(PrimaryFamilyMember,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
AS BaseName,
PrimaryFamilyMember, NickName, FirstName, LastName,
ContactID, SignificantOtherID
FROM Contacts T

Here, I've changed PrimaryFamilyNumber to PrimaryFamilyMember (I must have
been tired), put an alias for BaseName, and added other columns so you can
see if this is working correctly. If it is, you can put this into your
existing query in the ORDER BY. Then all the family will be together in the
sort order. If you want to use it in a report, since reports do not follow
the sort order of the query, it will need to be a column in the query
results also, so the report can sort by it. Sort also by the
PrimaryFamilyMember so you can get the primary member first within each
family group.

Based on your original request, and ignoring for the moment where the data
is messed up, this will do the whole thing for you. The rest is just about
the problems created where the data is not consistent with what you require.

Tom Ellison

Tom,
I have about 5 couples who are member spouses. SignificantOtherID is null
unless there is a member spouse, and PrimaryFamilyID is null unless there
is
a member spouse. While the self-referencing table concept is interesting,
it's very complicated and I'm not really sure that the added value is
worth
the effort.

At this point, all I really want to do, is have a query that returns the
names in a consistent order for member spouses. I'd perfer the
"PrimaryFamilyID = Yes" spouse to come first, but it doesn't really
matter.
What does matter is that I consistently return: John Doe and Susie
Anderson
and not Susie Anderson and John Doe
So that I can group my records.
Thanks.

Dear Stephanie:

I recognized the first name as being familiar. I certainly remember the
balance forward discussion. But, I hadn't tied the two together. Sorry,
but I answer several questions a day on the average, and it can become a
bit
of a blur.

The additional column for base name could be quite invisible to users.
It
would be derived. As such, it would technically be a violation of the
rules
for normalization, but we sometimes allow for this in designs when there
is
good reason for it, and a controlled way of implementing it. You can
implement it at the query level if you prefer. The difference would be
performance. Since you need to order on BaseName, you'd be building the
index every time you query. For now, let's assume it is derived and not
stored.

So, let's start by deriving it. When the row is a PrimaryFamilyMember,
it's
the name in the current row. When not a PrimaryFamilyMember, it's the
name
in another row designated by the SignificantOtherID. So:

SELECT IIf(PrimaryFamilyNumber,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
FROM Contacts T

This funtions on the basis that, when a row is not a PrimaryFamilyMember,
it
MUST have a SignificantOtherID. It would be a good idea to test this
assumption to see how often this fails in your current data:

SELECT *
FROM Contacts
WHERE NOT PrimaryFamilyMember
AND SignificantOtherID IS NULL

Am I correct that this should not happen? If it is happening a lot, then
I'd like to make a suggestion. You can build the table so this cannot
happen. Here's how I'd suggest doing that.

I'm proposign that you not allow SignificantOtherID to be NULL. When the
row is a PrimaryFamilyMember, put the ContactID in the
SignificantOtherID.
The row then points to itself. That's logically reasonable, isn't it?
It's
saying, "I am the PrimaryFamilyMember in this Family, so the
SignificantOther is ME." In fact, this obviates the need for the
PrimaryFamilyMember column. A PrimaryFamilyMember is a row where
ContactID
= SignificantOtherID.

This design is a "self-referencing table" style. That's probably the
correct way to do this, but it makes for a different set of rules in some
ways. For example, it creates sets of interrelated rows. A rule must be
observed for integrity of this. There should be exactly one
PrimaryFamilyMember in each set. Not more, not less. That member must
be
the only one designated as the PrimaryFamilyMember.

How can you best enforce these rules? Eliminating the
PrimaryFamilyMember
column (eventually, not initially!) will help. That's just one less
thing
to be enforced. The other rule is that every row that is referenced by
another row must be a PrimaryFamilyMember, initally with the
PrimaryFamilyMember column set to True, and eventually with ContactID =
SignificantOtherID.

This second rule can be enforced! During data entry, the
PrimaryFamilyMember row must be put in first, right? Well, there would
be a
combo box of Members in which to select who the SignificantOther is.
This
combo box should be created so it lists ONLY the PrimaryFamilyMembers in
the
table. However, when entering a new member in the table, that row would
not
be listed.

What I would do is to have an unbound check box that appears on the form
only when entering a NewRecord. When this check box is checked, the
combo
box for SignificantOther disappears from the form. The BeforeUpdate
event
should test the check box. If it's checked, then set the combo box
(which
is invisible, but still there) to a value of empty. Have the table
definition set the SignificantOtherID to a default value of the
ContactID.
When the row is added, it will be set up as desired. The user's cannot
screw this up.

If a row is added without the box checked and without selecting a
SignificantOther, it will become a PrimaryFamilyMember, because the
SignificantOtherID will default to the ContactID of itself. The check
box
is a good idea anyway, in case the user accidentally selects some
SignificantOther in the combo box, but means for it to be a
PrimaryFamilyMember. You can even clear the selection on the check box
click event when it changes to True.

Doing all you can do to prevent corruption of the data, even a "logical"
corruption like this, is a very positive step. When integrity is
questionable, a test for integrity should be built and run periodically.

I'm going to stop for now. This isn't finished, but maybe it's enough to
think about for a while. I may not be back to check on this till Sunday.
Enjoy!

Tom Ellison


Tom,
This is really an extension of the balance forward issue that you
helped
me
with!
I'm trying to find a way to report on the dues for the year and the
"associated" payments (those for that same year).

The SignificantOtherID appears on each of the spouse member's record.
The
PrimaryFamilyMember could be the husband or wife. So the JointName
could
be
Susie Anderson (primary) & John Doe.

Users could forget to enter SignificantOtherID on one or both records,
and
forget or duplicate PrimaryFamilyMember. So I don't really see how
having
another field (BaseName) to mess up helps, but I'd appreciate your
enlightenment. As well as help with a query that would let me know if
I
have
these type of issues.

Basically, with the query I have I thought that if I could get the
JointName
in the correct order, I could sort a report by JointName and Year. I
actually like the syntax I have for JointName. But I'm not so good a
series
of queries (short attention span, and all).

The first step is to get the JointName for member spouses where the
PrimaryFamilyMember flag is Yes, in the name order of Primary &
"Secondary".
Here's what I have:
SELECT Year([DateCreated]) AS [Date Year], Nz(c1.NickName,c1.FirstName)
&
"
" & c1.LastName AS [Member Name], C1.PrimaryFamilyMember, (SELECT
Nz(c2.[NickName],c2.[FirstName]) & " " & c2.[LastName] AS [SONm] FROM
Contacts c2 WHERE c2.ContactID = Nz(c1.SignificantOtherID,-9999)) AS
[SO
Name], C1.SignificantOtherID, DuesLineItem.Reference,
DuesLineItem.Amount,
DuesLineItem.DateRemitted, DuesLineItem.DateCreated, C1.LastName,
C1.FirstName, PaymentTypes.Type, DuesItemType.DuesItemType,
tblDuesRates.DuesAmount, (IIf(Len(Trim([SO Name]) & "")=0,Trim([Member
Name]),Trim([Member Name] & " & " & [SO Name]))) AS JointName
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
WHERE PrimaryFamilyMember <> 0;

Then, I suppose I need to get those records where the
PrimaryFamilyMember
is
No, but in the JointName order that I want. This statement gave me
fits:
SELECT [Member Name], [SO Name], 0
What is it suppose to do?

Last, I'll need to get the members without member spouses
(SignificantOtherID null) into the mix.

Thanks for the help!




:

Dear Stephanie:

You want the names in a specific order. Is that always husband, then
wife?

Is the data structured to ensure you can tell which is which? The
SignificantOtherID column, do both the husband and wife have that,
linked
both ways?

To see them together, you would need to sort on something like this,
if
possible:

Member Name Base Name PrimaryFamilyMember
John Doe John Doe -1 (yes)
Susie Anderson John Doe 0 (no)

 

[Tom Ellison]

Dear Stephanie:

The idea of the BaseName is not so much to display this value, but to sort
by it. If you sort by BaseName and PrimaryFamilyID you can produce the
sequence of rows you asked for. If you want to do this in a report, then
you must provide the BaseName value in the SELECT clause, since report
sorting is a function of the report, not of the query.

To get help on this, please post the entire query you have.

More inline below:

Tom Ellison

Tom,
I'm so sorry- I didn't know you had a replied. At what a reply it was!
Thanks for the query help- that went a long way to get me where I wanted
to
go. Base Name good! Parentheses slay me.

One question though on the query. Where there is a SignificantOtherID,
I'd
like to be able to list the Significant Other (that way I can saw John Doe
and Susie Anderson have paid their family dues). However, every time I
try
to list the other name as well, I run into the same issues I was having
before the Base Name solved many of the problems.

Not following you. Example of data produce, please.

Here's the pertinent part of the existing query:

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

Post whole query, please. I'll try to fix it up.

Is there a way to say if the Base Name is not the same as the Member Name,
list the Base Name and Member Name, else just the Base Name?

All rows of query results must have the same columns in the same sequence.
You cannot omit a column in some rows and not others. That's probably not
what you mean, but that's what is sounded like.

I'm still thinking about SignificantOtherID and PrimaryFamilyMember.
SignificantOther is used in many reports to show that the members are a
couple and for mailing labels. So I wouldn't was SignificantOtherID to
point
back to a member.

Sorry, "So I wouldn't was" doesn't make sense. Please ask again.

PrimaryFamilyMember is used in displaying "Bucks". I can set the default
as
"yes"...

I had suggested that when the SignificantOtherID is equal to the ContactID,
then that member is the Primary. You could also say that when the
SignificantOtherID is NULL then that is the Primary. That would make
PrimaryFamilyMember unnecessary. No big deal I expect, as long as they
agree in all cases. It is the possibility they might not agree, and the
difficulty in making sure they do always agree, that makes the
simplification superior. Do you have any rows where PrimaryFamilyMember is
True and SignificantOtherID is not NULL? Run a query to test that. What
does it mean when this happens?

Thanks for the reply- it was very helpful!

Dear Stephanie:

A self-referencing table is not something I'm recommending, it is what
you
already have. I'm not trying to get you to change that, but I did
recommend
how to protect its integrity.

In order to do the sorting you said you want, you must have the BaseName
column I talked about. That's what sorts the family together. You can
do
this in a table, but I'm not currently advocating that. That means you
must
derive it in a query. I've showed how to do that. Whether you fix up
the
table to protect data integrity or not, the BaseName approach is
essential.
However, it will suffer from the types of data inconsistencies I warned
about, and for which I propose you query to see how bad the problem will
be.

So, my response is not a recommendation to radically change your table
structure. I did propose to simplify it and then protect it. That's
optional, but it will affect how well the queries work. Simplifying the
table will result in an easier task of protecting the data integrity.
Having complete integrity in the data will mean the query I propose will
work better. The thing about adding BaseName to the actual table,
instead
of deriving it, is so you can index on it. That's only a performance
issue,
and we can forget that for now. Perhaps I shouldn't have mentioned that
so
early on. If it isn't needed, then let's forget it and move on. We can
revisit it later if it becomes important.

I gave this query before to derive the BaseName:

SELECT IIf(PrimaryFamilyNumber,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
FROM Contacts T

I'll fix that slightly now:

SELECT IIf(PrimaryFamilyMember,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
AS BaseName,
PrimaryFamilyMember, NickName, FirstName, LastName,
ContactID, SignificantOtherID
FROM Contacts T

Here, I've changed PrimaryFamilyNumber to PrimaryFamilyMember (I must
have
been tired), put an alias for BaseName, and added other columns so you
can
see if this is working correctly. If it is, you can put this into your
existing query in the ORDER BY. Then all the family will be together in
the
sort order. If you want to use it in a report, since reports do not
follow
the sort order of the query, it will need to be a column in the query
results also, so the report can sort by it. Sort also by the
PrimaryFamilyMember so you can get the primary member first within each
family group.

Based on your original request, and ignoring for the moment where the
data
is messed up, this will do the whole thing for you. The rest is just
about
the problems created where the data is not consistent with what you
require.

Tom Ellison

Tom,
I have about 5 couples who are member spouses. SignificantOtherID is
null
unless there is a member spouse, and PrimaryFamilyID is null unless
there
is
a member spouse. While the self-referencing table concept is
interesting,
it's very complicated and I'm not really sure that the added value is
worth
the effort.

At this point, all I really want to do, is have a query that returns
the
names in a consistent order for member spouses. I'd perfer the
"PrimaryFamilyID = Yes" spouse to come first, but it doesn't really
matter.
What does matter is that I consistently return: John Doe and Susie
Anderson
and not Susie Anderson and John Doe
So that I can group my records.
Thanks.


:

Dear Stephanie:

I recognized the first name as being familiar. I certainly remember
the
balance forward discussion. But, I hadn't tied the two together.
Sorry,
but I answer several questions a day on the average, and it can become
a
bit
of a blur.

The additional column for base name could be quite invisible to users.
It
would be derived. As such, it would technically be a violation of the
rules
for normalization, but we sometimes allow for this in designs when
there
is
good reason for it, and a controlled way of implementing it. You can
implement it at the query level if you prefer. The difference would
be
performance. Since you need to order on BaseName, you'd be building
the
index every time you query. For now, let's assume it is derived and
not
stored.

So, let's start by deriving it. When the row is a
PrimaryFamilyMember,
it's
the name in the current row. When not a PrimaryFamilyMember, it's the
name
in another row designated by the SignificantOtherID. So:

SELECT IIf(PrimaryFamilyNumber,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
FROM Contacts T

This funtions on the basis that, when a row is not a
PrimaryFamilyMember,
it
MUST have a SignificantOtherID. It would be a good idea to test this
assumption to see how often this fails in your current data:

SELECT *
FROM Contacts
WHERE NOT PrimaryFamilyMember
AND SignificantOtherID IS NULL

Am I correct that this should not happen? If it is happening a lot,
then
I'd like to make a suggestion. You can build the table so this cannot
happen. Here's how I'd suggest doing that.

I'm proposign that you not allow SignificantOtherID to be NULL. When
the
row is a PrimaryFamilyMember, put the ContactID in the
SignificantOtherID.
The row then points to itself. That's logically reasonable, isn't it?
It's
saying, "I am the PrimaryFamilyMember in this Family, so the
SignificantOther is ME." In fact, this obviates the need for the
PrimaryFamilyMember column. A PrimaryFamilyMember is a row where
ContactID
= SignificantOtherID.

This design is a "self-referencing table" style. That's probably the
correct way to do this, but it makes for a different set of rules in
some
ways. For example, it creates sets of interrelated rows. A rule must
be
observed for integrity of this. There should be exactly one
PrimaryFamilyMember in each set. Not more, not less. That member
must
be
the only one designated as the PrimaryFamilyMember.

How can you best enforce these rules? Eliminating the
PrimaryFamilyMember
column (eventually, not initially!) will help. That's just one less
thing
to be enforced. The other rule is that every row that is referenced
by
another row must be a PrimaryFamilyMember, initally with the
PrimaryFamilyMember column set to True, and eventually with ContactID
=
SignificantOtherID.

This second rule can be enforced! During data entry, the
PrimaryFamilyMember row must be put in first, right? Well, there
would
be a
combo box of Members in which to select who the SignificantOther is.
This
combo box should be created so it lists ONLY the PrimaryFamilyMembers
in
the
table. However, when entering a new member in the table, that row
would
not
be listed.

What I would do is to have an unbound check box that appears on the
form
only when entering a NewRecord. When this check box is checked, the
combo
box for SignificantOther disappears from the form. The BeforeUpdate
event
should test the check box. If it's checked, then set the combo box
(which
is invisible, but still there) to a value of empty. Have the table
definition set the SignificantOtherID to a default value of the
ContactID.
When the row is added, it will be set up as desired. The user's
cannot
screw this up.

If a row is added without the box checked and without selecting a
SignificantOther, it will become a PrimaryFamilyMember, because the
SignificantOtherID will default to the ContactID of itself. The check
box
is a good idea anyway, in case the user accidentally selects some
SignificantOther in the combo box, but means for it to be a
PrimaryFamilyMember. You can even clear the selection on the check
box
click event when it changes to True.

Doing all you can do to prevent corruption of the data, even a
"logical"
corruption like this, is a very positive step. When integrity is
questionable, a test for integrity should be built and run
periodically.

I'm going to stop for now. This isn't finished, but maybe it's enough
to
think about for a while. I may not be back to check on this till
Sunday.
Enjoy!

Tom Ellison


Tom,
This is really an extension of the balance forward issue that you
helped
me
with!
I'm trying to find a way to report on the dues for the year and the
"associated" payments (those for that same year).

The SignificantOtherID appears on each of the spouse member's
record.
The
PrimaryFamilyMember could be the husband or wife. So the JointName
could
be
Susie Anderson (primary) & John Doe.

Users could forget to enter SignificantOtherID on one or both
records,
and
forget or duplicate PrimaryFamilyMember. So I don't really see how
having
another field (BaseName) to mess up helps, but I'd appreciate your
enlightenment. As well as help with a query that would let me know
if
I
have
these type of issues.

Basically, with the query I have I thought that if I could get the
JointName
in the correct order, I could sort a report by JointName and Year.
I
actually like the syntax I have for JointName. But I'm not so good
a
series
of queries (short attention span, and all).

The first step is to get the JointName for member spouses where the
PrimaryFamilyMember flag is Yes, in the name order of Primary &
"Secondary".
Here's what I have:
SELECT Year([DateCreated]) AS [Date Year],
Nz(c1.NickName,c1.FirstName)
&
"
" & c1.LastName AS [Member Name], C1.PrimaryFamilyMember, (SELECT
Nz(c2.[NickName],c2.[FirstName]) & " " & c2.[LastName] AS [SONm]
FROM
Contacts c2 WHERE c2.ContactID = Nz(c1.SignificantOtherID,-9999)) AS
[SO
Name], C1.SignificantOtherID, DuesLineItem.Reference,
DuesLineItem.Amount,
DuesLineItem.DateRemitted, DuesLineItem.DateCreated, C1.LastName,
C1.FirstName, PaymentTypes.Type, DuesItemType.DuesItemType,
tblDuesRates.DuesAmount, (IIf(Len(Trim([SO Name]) &
"")=0,Trim([Member
Name]),Trim([Member Name] & " & " & [SO Name]))) AS JointName
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
WHERE PrimaryFamilyMember <> 0;

Then, I suppose I need to get those records where the
PrimaryFamilyMember
is
No, but in the JointName order that I want. This statement gave me
fits:
SELECT [Member Name], [SO Name], 0
What is it suppose to do?

Last, I'll need to get the members without member spouses
(SignificantOtherID null) into the mix.

Thanks for the help!




:

Dear Stephanie:

You want the names in a specific order. Is that always husband,
then
wife?

Is the data structured to ensure you can tell which is which? The
SignificantOtherID column, do both the husband and wife have that,
linked
both ways?

To see them together, you would need to sort on something like
this,
if
possible:

Member Name Base Name PrimaryFamilyMember
John Doe John Doe -1 (yes)
Susie Anderson John Doe 0 (no)

 

[Stephanie]

Tom,
Thanks for the reply.
I am actually using BaseName to display a value and am having the user use a
parameter form to select BaseName. The issue is that I have BaseName John
Doe but then can't also indicate that the dues are for John Doe and Susie
Anderson.

Originally I wanted to sort by BaseName and then Year, but keep getting this
error message: Multi-level GROUP BY clause is not allowed in a subquery.

Here's the (entire) query I'm working with now:
SELECT Year([DateCreated]) AS [Date Year],
IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID =
c1.SignificantOtherID)) AS [Base Name], C1.PrimaryFamilyMember, C1.FirstName,
C1.LastName, C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount, DuesLineItem.DateRemitted,
DuesLineItem.DateCreated, PaymentTypes.Type, DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes INNER JOIN (DuesItemType INNER JOIN (Contacts AS C1 INNER
JOIN (DuesLineItem INNER JOIN Dues ON DuesLineItem.DuesRateID =
Dues.DuesRateID) ON C1.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) ON
PaymentTypes.TypeID = DuesLineItem.TypeID
WHERE (((IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID =
c1.SignificantOtherID)))=[Forms]![MemberParam]![FindMember]))
ORDER BY Year([DateCreated]) DESC;

I'll simply my statement about listing the Significant Other (which is also
what I was trying to say regarding Base Member and Member Name. I can't get
it to work. I want to show: dues paid by John Doe and Susie Anderson; dues
paid by Peter Morgan (a "non-family" member...

SignificantOtherID is used for address labels and reports to show that there
are members who are couples. I wouldn't want SignificantOtherID to point
back to a member. Then the labels would read: Peter Morgan and Peter Morgan.

"You could also say that when the SignificantOtherID is NULL then that is
the Primary" That's not too bad. In fact, it's rather brilliant! That
won't mess up any of my reports. So the only issue is with the query I
listed above.

I can change to: IIf([SignificantOtherID]<>0,[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName])
But that make Susie Anderson the BaseName rather than John Doe. So, I'd
appreciate your help in revamping the query to get the correct BaseName, not
using PrimaryFamilyMember but rather using SignificantOtherID, and being able
to list both members is there is a SignificantOtherID involved. Thanks!

Dear Stephanie:

The idea of the BaseName is not so much to display this value, but to sort
by it. If you sort by BaseName and PrimaryFamilyID you can produce the
sequence of rows you asked for. If you want to do this in a report, then
you must provide the BaseName value in the SELECT clause, since report
sorting is a function of the report, not of the query.

To get help on this, please post the entire query you have.

More inline below:

Tom Ellison

Tom,
I'm so sorry- I didn't know you had a replied. At what a reply it was!
Thanks for the query help- that went a long way to get me where I wanted
to
go. Base Name good! Parentheses slay me.

One question though on the query. Where there is a SignificantOtherID,
I'd
like to be able to list the Significant Other (that way I can saw John Doe
and Susie Anderson have paid their family dues). However, every time I
try
to list the other name as well, I run into the same issues I was having
before the Base Name solved many of the problems.

Not following you. Example of data produce, please.

Here's the pertinent part of the existing query:

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

Post whole query, please. I'll try to fix it up.

Is there a way to say if the Base Name is not the same as the Member Name,
list the Base Name and Member Name, else just the Base Name?

All rows of query results must have the same columns in the same sequence.
You cannot omit a column in some rows and not others. That's probably not
what you mean, but that's what is sounded like.

I'm still thinking about SignificantOtherID and PrimaryFamilyMember.
SignificantOther is used in many reports to show that the members are a
couple and for mailing labels. So I wouldn't was SignificantOtherID to
point
back to a member.

Sorry, "So I wouldn't was" doesn't make sense. Please ask again.

PrimaryFamilyMember is used in displaying "Bucks". I can set the default
as
"yes"...

I had suggested that when the SignificantOtherID is equal to the ContactID,
then that member is the Primary. You could also say that when the
SignificantOtherID is NULL then that is the Primary. That would make
PrimaryFamilyMember unnecessary. No big deal I expect, as long as they
agree in all cases. It is the possibility they might not agree, and the
difficulty in making sure they do always agree, that makes the
simplification superior. Do you have any rows where PrimaryFamilyMember is
True and SignificantOtherID is not NULL? Run a query to test that. What
does it mean when this happens?

Thanks for the reply- it was very helpful!

 

[Tom Ellison]

Dear Stephanie:

Below, I have reformatted your query for my personal readability preferences
so I can study it more easily.

The lines where I have placed a * at the beginning have recommended changes.
Replace the * with 2 spaces, please.

SELECT Year(DateCreated) AS [Date Year],
* IIf(PrimaryFamilyMember 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 [Base Name],
C1.PrimaryFamilyMember, C1.FirstName, C1.LastName,
C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount,
DuesLineItem.DateRemitted, DuesLineItem.DateCreated,
PaymentTypes.Type, DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes
INNER JOIN (DuesItemType
INNER JOIN (Contacts AS C1
INNER JOIN (DuesLineItem
INNER JOIN Dues
ON DuesLineItem.DuesRateID = Dues.DuesRateID)
ON C1.ContactID = DuesLineItem.ContactID)
ON DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID)
ON PaymentTypes.TypeID = DuesLineItem.TypeID
*WHERE (((IIf(PrimaryFamilyMember] 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))) =
[Forms]![MemberParam]![FindMember]))
ORDER BY Year(DateCreated) DESC;

Since the BaseName is a separate, and possibly resuable functionality, I
recommend making a separate query just to do that:

SELECT *,
IIf(PrimaryFamilyMember 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 [Base Name]
FROM Contacts c1

Save this query and use it instead of Contacts in further work. It may
really simplify much of the work and eliminate difficulties you're having.
As it is, you have need of this functionality 3 times in your query, once to
display it, once to filter, and also potentially to sort.

More inline below:

Tom,
Thanks for the reply.
I am actually using BaseName to display a value and am having the user use
a
parameter form to select BaseName. The issue is that I have BaseName John
Doe but then can't also indicate that the dues are for John Doe and Susie
Anderson.

How many names might be associated with an account? I would think only one,
but you could also list the others in that family. How many could there be?
No limit I would think. So, how do you want to handle it when there are a
large number? Will you be printing some kind of statement on a form? Will
you then allow as many "continuation pages" as are necessary to hold the
entire list of names?

Originally I wanted to sort by BaseName and then Year, but keep getting
this
error message: Multi-level GROUP BY clause is not allowed in a subquery.

You didn't post the query at this point. I can't tell how to fix it.

Here's the (entire) query I'm working with now:
SELECT Year([DateCreated]) AS [Date Year],
IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID =
c1.SignificantOtherID)) AS [Base Name], C1.PrimaryFamilyMember,
C1.FirstName,
C1.LastName, C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount, DuesLineItem.DateRemitted,
DuesLineItem.DateCreated, PaymentTypes.Type, DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes INNER JOIN (DuesItemType INNER JOIN (Contacts AS C1
INNER
JOIN (DuesLineItem INNER JOIN Dues ON DuesLineItem.DuesRateID =
Dues.DuesRateID) ON C1.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) ON
PaymentTypes.TypeID = DuesLineItem.TypeID
WHERE (((IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID =
c1.SignificantOtherID)))=[Forms]![MemberParam]![FindMember]))
ORDER BY Year([DateCreated]) DESC;

I'll simply my statement about listing the Significant Other (which is
also
what I was trying to say regarding Base Member and Member Name. I can't
get
it to work. I want to show: dues paid by John Doe and Susie Anderson;
dues
paid by Peter Morgan (a "non-family" member...

The above query is filtered by the FindMember (combo box?). Does it not
work for Peter Morgan? Does it not work for John and Susie? How does it
"not work?"

SignificantOtherID is used for address labels and reports to show that
there
are members who are couples. I wouldn't want SignificantOtherID to point
back to a member. Then the labels would read: Peter Morgan and Peter
Morgan.

They would not do this if you filtered or tested if ContactID =
SignificantOtherID. I won't press this, however. Proabably it doesn't
matter, except for the integrity of the data.

"You could also say that when the SignificantOtherID is NULL then that is
the Primary" That's not too bad. In fact, it's rather brilliant! That
won't mess up any of my reports. So the only issue is with the query I
listed above.

Yes, that's good, and you can maintain referential integrity on that basis
as well.

I can change to: IIf([SignificantOtherID]<>0,[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName])
But that make Susie Anderson the BaseName rather than John Doe. So, I'd
appreciate your help in revamping the query to get the correct BaseName,
not
using PrimaryFamilyMember but rather using SignificantOtherID, and being
able
to list both members is there is a SignificantOtherID involved. Thanks!

Dear Stephanie:

The idea of the BaseName is not so much to display this value, but to
sort
by it. If you sort by BaseName and PrimaryFamilyID you can produce the
sequence of rows you asked for. If you want to do this in a report, then
you must provide the BaseName value in the SELECT clause, since report
sorting is a function of the report, not of the query.

To get help on this, please post the entire query you have.

More inline below:

Tom Ellison

Tom,
I'm so sorry- I didn't know you had a replied. At what a reply it was!
Thanks for the query help- that went a long way to get me where I
wanted
to
go. Base Name good! Parentheses slay me.

One question though on the query. Where there is a SignificantOtherID,
I'd
like to be able to list the Significant Other (that way I can saw John
Doe
and Susie Anderson have paid their family dues). However, every time I
try
to list the other name as well, I run into the same issues I was having
before the Base Name solved many of the problems.

Not following you. Example of data produce, please.

Here's the pertinent part of the existing query:

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

Post whole query, please. I'll try to fix it up.

Is there a way to say if the Base Name is not the same as the Member
Name,
list the Base Name and Member Name, else just the Base Name?

All rows of query results must have the same columns in the same
sequence.
You cannot omit a column in some rows and not others. That's probably
not
what you mean, but that's what is sounded like.

I'm still thinking about SignificantOtherID and PrimaryFamilyMember.
SignificantOther is used in many reports to show that the members are a
couple and for mailing labels. So I wouldn't was SignificantOtherID to
point
back to a member.

Sorry, "So I wouldn't was" doesn't make sense. Please ask again.

PrimaryFamilyMember is used in displaying "Bucks". I can set the
default
as
"yes"...

I had suggested that when the SignificantOtherID is equal to the
ContactID,
then that member is the Primary. You could also say that when the
SignificantOtherID is NULL then that is the Primary. That would make
PrimaryFamilyMember unnecessary. No big deal I expect, as long as they
agree in all cases. It is the possibility they might not agree, and the
difficulty in making sure they do always agree, that makes the
simplification superior. Do you have any rows where PrimaryFamilyMember
is
True and SignificantOtherID is not NULL? Run a query to test that. What
does it mean when this happens?

Thanks for the reply- it was very helpful!



:

Dear Stephanie:

A self-referencing table is not something I'm recommending, it is what
you
already have. I'm not trying to get you to change that, but I did
recommend
how to protect its integrity.

In order to do the sorting you said you want, you must have the
BaseName
column I talked about. That's what sorts the family together. You
can
do
this in a table, but I'm not currently advocating that. That means
you
must
derive it in a query. I've showed how to do that. Whether you fix up
the
table to protect data integrity or not, the BaseName approach is
essential.
However, it will suffer from the types of data inconsistencies I
warned
about, and for which I propose you query to see how bad the problem
will
be.

So, my response is not a recommendation to radically change your table
structure. I did propose to simplify it and then protect it. That's
optional, but it will affect how well the queries work. Simplifying
the
table will result in an easier task of protecting the data integrity.
Having complete integrity in the data will mean the query I propose
will
work better. The thing about adding BaseName to the actual table,
instead
of deriving it, is so you can index on it. That's only a performance
issue,
and we can forget that for now. Perhaps I shouldn't have mentioned
that
so
early on. If it isn't needed, then let's forget it and move on. We
can
revisit it later if it becomes important.

I gave this query before to derive the BaseName:

SELECT IIf(PrimaryFamilyNumber,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
FROM Contacts T

I'll fix that slightly now:

SELECT IIf(PrimaryFamilyMember,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
AS BaseName,
PrimaryFamilyMember, NickName, FirstName, LastName,
ContactID, SignificantOtherID
FROM Contacts T

Here, I've changed PrimaryFamilyNumber to PrimaryFamilyMember (I must
have
been tired), put an alias for BaseName, and added other columns so you
can
see if this is working correctly. If it is, you can put this into
your
existing query in the ORDER BY. Then all the family will be together
in
the
sort order. If you want to use it in a report, since reports do not
follow
the sort order of the query, it will need to be a column in the query
results also, so the report can sort by it. Sort also by the
PrimaryFamilyMember so you can get the primary member first within
each
family group.

Based on your original request, and ignoring for the moment where the
data
is messed up, this will do the whole thing for you. The rest is just
about
the problems created where the data is not consistent with what you
require.

Tom Ellison


Tom,
I have about 5 couples who are member spouses. SignificantOtherID
is
null
unless there is a member spouse, and PrimaryFamilyID is null unless
there
is
a member spouse. While the self-referencing table concept is
interesting,
it's very complicated and I'm not really sure that the added value
is
worth
the effort.

At this point, all I really want to do, is have a query that returns
the
names in a consistent order for member spouses. I'd perfer the
"PrimaryFamilyID = Yes" spouse to come first, but it doesn't really
matter.
What does matter is that I consistently return: John Doe and Susie
Anderson
and not Susie Anderson and John Doe
So that I can group my records.
Thanks.


:

Dear Stephanie:

I recognized the first name as being familiar. I certainly
remember
the
balance forward discussion. But, I hadn't tied the two together.
Sorry,
but I answer several questions a day on the average, and it can
become
a
bit
of a blur.

The additional column for base name could be quite invisible to
users.
It
would be derived. As such, it would technically be a violation of
the
rules
for normalization, but we sometimes allow for this in designs when
there
is
good reason for it, and a controlled way of implementing it. You
can
implement it at the query level if you prefer. The difference
would
be
performance. Since you need to order on BaseName, you'd be
building
the
index every time you query. For now, let's assume it is derived
and
not
stored.

So, let's start by deriving it. When the row is a
PrimaryFamilyMember,
it's
the name in the current row. When not a PrimaryFamilyMember, it's
the
name
in another row designated by the SignificantOtherID. So:

SELECT IIf(PrimaryFamilyNumber,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
FROM Contacts T

This funtions on the basis that, when a row is not a
PrimaryFamilyMember,
it
MUST have a SignificantOtherID. It would be a good idea to test
this
assumption to see how often this fails in your current data:

SELECT *
FROM Contacts
WHERE NOT PrimaryFamilyMember
AND SignificantOtherID IS NULL

Am I correct that this should not happen? If it is happening a
lot,
then
I'd like to make a suggestion. You can build the table so this
cannot
happen. Here's how I'd suggest doing that.

I'm proposign that you not allow SignificantOtherID to be NULL.
When
the
row is a PrimaryFamilyMember, put the ContactID in the
SignificantOtherID.
The row then points to itself. That's logically reasonable, isn't
it?
It's
saying, "I am the PrimaryFamilyMember in this Family, so the
SignificantOther is ME." In fact, this obviates the need for the
PrimaryFamilyMember column. A PrimaryFamilyMember is a row where
ContactID
= SignificantOtherID.

This design is a "self-referencing table" style. That's probably
the
correct way to do this, but it makes for a different set of rules
in
some
ways. For example, it creates sets of interrelated rows. A rule
must
be
observed for integrity of this. There should be exactly one
PrimaryFamilyMember in each set. Not more, not less. That member
must
be
the only one designated as the PrimaryFamilyMember.

How can you best enforce these rules? Eliminating the
PrimaryFamilyMember
column (eventually, not initially!) will help. That's just one
less
thing
to be enforced. The other rule is that every row that is
referenced
by
another row must be a PrimaryFamilyMember, initally with the
PrimaryFamilyMember column set to True, and eventually with
ContactID
=
SignificantOtherID.

This second rule can be enforced! During data entry, the
PrimaryFamilyMember row must be put in first, right? Well, there
would
be a
combo box of Members in which to select who the SignificantOther
is.
This
combo box should be created so it lists ONLY the
PrimaryFamilyMembers
in
the
table. However, when entering a new member in the table, that row
would
not
be listed.

What I would do is to have an unbound check box that appears on the
form
only when entering a NewRecord. When this check box is checked,
the
combo

 

[Stephanie]

Tom,
I'm so confused and I'm sorry to be so slow in picking up what you are
trying to do.

I'm not happy with the BaseName separate query. It returns a BaseName only
for those who have a SignificantOtherID. However, I still need to report on
members who have individual memberships. This is more what I think I want:

SELECT IIf(PrimaryFamilyMember,Nz(NickName,FirstName) & " " &
LastName,(SELECT Nz(c2.[NickName], c2.[FirstName]) & " " & c2.[LastName]
FROM Contacts c2
WHERE c2.ContactID = c1.SignificantOtherID)) AS [Base Name], c1.Nickname,
c1.FirstName, c1.LastName, c1.ContactID, c1.SignificantOtherID
FROM Contacts AS c1;

This gives me John Doe with BaseName = John Doe;
Susie Anderson with BaseName = John Doe;
Peter Morgan (an individual membership) with BaseName = Peter Morgan.

Two issues- 1) I'm tyring to get rid of PrimaryFamilyMember and use
SignificantOtherID instead. That means that I need to have a 2-step query
(maybe) to find members with SignificantOtherID and those without a
SignificantOtherID so that I can report on members with both family
membership and individual memberships. I'm afraid I'll need help if you have
the time.
And 2) no, I'll wait to ask until the query is OK.

The "big" query you posted isn't quite doing the trick. When I select Doe,
John from my input form, I get back the one record attributable to Susie
Anderson listing Doe, John as the BaseName. When I select Anderson, Susie I
get back all of John Doe's records listing Anderson, Susie as the BaseName.
I want all of these records together listing one or the other as the
BaseName. If I use PrimaryFamilyMember, it would be the member = "yes" as
the BaseName. If I make the switch to SignificantOtherID, I'm not sure who I
would say who should be listed first- but in any case, the listing order
should be consistent and bring back ALL of the associated records for John
Doe and Susie Anderson.
The "big" query also doesn't handle members without a SignificantOtherID so
none of my individual membership people appear in the results.

You're exactly correct about SignificantOtherID- I only have the capablility
to have 2 members associated. And yes, there may be more people in the
family that participate. It seems that would only happen in less than a
handful of situations and the work didn't seem worth the effort. Feel free
to help me change my mind.

As far as the sorting issue, I'll ask again when the query is OK.

As far as the "not work" issue. Simply, I don't want a member to also be
their own significant other. I'm not sure how that would affect the
integrity of the data.

Here's the data:
John Doe and Susie Anderson have a family membership for $100.
They pay by check for $85.
John uses $10 "bucks", Susie uses $5 "bucks"

When I run the report, I want it to say:
John Doe and Susie Anderson Amount Due: $100
Amount Paid:
$85 check
$10 "bucks"
$5 "bucks" (I think I want the 2 "bucks" entries separate).

So I need John Doe's and Susie Anderson's data merged together....
Thanks!

Dear Stephanie:

Below, I have reformatted your query for my personal readability preferences
so I can study it more easily.

The lines where I have placed a * at the beginning have recommended changes.
Replace the * with 2 spaces, please.

SELECT Year(DateCreated) AS [Date Year],
* IIf(PrimaryFamilyMember 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 [Base Name],
C1.PrimaryFamilyMember, C1.FirstName, C1.LastName,
C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount,
DuesLineItem.DateRemitted, DuesLineItem.DateCreated,
PaymentTypes.Type, DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes
INNER JOIN (DuesItemType
INNER JOIN (Contacts AS C1
INNER JOIN (DuesLineItem
INNER JOIN Dues
ON DuesLineItem.DuesRateID = Dues.DuesRateID)
ON C1.ContactID = DuesLineItem.ContactID)
ON DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID)
ON PaymentTypes.TypeID = DuesLineItem.TypeID
*WHERE (((IIf(PrimaryFamilyMember] 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))) =
[Forms]![MemberParam]![FindMember]))
ORDER BY Year(DateCreated) DESC;

Since the BaseName is a separate, and possibly resuable functionality, I
recommend making a separate query just to do that:

SELECT *,
IIf(PrimaryFamilyMember 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 [Base Name]
FROM Contacts c1

Save this query and use it instead of Contacts in further work. It may
really simplify much of the work and eliminate difficulties you're having.
As it is, you have need of this functionality 3 times in your query, once to
display it, once to filter, and also potentially to sort.

More inline below:

Tom,
Thanks for the reply.
I am actually using BaseName to display a value and am having the user use
a
parameter form to select BaseName. The issue is that I have BaseName John
Doe but then can't also indicate that the dues are for John Doe and Susie
Anderson.

How many names might be associated with an account? I would think only one,
but you could also list the others in that family. How many could there be?
No limit I would think. So, how do you want to handle it when there are a
large number? Will you be printing some kind of statement on a form? Will
you then allow as many "continuation pages" as are necessary to hold the
entire list of names?

Originally I wanted to sort by BaseName and then Year, but keep getting
this
error message: Multi-level GROUP BY clause is not allowed in a subquery.

You didn't post the query at this point. I can't tell how to fix it.

Here's the (entire) query I'm working with now:
SELECT Year([DateCreated]) AS [Date Year],
IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID =
c1.SignificantOtherID)) AS [Base Name], C1.PrimaryFamilyMember,
C1.FirstName,
C1.LastName, C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount, DuesLineItem.DateRemitted,
DuesLineItem.DateCreated, PaymentTypes.Type, DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes INNER JOIN (DuesItemType INNER JOIN (Contacts AS C1
INNER
JOIN (DuesLineItem INNER JOIN Dues ON DuesLineItem.DuesRateID =
Dues.DuesRateID) ON C1.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) ON
PaymentTypes.TypeID = DuesLineItem.TypeID
WHERE (((IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID =
c1.SignificantOtherID)))=[Forms]![MemberParam]![FindMember]))
ORDER BY Year([DateCreated]) DESC;

I'll simply my statement about listing the Significant Other (which is
also
what I was trying to say regarding Base Member and Member Name. I can't
get
it to work. I want to show: dues paid by John Doe and Susie Anderson;
dues
paid by Peter Morgan (a "non-family" member...

The above query is filtered by the FindMember (combo box?). Does it not
work for Peter Morgan? Does it not work for John and Susie? How does it
"not work?"

SignificantOtherID is used for address labels and reports to show that
there
are members who are couples. I wouldn't want SignificantOtherID to point
back to a member. Then the labels would read: Peter Morgan and Peter
Morgan.

They would not do this if you filtered or tested if ContactID =
SignificantOtherID. I won't press this, however. Proabably it doesn't
matter, except for the integrity of the data.

"You could also say that when the SignificantOtherID is NULL then that is
the Primary" That's not too bad. In fact, it's rather brilliant! That
won't mess up any of my reports. So the only issue is with the query I
listed above.

Yes, that's good, and you can maintain referential integrity on that basis
as well.

I can change to: IIf([SignificantOtherID]<>0,[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName])
But that make Susie Anderson the BaseName rather than John Doe. So, I'd
appreciate your help in revamping the query to get the correct BaseName,
not
using PrimaryFamilyMember but rather using SignificantOtherID, and being
able
to list both members is there is a SignificantOtherID involved. Thanks!

Dear Stephanie:

The idea of the BaseName is not so much to display this value, but to
sort
by it. If you sort by BaseName and PrimaryFamilyID you can produce the
sequence of rows you asked for. If you want to do this in a report, then
you must provide the BaseName value in the SELECT clause, since report
sorting is a function of the report, not of the query.

To get help on this, please post the entire query you have.

More inline below:

Tom Ellison


Tom,
I'm so sorry- I didn't know you had a replied. At what a reply it was!
Thanks for the query help- that went a long way to get me where I
wanted
to
go. Base Name good! Parentheses slay me.

One question though on the query. Where there is a SignificantOtherID,
I'd
like to be able to list the Significant Other (that way I can saw John
Doe
and Susie Anderson have paid their family dues). However, every time I
try
to list the other name as well, I run into the same issues I was having
before the Base Name solved many of the problems.

Not following you. Example of data produce, please.


Here's the pertinent part of the existing query:

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

Post whole query, please. I'll try to fix it up.


Is there a way to say if the Base Name is not the same as the Member
Name,
list the Base Name and Member Name, else just the Base Name?

All rows of query results must have the same columns in the same
sequence.
You cannot omit a column in some rows and not others. That's probably
not
what you mean, but that's what is sounded like.


I'm still thinking about SignificantOtherID and PrimaryFamilyMember.
SignificantOther is used in many reports to show that the members are a
couple and for mailing labels. So I wouldn't was SignificantOtherID to
point
back to a member.

Sorry, "So I wouldn't was" doesn't make sense. Please ask again.

PrimaryFamilyMember is used in displaying "Bucks". I can set the
default
as
"yes"...

I had suggested that when the SignificantOtherID is equal to the
ContactID,
then that member is the Primary. You could also say that when the
SignificantOtherID is NULL then that is the Primary. That would make
PrimaryFamilyMember unnecessary. No big deal I expect, as long as they
agree in all cases. It is the possibility they might not agree, and the
difficulty in making sure they do always agree, that makes the
simplification superior. Do you have any rows where PrimaryFamilyMember
is
True and SignificantOtherID is not NULL? Run a query to test that. What
does it mean when this happens?


Thanks for the reply- it was very helpful!



:

Dear Stephanie:

A self-referencing table is not something I'm recommending, it is what
you
already have. I'm not trying to get you to change that, but I did
recommend
how to protect its integrity.

In order to do the sorting you said you want, you must have the
BaseName
column I talked about. That's what sorts the family together. You
can
do
this in a table, but I'm not currently advocating that. That means
you
must
derive it in a query. I've showed how to do that. Whether you fix up
the
table to protect data integrity or not, the BaseName approach is
essential.
However, it will suffer from the types of data inconsistencies I
warned
about, and for which I propose you query to see how bad the problem
will
be.

So, my response is not a recommendation to radically change your table
structure. I did propose to simplify it and then protect it. That's
optional, but it will affect how well the queries work. Simplifying
the
table will result in an easier task of protecting the data integrity.
Having complete integrity in the data will mean the query I propose
will
work better. The thing about adding BaseName to the actual table,
instead
of deriving it, is so you can index on it. That's only a performance
issue,
and we can forget that for now. Perhaps I shouldn't have mentioned
that
so
early on. If it isn't needed, then let's forget it and move on. We
can
revisit it later if it becomes important.

I gave this query before to derive the BaseName:

SELECT IIf(PrimaryFamilyNumber,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
FROM Contacts T

I'll fix that slightly now:

SELECT IIf(PrimaryFamilyMember,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
AS BaseName,

 

[Tom Ellison]

Dear Stephanie:

The query I posted should have used SignificantOtherID instead of
PrimaryFamilyMember.

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 [Base Name]
FROM Contacts c1

Sorry. I mean to get away from using PrimaryFamilyMember. If your
convention is to have SignificantOtherID be NULL whenever
PrimaryFamilyMember is true, then this test is the same. However, I'm not
sure how you constrain PrimaryFamilyMember to always be false when
SignificantOtherID is not NULL, or how you constrain PrimaryFamilyMember to
be always true when SignificantOtherID is NULL.

The problem is that, whenever PrimaryFamilyMember is false, the query will
always try to use SignificantOtherID to find the BaseName from the person
who is the Primary. If the SignificantOtherID is missing (NULL) in this
case, then there is no BaseName available. On the other hand, when
SignificantOtherID is not NULL and PrimaryFamilyMember is true, what does
that mean? What does it do? This also is nonsense. That's why I suggest
you not even have PrimaryFamilyMember. The information it contains must
agree with SignificantOtherID or your database is logically corrupt. By
having only a single factor that determines whether someone is primary or
not you can ensure consistency. So, as a step in that direction, I strongly
recommend you start by eliminating the use of PrimaryFamilyMember, and just
test SignificantOtherID.

The other, more difficult issue of logical consistency in the database is to
ensure that if some row is a primary, as defined by being the
SignificantOtherID of another row, then that row must always have
SignificantOtherID as NULL. I recommend that, in the form where these rows
are added, you make the SignificantOther control invisible whenever the
current row is in the SignificantOtherID of any other row. You could
replace that control on the form with a label that says this row is a
Primary. Based on a test using a query to see if the current row is the
SignificantOtherID of any other row in the table, you can set the visibility
so one of the other of these controls is visible.

What if you need to change from PersonA being the Primary in a family to
having PersonB (formerly with SignificantOtherID set to PersonA) being
Primary? You will need to be able to change PersonB to be a primary
(SignificantOtherID changes from PersonA to NULL). For this, I would use a
UNION query to add a new line to the SignificantOther Combo Box with the
value of NULL. After you change PersonB (and any others who may be using
PersonA as Primary) to be a Primary (and setting all the "children" to have
PersonB as their Primary) you would be able to then set PersonA to have
PersonB as the primary.

To make this work well, it would be useful to be able to see all the
dependent rows of the currently displayed row whenever it is Primary. This
could be a combo or a subform. I'd go with a subform in which the
SignificantOther combo is displayed, allowing a user to quickly change from
PersonA to PersonB all the "children".

Now this is a fair amount of complexity, although it will be rather easy for
users to learn and appreciate (especially the first time they need it).
However, I do not know of another way to prevent users from corrupting the
data and getting spurious results. And don't forget to provide a reasonably
easy way for user to perform every task they will eventually require, while
providing the little or no way to screw it up.

Tom Ellison

Tom,
I'm so confused and I'm sorry to be so slow in picking up what you are
trying to do.

I'm not happy with the BaseName separate query. It returns a BaseName
only
for those who have a SignificantOtherID. However, I still need to report
on
members who have individual memberships. This is more what I think I
want:

SELECT IIf(PrimaryFamilyMember,Nz(NickName,FirstName) & " " &
LastName,(SELECT Nz(c2.[NickName], c2.[FirstName]) & " " & c2.[LastName]
FROM Contacts c2
WHERE c2.ContactID = c1.SignificantOtherID)) AS [Base Name], c1.Nickname,
c1.FirstName, c1.LastName, c1.ContactID, c1.SignificantOtherID
FROM Contacts AS c1;

This gives me John Doe with BaseName = John Doe;
Susie Anderson with BaseName = John Doe;
Peter Morgan (an individual membership) with BaseName = Peter Morgan.

Two issues- 1) I'm tyring to get rid of PrimaryFamilyMember and use
SignificantOtherID instead. That means that I need to have a 2-step query
(maybe) to find members with SignificantOtherID and those without a
SignificantOtherID so that I can report on members with both family
membership and individual memberships. I'm afraid I'll need help if you
have
the time.
And 2) no, I'll wait to ask until the query is OK.

The "big" query you posted isn't quite doing the trick. When I select
Doe,
John from my input form, I get back the one record attributable to Susie
Anderson listing Doe, John as the BaseName. When I select Anderson, Susie
I
get back all of John Doe's records listing Anderson, Susie as the
BaseName.
I want all of these records together listing one or the other as the
BaseName. If I use PrimaryFamilyMember, it would be the member = "yes" as
the BaseName. If I make the switch to SignificantOtherID, I'm not sure
who I
would say who should be listed first- but in any case, the listing order
should be consistent and bring back ALL of the associated records for John
Doe and Susie Anderson.
The "big" query also doesn't handle members without a SignificantOtherID
so
none of my individual membership people appear in the results.

You're exactly correct about SignificantOtherID- I only have the
capablility
to have 2 members associated. And yes, there may be more people in the
family that participate. It seems that would only happen in less than a
handful of situations and the work didn't seem worth the effort. Feel
free
to help me change my mind.

As far as the sorting issue, I'll ask again when the query is OK.

As far as the "not work" issue. Simply, I don't want a member to also be
their own significant other. I'm not sure how that would affect the
integrity of the data.

Here's the data:
John Doe and Susie Anderson have a family membership for $100.
They pay by check for $85.
John uses $10 "bucks", Susie uses $5 "bucks"

When I run the report, I want it to say:
John Doe and Susie Anderson Amount Due: $100
Amount Paid:
$85 check
$10 "bucks"
$5 "bucks" (I think I want the 2 "bucks" entries separate).

So I need John Doe's and Susie Anderson's data merged together....
Thanks!

Dear Stephanie:

Below, I have reformatted your query for my personal readability
preferences
so I can study it more easily.

The lines where I have placed a * at the beginning have recommended
changes.
Replace the * with 2 spaces, please.

SELECT Year(DateCreated) AS [Date Year],
* IIf(PrimaryFamilyMember 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 [Base Name],
C1.PrimaryFamilyMember, C1.FirstName, C1.LastName,
C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount,
DuesLineItem.DateRemitted, DuesLineItem.DateCreated,
PaymentTypes.Type, DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes
INNER JOIN (DuesItemType
INNER JOIN (Contacts AS C1
INNER JOIN (DuesLineItem
INNER JOIN Dues
ON DuesLineItem.DuesRateID = Dues.DuesRateID)
ON C1.ContactID = DuesLineItem.ContactID)
ON DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID)
ON PaymentTypes.TypeID = DuesLineItem.TypeID
*WHERE (((IIf(PrimaryFamilyMember] 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))) =
[Forms]![MemberParam]![FindMember]))
ORDER BY Year(DateCreated) DESC;

Since the BaseName is a separate, and possibly resuable functionality, I
recommend making a separate query just to do that:

SELECT *,
IIf(PrimaryFamilyMember 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 [Base Name]
FROM Contacts c1

Save this query and use it instead of Contacts in further work. It may
really simplify much of the work and eliminate difficulties you're
having.
As it is, you have need of this functionality 3 times in your query, once
to
display it, once to filter, and also potentially to sort.

More inline below:

Tom,
Thanks for the reply.
I am actually using BaseName to display a value and am having the user
use
a
parameter form to select BaseName. The issue is that I have BaseName
John
Doe but then can't also indicate that the dues are for John Doe and
Susie
Anderson.

How many names might be associated with an account? I would think only
one,
but you could also list the others in that family. How many could there
be?
No limit I would think. So, how do you want to handle it when there are
a
large number? Will you be printing some kind of statement on a form?
Will
you then allow as many "continuation pages" as are necessary to hold the
entire list of names?

Originally I wanted to sort by BaseName and then Year, but keep getting
this
error message: Multi-level GROUP BY clause is not allowed in a
subquery.

You didn't post the query at this point. I can't tell how to fix it.

Here's the (entire) query I'm working with now:
SELECT Year([DateCreated]) AS [Date Year],
IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID
=
c1.SignificantOtherID)) AS [Base Name], C1.PrimaryFamilyMember,
C1.FirstName,
C1.LastName, C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount, DuesLineItem.DateRemitted,
DuesLineItem.DateCreated, PaymentTypes.Type, DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes INNER JOIN (DuesItemType INNER JOIN (Contacts AS C1
INNER
JOIN (DuesLineItem INNER JOIN Dues ON DuesLineItem.DuesRateID =
Dues.DuesRateID) ON C1.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) ON
PaymentTypes.TypeID = DuesLineItem.TypeID
WHERE (((IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID
=
c1.SignificantOtherID)))=[Forms]![MemberParam]![FindMember]))
ORDER BY Year([DateCreated]) DESC;

I'll simply my statement about listing the Significant Other (which is
also
what I was trying to say regarding Base Member and Member Name. I
can't
get
it to work. I want to show: dues paid by John Doe and Susie Anderson;
dues
paid by Peter Morgan (a "non-family" member...

The above query is filtered by the FindMember (combo box?). Does it not
work for Peter Morgan? Does it not work for John and Susie? How does it
"not work?"

SignificantOtherID is used for address labels and reports to show that
there
are members who are couples. I wouldn't want SignificantOtherID to
point
back to a member. Then the labels would read: Peter Morgan and Peter
Morgan.

They would not do this if you filtered or tested if ContactID =
SignificantOtherID. I won't press this, however. Proabably it doesn't
matter, except for the integrity of the data.

"You could also say that when the SignificantOtherID is NULL then that
is
the Primary" That's not too bad. In fact, it's rather brilliant!
That
won't mess up any of my reports. So the only issue is with the query I
listed above.

Yes, that's good, and you can maintain referential integrity on that
basis
as well.

I can change to: IIf([SignificantOtherID]<>0,[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName])
But that make Susie Anderson the BaseName rather than John Doe. So,
I'd
appreciate your help in revamping the query to get the correct
BaseName,
not
using PrimaryFamilyMember but rather using SignificantOtherID, and
being
able
to list both members is there is a SignificantOtherID involved.
Thanks!






:

Dear Stephanie:

The idea of the BaseName is not so much to display this value, but to
sort
by it. If you sort by BaseName and PrimaryFamilyID you can produce
the
sequence of rows you asked for. If you want to do this in a report,
then
you must provide the BaseName value in the SELECT clause, since report
sorting is a function of the report, not of the query.

To get help on this, please post the entire query you have.

More inline below:

Tom Ellison


Tom,
I'm so sorry- I didn't know you had a replied. At what a reply it
was!
Thanks for the query help- that went a long way to get me where I
wanted
to
go. Base Name good! Parentheses slay me.

One question though on the query. Where there is a
SignificantOtherID,
I'd
like to be able to list the Significant Other (that way I can saw
John
Doe
and Susie Anderson have paid their family dues). However, every
time I
try
to list the other name as well, I run into the same issues I was
having
before the Base Name solved many of the problems.

Not following you. Example of data produce, please.


Here's the pertinent part of the existing query:

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

Post whole query, please. I'll try to fix it up.


Is there a way to say if the Base Name is not the same as the Member
Name,
list the Base Name and Member Name, else just the Base Name?

All rows of query results must have the same columns in the same
sequence.
You cannot omit a column in some rows and not others. That's probably
not
what you mean, but that's what is sounded like.


I'm still thinking about SignificantOtherID and PrimaryFamilyMember.
SignificantOther is used in many reports to show that the members
are a
couple and for mailing labels. So I wouldn't was SignificantOtherID
to
point
back to a member.

Sorry, "So I wouldn't was" doesn't make sense. Please ask again.

PrimaryFamilyMember is used in displaying "Bucks". I can set the
default
as
"yes"...

I had suggested that when the SignificantOtherID is equal to the
ContactID,
then that member is the Primary. You could also say that when the
SignificantOtherID is NULL then that is the Primary. That would make
PrimaryFamilyMember unnecessary. No big deal I expect, as long as
they
agree in all cases. It is the possibility they might not agree, and
the
difficulty in making sure they do always agree, that makes the
simplification superior. Do you have any rows where
PrimaryFamilyMember
is
True and SignificantOtherID is not NULL? Run a query to test that.
What
does it mean when this happens?


Thanks for the reply- it was very helpful!



:

Dear Stephanie:

A self-referencing table is not something I'm recommending, it is
what
you
already have. I'm not trying to get you to change that, but I did
recommend
how to protect its integrity.

In order to do the sorting you said you want, you must have the
BaseName
column I talked about. That's what sorts the family together. You
can
do
this in a table, but I'm not currently advocating that. That means
you
must
derive it in a query. I've showed how to do that. Whether you fix
up
the
table to protect data integrity or not, the BaseName approach is
essential.
However, it will suffer from the types of data inconsistencies I
warned
about, and for which I propose you query to see how bad the problem
will
be.

So, my response is not a recommendation to radically change your
table
structure. I did propose to simplify it and then protect it.
That's
optional, but it will affect how well the queries work.
Simplifying
the
table will result in an easier task of protecting the data
integrity.
Having complete integrity in the data will mean the query I propose
will
work better. The thing about adding BaseName to the actual table,
instead
of deriving it, is so you can index on it. That's only a
performance
issue,
and we can forget that for now. Perhaps I shouldn't have mentioned
that
so
early on. If it isn't needed, then let's forget it and move on.
We
can
revisit it later if it becomes important.

I gave this query before to derive the BaseName:

SELECT IIf(PrimaryFamilyNumber,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
FROM Contacts T

I'll fix that slightly now:

SELECT IIf(PrimaryFamilyMember,
Nz(NickName, FirstName) & " " & LastName,
(SELECT Nz(T1.NickName, T1.FirstName) & " " & T1.LastName
FROM Contacts T1
WHERE T1.ContactID = T.SignificantOtherID)
AS BaseName,

 

[Stephanie]

Tom,
Thanks for your thoughtful reply.
I am certainly willing to get rid of PrimaryFamilyMember flag as long as I
can get the query to return the data I want. The query posted isn't doing
what I had hoped.

John Doe and Susie Anderson have a family membership. John Doe is "primary"
so I want his name to be BaseName (if I get rid of "primary", I still want
one in the couple to be the BaseName, not both of them). When Susie has
cashed-in bucks, I need to relate them to the BaseName. The query that you
posted does not keep all of their information together. In my query, I
select Doe, John as the BaseName and want to see all of the payments
associated with that family membership. The posted query does not return
Susie's information with John's. Rather I have to run the query again with
Anderson, Susie as the BaseName which means that my report will not gather
the data from both John and Susie to give me a snapshot of their family
membership. That's why I want one (either John or Susie to always be the
BaseName).

The query I''m posting gives me what I want- expect that I'm still using
PrimaryFamilyMember and that I can't get the report to state that the
information is from both John and Susie (If I put on the report to list
BaseName, Susie's name- correctly- doesn't appear, but I would like her name
listed as she is the SignificantOtherID associated with John. However, John
is the SOID associated with Susie....).

SELECT Year([DateCreated]) AS [Date Year],
IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID =
c1.SignificantOtherID)) AS [Base Name], C1.PrimaryFamilyMember, C1.FirstName,
C1.LastName, C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount, DuesLineItem.DateRemitted,
DuesLineItem.DateCreated, PaymentTypes.Type, DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes INNER JOIN (DuesItemType INNER JOIN (Contacts AS C1 INNER
JOIN (DuesLineItem INNER JOIN Dues ON DuesLineItem.DuesRateID =
Dues.DuesRateID) ON C1.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) ON
PaymentTypes.TypeID = DuesLineItem.TypeID
WHERE (((IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID =
c1.SignificantOtherID)))=[Forms]![MemberParam]![FindMember]))
ORDER BY Year([DateCreated]) DESC;

Let me know when you've had enough!

Dear Stephanie:

The query I posted should have used SignificantOtherID instead of
PrimaryFamilyMember.

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 [Base Name]
FROM Contacts c1

Sorry. I mean to get away from using PrimaryFamilyMember. If your
convention is to have SignificantOtherID be NULL whenever
PrimaryFamilyMember is true, then this test is the same. However, I'm not
sure how you constrain PrimaryFamilyMember to always be false when
SignificantOtherID is not NULL, or how you constrain PrimaryFamilyMember to
be always true when SignificantOtherID is NULL.

The problem is that, whenever PrimaryFamilyMember is false, the query will
always try to use SignificantOtherID to find the BaseName from the person
who is the Primary. If the SignificantOtherID is missing (NULL) in this
case, then there is no BaseName available. On the other hand, when
SignificantOtherID is not NULL and PrimaryFamilyMember is true, what does
that mean? What does it do? This also is nonsense. That's why I suggest
you not even have PrimaryFamilyMember. The information it contains must
agree with SignificantOtherID or your database is logically corrupt. By
having only a single factor that determines whether someone is primary or
not you can ensure consistency. So, as a step in that direction, I strongly
recommend you start by eliminating the use of PrimaryFamilyMember, and just
test SignificantOtherID.

The other, more difficult issue of logical consistency in the database is to
ensure that if some row is a primary, as defined by being the
SignificantOtherID of another row, then that row must always have
SignificantOtherID as NULL. I recommend that, in the form where these rows
are added, you make the SignificantOther control invisible whenever the
current row is in the SignificantOtherID of any other row. You could
replace that control on the form with a label that says this row is a
Primary. Based on a test using a query to see if the current row is the
SignificantOtherID of any other row in the table, you can set the visibility
so one of the other of these controls is visible.

What if you need to change from PersonA being the Primary in a family to
having PersonB (formerly with SignificantOtherID set to PersonA) being
Primary? You will need to be able to change PersonB to be a primary
(SignificantOtherID changes from PersonA to NULL). For this, I would use a
UNION query to add a new line to the SignificantOther Combo Box with the
value of NULL. After you change PersonB (and any others who may be using
PersonA as Primary) to be a Primary (and setting all the "children" to have
PersonB as their Primary) you would be able to then set PersonA to have
PersonB as the primary.

To make this work well, it would be useful to be able to see all the
dependent rows of the currently displayed row whenever it is Primary. This
could be a combo or a subform. I'd go with a subform in which the
SignificantOther combo is displayed, allowing a user to quickly change from
PersonA to PersonB all the "children".

Now this is a fair amount of complexity, although it will be rather easy for
users to learn and appreciate (especially the first time they need it).
However, I do not know of another way to prevent users from corrupting the
data and getting spurious results. And don't forget to provide a reasonably
easy way for user to perform every task they will eventually require, while
providing the little or no way to screw it up.

Tom Ellison

Tom,
I'm so confused and I'm sorry to be so slow in picking up what you are
trying to do.

I'm not happy with the BaseName separate query. It returns a BaseName
only
for those who have a SignificantOtherID. However, I still need to report
on
members who have individual memberships. This is more what I think I
want:

SELECT IIf(PrimaryFamilyMember,Nz(NickName,FirstName) & " " &
LastName,(SELECT Nz(c2.[NickName], c2.[FirstName]) & " " & c2.[LastName]
FROM Contacts c2
WHERE c2.ContactID = c1.SignificantOtherID)) AS [Base Name], c1.Nickname,
c1.FirstName, c1.LastName, c1.ContactID, c1.SignificantOtherID
FROM Contacts AS c1;

This gives me John Doe with BaseName = John Doe;
Susie Anderson with BaseName = John Doe;
Peter Morgan (an individual membership) with BaseName = Peter Morgan.

Two issues- 1) I'm tyring to get rid of PrimaryFamilyMember and use
SignificantOtherID instead. That means that I need to have a 2-step query
(maybe) to find members with SignificantOtherID and those without a
SignificantOtherID so that I can report on members with both family
membership and individual memberships. I'm afraid I'll need help if you
have
the time.
And 2) no, I'll wait to ask until the query is OK.

The "big" query you posted isn't quite doing the trick. When I select
Doe,
John from my input form, I get back the one record attributable to Susie
Anderson listing Doe, John as the BaseName. When I select Anderson, Susie
I
get back all of John Doe's records listing Anderson, Susie as the
BaseName.
I want all of these records together listing one or the other as the
BaseName. If I use PrimaryFamilyMember, it would be the member = "yes" as
the BaseName. If I make the switch to SignificantOtherID, I'm not sure
who I
would say who should be listed first- but in any case, the listing order
should be consistent and bring back ALL of the associated records for John
Doe and Susie Anderson.
The "big" query also doesn't handle members without a SignificantOtherID
so
none of my individual membership people appear in the results.

You're exactly correct about SignificantOtherID- I only have the
capablility
to have 2 members associated. And yes, there may be more people in the
family that participate. It seems that would only happen in less than a
handful of situations and the work didn't seem worth the effort. Feel
free
to help me change my mind.

As far as the sorting issue, I'll ask again when the query is OK.

As far as the "not work" issue. Simply, I don't want a member to also be
their own significant other. I'm not sure how that would affect the
integrity of the data.

Here's the data:
John Doe and Susie Anderson have a family membership for $100.
They pay by check for $85.
John uses $10 "bucks", Susie uses $5 "bucks"

When I run the report, I want it to say:
John Doe and Susie Anderson Amount Due: $100
Amount Paid:
$85 check
$10 "bucks"
$5 "bucks" (I think I want the 2 "bucks" entries separate).

So I need John Doe's and Susie Anderson's data merged together....
Thanks!

Dear Stephanie:

Below, I have reformatted your query for my personal readability
preferences
so I can study it more easily.

The lines where I have placed a * at the beginning have recommended
changes.
Replace the * with 2 spaces, please.

SELECT Year(DateCreated) AS [Date Year],
* IIf(PrimaryFamilyMember 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 [Base Name],
C1.PrimaryFamilyMember, C1.FirstName, C1.LastName,
C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount,
DuesLineItem.DateRemitted, DuesLineItem.DateCreated,
PaymentTypes.Type, DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes
INNER JOIN (DuesItemType
INNER JOIN (Contacts AS C1
INNER JOIN (DuesLineItem
INNER JOIN Dues
ON DuesLineItem.DuesRateID = Dues.DuesRateID)
ON C1.ContactID = DuesLineItem.ContactID)
ON DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID)
ON PaymentTypes.TypeID = DuesLineItem.TypeID
*WHERE (((IIf(PrimaryFamilyMember] 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))) =
[Forms]![MemberParam]![FindMember]))
ORDER BY Year(DateCreated) DESC;

Since the BaseName is a separate, and possibly resuable functionality, I
recommend making a separate query just to do that:

SELECT *,
IIf(PrimaryFamilyMember 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 [Base Name]
FROM Contacts c1

Save this query and use it instead of Contacts in further work. It may
really simplify much of the work and eliminate difficulties you're
having.
As it is, you have need of this functionality 3 times in your query, once
to
display it, once to filter, and also potentially to sort.

More inline below:

Tom,
Thanks for the reply.
I am actually using BaseName to display a value and am having the user
use
a
parameter form to select BaseName. The issue is that I have BaseName
John
Doe but then can't also indicate that the dues are for John Doe and
Susie
Anderson.

How many names might be associated with an account? I would think only
one,
but you could also list the others in that family. How many could there
be?
No limit I would think. So, how do you want to handle it when there are
a
large number? Will you be printing some kind of statement on a form?
Will
you then allow as many "continuation pages" as are necessary to hold the
entire list of names?

Originally I wanted to sort by BaseName and then Year, but keep getting
this
error message: Multi-level GROUP BY clause is not allowed in a
subquery.

You didn't post the query at this point. I can't tell how to fix it.

Here's the (entire) query I'm working with now:
SELECT Year([DateCreated]) AS [Date Year],
IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID
=
c1.SignificantOtherID)) AS [Base Name], C1.PrimaryFamilyMember,
C1.FirstName,
C1.LastName, C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount, DuesLineItem.DateRemitted,
DuesLineItem.DateCreated, PaymentTypes.Type, DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes INNER JOIN (DuesItemType INNER JOIN (Contacts AS C1
INNER
JOIN (DuesLineItem INNER JOIN Dues ON DuesLineItem.DuesRateID =
Dues.DuesRateID) ON C1.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) ON
PaymentTypes.TypeID = DuesLineItem.TypeID
WHERE (((IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID
=
c1.SignificantOtherID)))=[Forms]![MemberParam]![FindMember]))
ORDER BY Year([DateCreated]) DESC;

I'll simply my statement about listing the Significant Other (which is
also
what I was trying to say regarding Base Member and Member Name. I
can't
get
it to work. I want to show: dues paid by John Doe and Susie Anderson;
dues
paid by Peter Morgan (a "non-family" member...

The above query is filtered by the FindMember (combo box?). Does it not
work for Peter Morgan? Does it not work for John and Susie? How does it
"not work?"

SignificantOtherID is used for address labels and reports to show that
there
are members who are couples. I wouldn't want SignificantOtherID to
point
back to a member. Then the labels would read: Peter Morgan and Peter
Morgan.

They would not do this if you filtered or tested if ContactID =
SignificantOtherID. I won't press this, however. Proabably it doesn't
matter, except for the integrity of the data.

"You could also say that when the SignificantOtherID is NULL then that
is
the Primary" That's not too bad. In fact, it's rather brilliant!
That

 

[Tom Ellison]

Dear Stephanie:

The query I posted was intended only to illustrate how to get the BaseName.
Does it get the BaseName correctly? If you want the "together" you would
need to sort by that:

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 [Base Name]
FROM Contacts c1
ORDER BY
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),
SignificantOtherID

I would prefer we get just this much straight before we leap too far ahead.
Does this work as far as giving the correct BaseName and sorting them the
way you want?

I had intended to use this much:

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 [Base Name]
FROM Contacts c1

as a building block on which to build. It is not important whether this
sorts correctly. Rather, save this much as a separate query and use it
instead of the Contacts table to build the final query. Sorting can wait
till the final step, although it can be good to see ahead of time that it
will sort correctly.

Tom Ellison

Tom,
Thanks for your thoughtful reply.
I am certainly willing to get rid of PrimaryFamilyMember flag as long as I
can get the query to return the data I want. The query posted isn't doing
what I had hoped.

John Doe and Susie Anderson have a family membership. John Doe is
"primary"
so I want his name to be BaseName (if I get rid of "primary", I still want
one in the couple to be the BaseName, not both of them). When Susie has
cashed-in bucks, I need to relate them to the BaseName. The query that
you
posted does not keep all of their information together. In my query, I
select Doe, John as the BaseName and want to see all of the payments
associated with that family membership. The posted query does not return
Susie's information with John's. Rather I have to run the query again
with
Anderson, Susie as the BaseName which means that my report will not gather
the data from both John and Susie to give me a snapshot of their family
membership. That's why I want one (either John or Susie to always be the
BaseName).

The query I''m posting gives me what I want- expect that I'm still using
PrimaryFamilyMember and that I can't get the report to state that the
information is from both John and Susie (If I put on the report to list
BaseName, Susie's name- correctly- doesn't appear, but I would like her
name
listed as she is the SignificantOtherID associated with John. However,
John
is the SOID associated with Susie....).

SELECT Year([DateCreated]) AS [Date Year],
IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID =
c1.SignificantOtherID)) AS [Base Name], C1.PrimaryFamilyMember,
C1.FirstName,
C1.LastName, C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount, DuesLineItem.DateRemitted,
DuesLineItem.DateCreated, PaymentTypes.Type, DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes INNER JOIN (DuesItemType INNER JOIN (Contacts AS C1
INNER
JOIN (DuesLineItem INNER JOIN Dues ON DuesLineItem.DuesRateID =
Dues.DuesRateID) ON C1.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) ON
PaymentTypes.TypeID = DuesLineItem.TypeID
WHERE (((IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID =
c1.SignificantOtherID)))=[Forms]![MemberParam]![FindMember]))
ORDER BY Year([DateCreated]) DESC;

Let me know when you've had enough!

Dear Stephanie:

The query I posted should have used SignificantOtherID instead of
PrimaryFamilyMember.

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 [Base Name]
FROM Contacts c1

Sorry. I mean to get away from using PrimaryFamilyMember. If your
convention is to have SignificantOtherID be NULL whenever
PrimaryFamilyMember is true, then this test is the same. However, I'm
not
sure how you constrain PrimaryFamilyMember to always be false when
SignificantOtherID is not NULL, or how you constrain PrimaryFamilyMember
to
be always true when SignificantOtherID is NULL.

The problem is that, whenever PrimaryFamilyMember is false, the query
will
always try to use SignificantOtherID to find the BaseName from the person
who is the Primary. If the SignificantOtherID is missing (NULL) in this
case, then there is no BaseName available. On the other hand, when
SignificantOtherID is not NULL and PrimaryFamilyMember is true, what does
that mean? What does it do? This also is nonsense. That's why I
suggest
you not even have PrimaryFamilyMember. The information it contains must
agree with SignificantOtherID or your database is logically corrupt. By
having only a single factor that determines whether someone is primary or
not you can ensure consistency. So, as a step in that direction, I
strongly
recommend you start by eliminating the use of PrimaryFamilyMember, and
just
test SignificantOtherID.

The other, more difficult issue of logical consistency in the database is
to
ensure that if some row is a primary, as defined by being the
SignificantOtherID of another row, then that row must always have
SignificantOtherID as NULL. I recommend that, in the form where these
rows
are added, you make the SignificantOther control invisible whenever the
current row is in the SignificantOtherID of any other row. You could
replace that control on the form with a label that says this row is a
Primary. Based on a test using a query to see if the current row is the
SignificantOtherID of any other row in the table, you can set the
visibility
so one of the other of these controls is visible.

What if you need to change from PersonA being the Primary in a family to
having PersonB (formerly with SignificantOtherID set to PersonA) being
Primary? You will need to be able to change PersonB to be a primary
(SignificantOtherID changes from PersonA to NULL). For this, I would use
a
UNION query to add a new line to the SignificantOther Combo Box with the
value of NULL. After you change PersonB (and any others who may be using
PersonA as Primary) to be a Primary (and setting all the "children" to
have
PersonB as their Primary) you would be able to then set PersonA to have
PersonB as the primary.

To make this work well, it would be useful to be able to see all the
dependent rows of the currently displayed row whenever it is Primary.
This
could be a combo or a subform. I'd go with a subform in which the
SignificantOther combo is displayed, allowing a user to quickly change
from
PersonA to PersonB all the "children".

Now this is a fair amount of complexity, although it will be rather easy
for
users to learn and appreciate (especially the first time they need it).
However, I do not know of another way to prevent users from corrupting
the
data and getting spurious results. And don't forget to provide a
reasonably
easy way for user to perform every task they will eventually require,
while
providing the little or no way to screw it up.

Tom Ellison

Tom,
I'm so confused and I'm sorry to be so slow in picking up what you are
trying to do.

I'm not happy with the BaseName separate query. It returns a BaseName
only
for those who have a SignificantOtherID. However, I still need to
report
on
members who have individual memberships. This is more what I think I
want:

SELECT IIf(PrimaryFamilyMember,Nz(NickName,FirstName) & " " &
LastName,(SELECT Nz(c2.[NickName], c2.[FirstName]) & " " &
c2.[LastName]
FROM Contacts c2
WHERE c2.ContactID = c1.SignificantOtherID)) AS [Base Name],
c1.Nickname,
c1.FirstName, c1.LastName, c1.ContactID, c1.SignificantOtherID
FROM Contacts AS c1;

This gives me John Doe with BaseName = John Doe;
Susie Anderson with BaseName = John Doe;
Peter Morgan (an individual membership) with BaseName = Peter Morgan.

Two issues- 1) I'm tyring to get rid of PrimaryFamilyMember and use
SignificantOtherID instead. That means that I need to have a 2-step
query
(maybe) to find members with SignificantOtherID and those without a
SignificantOtherID so that I can report on members with both family
membership and individual memberships. I'm afraid I'll need help if
you
have
the time.
And 2) no, I'll wait to ask until the query is OK.

The "big" query you posted isn't quite doing the trick. When I select
Doe,
John from my input form, I get back the one record attributable to
Susie
Anderson listing Doe, John as the BaseName. When I select Anderson,
Susie
I
get back all of John Doe's records listing Anderson, Susie as the
BaseName.
I want all of these records together listing one or the other as the
BaseName. If I use PrimaryFamilyMember, it would be the member = "yes"
as
the BaseName. If I make the switch to SignificantOtherID, I'm not sure
who I
would say who should be listed first- but in any case, the listing
order
should be consistent and bring back ALL of the associated records for
John
Doe and Susie Anderson.
The "big" query also doesn't handle members without a
SignificantOtherID
so
none of my individual membership people appear in the results.

You're exactly correct about SignificantOtherID- I only have the
capablility
to have 2 members associated. And yes, there may be more people in the
family that participate. It seems that would only happen in less than
a
handful of situations and the work didn't seem worth the effort. Feel
free
to help me change my mind.

As far as the sorting issue, I'll ask again when the query is OK.

As far as the "not work" issue. Simply, I don't want a member to also
be
their own significant other. I'm not sure how that would affect the
integrity of the data.

Here's the data:
John Doe and Susie Anderson have a family membership for $100.
They pay by check for $85.
John uses $10 "bucks", Susie uses $5 "bucks"

When I run the report, I want it to say:
John Doe and Susie Anderson Amount Due: $100
Amount Paid:
$85 check
$10 "bucks"
$5 "bucks" (I think I want the 2 "bucks" entries separate).

So I need John Doe's and Susie Anderson's data merged together....
Thanks!






:

Dear Stephanie:

Below, I have reformatted your query for my personal readability
preferences
so I can study it more easily.

The lines where I have placed a * at the beginning have recommended
changes.
Replace the * with 2 spaces, please.

SELECT Year(DateCreated) AS [Date Year],
* IIf(PrimaryFamilyMember 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 [Base Name],
C1.PrimaryFamilyMember, C1.FirstName, C1.LastName,
C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount,
DuesLineItem.DateRemitted, DuesLineItem.DateCreated,
PaymentTypes.Type, DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes
INNER JOIN (DuesItemType
INNER JOIN (Contacts AS C1
INNER JOIN (DuesLineItem
INNER JOIN Dues
ON DuesLineItem.DuesRateID = Dues.DuesRateID)
ON C1.ContactID = DuesLineItem.ContactID)
ON DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID)
ON PaymentTypes.TypeID = DuesLineItem.TypeID
*WHERE (((IIf(PrimaryFamilyMember] 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))) =
[Forms]![MemberParam]![FindMember]))
ORDER BY Year(DateCreated) DESC;

Since the BaseName is a separate, and possibly resuable functionality,
I
recommend making a separate query just to do that:

SELECT *,
IIf(PrimaryFamilyMember 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 [Base Name]
FROM Contacts c1

Save this query and use it instead of Contacts in further work. It
may
really simplify much of the work and eliminate difficulties you're
having.
As it is, you have need of this functionality 3 times in your query,
once
to
display it, once to filter, and also potentially to sort.

More inline below:

Tom,
Thanks for the reply.
I am actually using BaseName to display a value and am having the
user
use
a
parameter form to select BaseName. The issue is that I have
BaseName
John
Doe but then can't also indicate that the dues are for John Doe and
Susie
Anderson.

How many names might be associated with an account? I would think
only
one,
but you could also list the others in that family. How many could
there
be?
No limit I would think. So, how do you want to handle it when there
are
a
large number? Will you be printing some kind of statement on a form?
Will
you then allow as many "continuation pages" as are necessary to hold
the
entire list of names?

Originally I wanted to sort by BaseName and then Year, but keep
getting
this
error message: Multi-level GROUP BY clause is not allowed in a
subquery.

You didn't post the query at this point. I can't tell how to fix it.

Here's the (entire) query I'm working with now:
SELECT Year([DateCreated]) AS [Date Year],
IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE
c2.ContactID
=
c1.SignificantOtherID)) AS [Base Name], C1.PrimaryFamilyMember,
C1.FirstName,
C1.LastName, C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount,
DuesLineItem.DateRemitted,
DuesLineItem.DateCreated, PaymentTypes.Type,
DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes INNER JOIN (DuesItemType INNER JOIN (Contacts AS
C1
INNER
JOIN (DuesLineItem INNER JOIN Dues ON DuesLineItem.DuesRateID =
Dues.DuesRateID) ON C1.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) ON
PaymentTypes.TypeID = DuesLineItem.TypeID
WHERE (((IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE
c2.ContactID
=
c1.SignificantOtherID)))=[Forms]![MemberParam]![FindMember]))
ORDER BY Year([DateCreated]) DESC;

I'll simply my statement about listing the Significant Other (which
is
also
what I was trying to say regarding Base Member and Member Name. I
can't
get
it to work. I want to show: dues paid by John Doe and Susie
Anderson;
dues
paid by Peter Morgan (a "non-family" member...

The above query is filtered by the FindMember (combo box?). Does it
not
work for Peter Morgan? Does it not work for John and Susie? How does
it
"not work?"

SignificantOtherID is used for address labels and reports to show
that
there
are members who are couples. I wouldn't want SignificantOtherID to
point
back to a member. Then the labels would read: Peter Morgan and
Peter
Morgan.

They would not do this if you filtered or tested if ContactID =
SignificantOtherID. I won't press this, however. Proabably it
doesn't
matter, except for the integrity of the data.

"You could also say that when the SignificantOtherID is NULL then
that
is
the Primary" That's not too bad. In fact, it's rather brilliant!
That

 

[Stephanie]

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 together
Susie and John's "bucks". Anyway, I've named the query for ease (expect I
can't quite figure out how to use it within a second query as the only field
name available is BaseName).

And I couldn't get the sorting part to work- there was a syntax error that I
can't figure out. Thanks for the help!

Dear Stephanie:

The query I posted was intended only to illustrate how to get the BaseName.
Does it get the BaseName correctly? If you want the "together" you would
need to sort by that:

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 [Base Name]
FROM Contacts c1
ORDER BY
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),
SignificantOtherID

I would prefer we get just this much straight before we leap too far ahead.
Does this work as far as giving the correct BaseName and sorting them the
way you want?

I had intended to use this much:

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 [Base Name]
FROM Contacts c1

as a building block on which to build. It is not important whether this
sorts correctly. Rather, save this much as a separate query and use it
instead of the Contacts table to build the final query. Sorting can wait
till the final step, although it can be good to see ahead of time that it
will sort correctly.

Tom Ellison

Tom,
Thanks for your thoughtful reply.
I am certainly willing to get rid of PrimaryFamilyMember flag as long as I
can get the query to return the data I want. The query posted isn't doing
what I had hoped.

John Doe and Susie Anderson have a family membership. John Doe is
"primary"
so I want his name to be BaseName (if I get rid of "primary", I still want
one in the couple to be the BaseName, not both of them). When Susie has
cashed-in bucks, I need to relate them to the BaseName. The query that
you
posted does not keep all of their information together. In my query, I
select Doe, John as the BaseName and want to see all of the payments
associated with that family membership. The posted query does not return
Susie's information with John's. Rather I have to run the query again
with
Anderson, Susie as the BaseName which means that my report will not gather
the data from both John and Susie to give me a snapshot of their family
membership. That's why I want one (either John or Susie to always be the
BaseName).

The query I''m posting gives me what I want- expect that I'm still using
PrimaryFamilyMember and that I can't get the report to state that the
information is from both John and Susie (If I put on the report to list
BaseName, Susie's name- correctly- doesn't appear, but I would like her
name
listed as she is the SignificantOtherID associated with John. However,
John
is the SOID associated with Susie....).

SELECT Year([DateCreated]) AS [Date Year],
IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID =
c1.SignificantOtherID)) AS [Base Name], C1.PrimaryFamilyMember,
C1.FirstName,
C1.LastName, C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount, DuesLineItem.DateRemitted,
DuesLineItem.DateCreated, PaymentTypes.Type, DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes INNER JOIN (DuesItemType INNER JOIN (Contacts AS C1
INNER
JOIN (DuesLineItem INNER JOIN Dues ON DuesLineItem.DuesRateID =
Dues.DuesRateID) ON C1.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) ON
PaymentTypes.TypeID = DuesLineItem.TypeID
WHERE (((IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID =
c1.SignificantOtherID)))=[Forms]![MemberParam]![FindMember]))
ORDER BY Year([DateCreated]) DESC;

Let me know when you've had enough!

Dear Stephanie:

The query I posted should have used SignificantOtherID instead of
PrimaryFamilyMember.

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 [Base Name]
FROM Contacts c1

Sorry. I mean to get away from using PrimaryFamilyMember. If your
convention is to have SignificantOtherID be NULL whenever
PrimaryFamilyMember is true, then this test is the same. However, I'm
not
sure how you constrain PrimaryFamilyMember to always be false when
SignificantOtherID is not NULL, or how you constrain PrimaryFamilyMember
to
be always true when SignificantOtherID is NULL.

The problem is that, whenever PrimaryFamilyMember is false, the query
will
always try to use SignificantOtherID to find the BaseName from the person
who is the Primary. If the SignificantOtherID is missing (NULL) in this
case, then there is no BaseName available. On the other hand, when
SignificantOtherID is not NULL and PrimaryFamilyMember is true, what does
that mean? What does it do? This also is nonsense. That's why I
suggest
you not even have PrimaryFamilyMember. The information it contains must
agree with SignificantOtherID or your database is logically corrupt. By
having only a single factor that determines whether someone is primary or
not you can ensure consistency. So, as a step in that direction, I
strongly
recommend you start by eliminating the use of PrimaryFamilyMember, and
just
test SignificantOtherID.

The other, more difficult issue of logical consistency in the database is
to
ensure that if some row is a primary, as defined by being the
SignificantOtherID of another row, then that row must always have
SignificantOtherID as NULL. I recommend that, in the form where these
rows
are added, you make the SignificantOther control invisible whenever the
current row is in the SignificantOtherID of any other row. You could
replace that control on the form with a label that says this row is a
Primary. Based on a test using a query to see if the current row is the
SignificantOtherID of any other row in the table, you can set the
visibility
so one of the other of these controls is visible.

What if you need to change from PersonA being the Primary in a family to
having PersonB (formerly with SignificantOtherID set to PersonA) being
Primary? You will need to be able to change PersonB to be a primary
(SignificantOtherID changes from PersonA to NULL). For this, I would use
a
UNION query to add a new line to the SignificantOther Combo Box with the
value of NULL. After you change PersonB (and any others who may be using
PersonA as Primary) to be a Primary (and setting all the "children" to
have
PersonB as their Primary) you would be able to then set PersonA to have
PersonB as the primary.

To make this work well, it would be useful to be able to see all the
dependent rows of the currently displayed row whenever it is Primary.
This
could be a combo or a subform. I'd go with a subform in which the
SignificantOther combo is displayed, allowing a user to quickly change
from
PersonA to PersonB all the "children".

Now this is a fair amount of complexity, although it will be rather easy
for
users to learn and appreciate (especially the first time they need it).
However, I do not know of another way to prevent users from corrupting
the
data and getting spurious results. And don't forget to provide a
reasonably
easy way for user to perform every task they will eventually require,
while
providing the little or no way to screw it up.

Tom Ellison


Tom,
I'm so confused and I'm sorry to be so slow in picking up what you are
trying to do.

I'm not happy with the BaseName separate query. It returns a BaseName
only
for those who have a SignificantOtherID. However, I still need to
report
on
members who have individual memberships. This is more what I think I
want:

SELECT IIf(PrimaryFamilyMember,Nz(NickName,FirstName) & " " &
LastName,(SELECT Nz(c2.[NickName], c2.[FirstName]) & " " &
c2.[LastName]
FROM Contacts c2
WHERE c2.ContactID = c1.SignificantOtherID)) AS [Base Name],
c1.Nickname,
c1.FirstName, c1.LastName, c1.ContactID, c1.SignificantOtherID
FROM Contacts AS c1;

This gives me John Doe with BaseName = John Doe;
Susie Anderson with BaseName = John Doe;
Peter Morgan (an individual membership) with BaseName = Peter Morgan.

Two issues- 1) I'm tyring to get rid of PrimaryFamilyMember and use
SignificantOtherID instead. That means that I need to have a 2-step
query
(maybe) to find members with SignificantOtherID and those without a
SignificantOtherID so that I can report on members with both family
membership and individual memberships. I'm afraid I'll need help if
you
have
the time.
And 2) no, I'll wait to ask until the query is OK.

The "big" query you posted isn't quite doing the trick. When I select
Doe,
John from my input form, I get back the one record attributable to
Susie
Anderson listing Doe, John as the BaseName. When I select Anderson,
Susie
I
get back all of John Doe's records listing Anderson, Susie as the
BaseName.
I want all of these records together listing one or the other as the
BaseName. If I use PrimaryFamilyMember, it would be the member = "yes"
as
the BaseName. If I make the switch to SignificantOtherID, I'm not sure
who I
would say who should be listed first- but in any case, the listing
order
should be consistent and bring back ALL of the associated records for
John
Doe and Susie Anderson.
The "big" query also doesn't handle members without a
SignificantOtherID
so
none of my individual membership people appear in the results.

You're exactly correct about SignificantOtherID- I only have the
capablility
to have 2 members associated. And yes, there may be more people in the
family that participate. It seems that would only happen in less than
a
handful of situations and the work didn't seem worth the effort. Feel
free
to help me change my mind.

As far as the sorting issue, I'll ask again when the query is OK.

As far as the "not work" issue. Simply, I don't want a member to also
be
their own significant other. I'm not sure how that would affect the
integrity of the data.

Here's the data:
John Doe and Susie Anderson have a family membership for $100.
They pay by check for $85.
John uses $10 "bucks", Susie uses $5 "bucks"

When I run the report, I want it to say:
John Doe and Susie Anderson Amount Due: $100
Amount Paid:
$85 check
$10 "bucks"
$5 "bucks" (I think I want the 2 "bucks" entries separate).

So I need John Doe's and Susie Anderson's data merged together....
Thanks!






:

Dear Stephanie:

Below, I have reformatted your query for my personal readability
preferences
so I can study it more easily.

The lines where I have placed a * at the beginning have recommended
changes.
Replace the * with 2 spaces, please.

 

[Tom Ellison]

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
together
Susie and John's "bucks". Anyway, I've named the query for ease (expect I
can't quite figure out how to use it within a second query as the only
field
name available is BaseName).

And I couldn't get the sorting part to work- there was a syntax error that
I
can't figure out. Thanks for the help!

Dear Stephanie:

The query I posted was intended only to illustrate how to get the
BaseName.
Does it get the BaseName correctly? If you want the "together" you would
need to sort by that:

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 [Base Name]
FROM Contacts c1
ORDER BY
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),
SignificantOtherID

I would prefer we get just this much straight before we leap too far
ahead.
Does this work as far as giving the correct BaseName and sorting them the
way you want?

I had intended to use this much:

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 [Base Name]
FROM Contacts c1

as a building block on which to build. It is not important whether this
sorts correctly. Rather, save this much as a separate query and use it
instead of the Contacts table to build the final query. Sorting can wait
till the final step, although it can be good to see ahead of time that it
will sort correctly.

Tom Ellison

Tom,
Thanks for your thoughtful reply.
I am certainly willing to get rid of PrimaryFamilyMember flag as long
as I
can get the query to return the data I want. The query posted isn't
doing
what I had hoped.

John Doe and Susie Anderson have a family membership. John Doe is
"primary"
so I want his name to be BaseName (if I get rid of "primary", I still
want
one in the couple to be the BaseName, not both of them). When Susie
has
cashed-in bucks, I need to relate them to the BaseName. The query that
you
posted does not keep all of their information together. In my query, I
select Doe, John as the BaseName and want to see all of the payments
associated with that family membership. The posted query does not
return
Susie's information with John's. Rather I have to run the query again
with
Anderson, Susie as the BaseName which means that my report will not
gather
the data from both John and Susie to give me a snapshot of their family
membership. That's why I want one (either John or Susie to always be
the
BaseName).

The query I''m posting gives me what I want- expect that I'm still
using
PrimaryFamilyMember and that I can't get the report to state that the
information is from both John and Susie (If I put on the report to list
BaseName, Susie's name- correctly- doesn't appear, but I would like her
name
listed as she is the SignificantOtherID associated with John. However,
John
is the SOID associated with Susie....).

SELECT Year([DateCreated]) AS [Date Year],
IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID
=
c1.SignificantOtherID)) AS [Base Name], C1.PrimaryFamilyMember,
C1.FirstName,
C1.LastName, C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount, DuesLineItem.DateRemitted,
DuesLineItem.DateCreated, PaymentTypes.Type, DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes INNER JOIN (DuesItemType INNER JOIN (Contacts AS C1
INNER
JOIN (DuesLineItem INNER JOIN Dues ON DuesLineItem.DuesRateID =
Dues.DuesRateID) ON C1.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) ON
PaymentTypes.TypeID = DuesLineItem.TypeID
WHERE (((IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID
=
c1.SignificantOtherID)))=[Forms]![MemberParam]![FindMember]))
ORDER BY Year([DateCreated]) DESC;

Let me know when you've had enough!

:

Dear Stephanie:

The query I posted should have used SignificantOtherID instead of
PrimaryFamilyMember.

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 [Base Name]
FROM Contacts c1

Sorry. I mean to get away from using PrimaryFamilyMember. If your
convention is to have SignificantOtherID be NULL whenever
PrimaryFamilyMember is true, then this test is the same. However, I'm
not
sure how you constrain PrimaryFamilyMember to always be false when
SignificantOtherID is not NULL, or how you constrain
PrimaryFamilyMember
to
be always true when SignificantOtherID is NULL.

The problem is that, whenever PrimaryFamilyMember is false, the query
will
always try to use SignificantOtherID to find the BaseName from the
person
who is the Primary. If the SignificantOtherID is missing (NULL) in
this
case, then there is no BaseName available. On the other hand, when
SignificantOtherID is not NULL and PrimaryFamilyMember is true, what
does
that mean? What does it do? This also is nonsense. That's why I
suggest
you not even have PrimaryFamilyMember. The information it contains
must
agree with SignificantOtherID or your database is logically corrupt.
By
having only a single factor that determines whether someone is primary
or
not you can ensure consistency. So, as a step in that direction, I
strongly
recommend you start by eliminating the use of PrimaryFamilyMember, and
just
test SignificantOtherID.

The other, more difficult issue of logical consistency in the database
is
to
ensure that if some row is a primary, as defined by being the
SignificantOtherID of another row, then that row must always have
SignificantOtherID as NULL. I recommend that, in the form where these
rows
are added, you make the SignificantOther control invisible whenever
the
current row is in the SignificantOtherID of any other row. You could
replace that control on the form with a label that says this row is a
Primary. Based on a test using a query to see if the current row is
the
SignificantOtherID of any other row in the table, you can set the
visibility
so one of the other of these controls is visible.

What if you need to change from PersonA being the Primary in a family
to
having PersonB (formerly with SignificantOtherID set to PersonA) being
Primary? You will need to be able to change PersonB to be a primary
(SignificantOtherID changes from PersonA to NULL). For this, I would
use
a
UNION query to add a new line to the SignificantOther Combo Box with
the
value of NULL. After you change PersonB (and any others who may be
using
PersonA as Primary) to be a Primary (and setting all the "children" to
have
PersonB as their Primary) you would be able to then set PersonA to
have
PersonB as the primary.

To make this work well, it would be useful to be able to see all the
dependent rows of the currently displayed row whenever it is Primary.
This
could be a combo or a subform. I'd go with a subform in which the
SignificantOther combo is displayed, allowing a user to quickly change
from
PersonA to PersonB all the "children".

Now this is a fair amount of complexity, although it will be rather
easy
for
users to learn and appreciate (especially the first time they need
it).
However, I do not know of another way to prevent users from corrupting
the
data and getting spurious results. And don't forget to provide a
reasonably
easy way for user to perform every task they will eventually require,
while
providing the little or no way to screw it up.

Tom Ellison


Tom,
I'm so confused and I'm sorry to be so slow in picking up what you
are
trying to do.

I'm not happy with the BaseName separate query. It returns a
BaseName
only
for those who have a SignificantOtherID. However, I still need to
report
on
members who have individual memberships. This is more what I think
I
want:

SELECT IIf(PrimaryFamilyMember,Nz(NickName,FirstName) & " " &
LastName,(SELECT Nz(c2.[NickName], c2.[FirstName]) & " " &
c2.[LastName]
FROM Contacts c2
WHERE c2.ContactID = c1.SignificantOtherID)) AS [Base Name],
c1.Nickname,
c1.FirstName, c1.LastName, c1.ContactID, c1.SignificantOtherID
FROM Contacts AS c1;

This gives me John Doe with BaseName = John Doe;
Susie Anderson with BaseName = John Doe;
Peter Morgan (an individual membership) with BaseName = Peter
Morgan.

Two issues- 1) I'm tyring to get rid of PrimaryFamilyMember and use
SignificantOtherID instead. That means that I need to have a 2-step
query
(maybe) to find members with SignificantOtherID and those without a
SignificantOtherID so that I can report on members with both family
membership and individual memberships. I'm afraid I'll need help if
you
have
the time.
And 2) no, I'll wait to ask until the query is OK.

The "big" query you posted isn't quite doing the trick. When I
select
Doe,
John from my input form, I get back the one record attributable to
Susie
Anderson listing Doe, John as the BaseName. When I select Anderson,
Susie
I
get back all of John Doe's records listing Anderson, Susie as the
BaseName.
I want all of these records together listing one or the other as the
BaseName. If I use PrimaryFamilyMember, it would be the member =
"yes"
as
the BaseName. If I make the switch to SignificantOtherID, I'm not
sure
who I
would say who should be listed first- but in any case, the listing
order
should be consistent and bring back ALL of the associated records
for
John
Doe and Susie Anderson.
The "big" query also doesn't handle members without a
SignificantOtherID
so
none of my individual membership people appear in the results.

You're exactly correct about SignificantOtherID- I only have the
capablility
to have 2 members associated. And yes, there may be more people in
the
family that participate. It seems that would only happen in less
than
a
handful of situations and the work didn't seem worth the effort.
Feel
free
to help me change my mind.

As far as the sorting issue, I'll ask again when the query is OK.

As far as the "not work" issue. Simply, I don't want a member to
also
be
their own significant other. I'm not sure how that would affect the
integrity of the data.

Here's the data:
John Doe and Susie Anderson have a family membership for $100.
They pay by check for $85.
John uses $10 "bucks", Susie uses $5 "bucks"

When I run the report, I want it to say:
John Doe and Susie Anderson Amount Due: $100
Amount Paid:
$85 check
$10 "bucks"
$5 "bucks" (I think I want the 2 "bucks" entries separate).

So I need John Doe's and Susie Anderson's data merged together....
Thanks!






:

Dear Stephanie:

Below, I have reformatted your query for my personal readability
preferences
so I can study it more easily.

The lines where I have placed a * at the beginning have recommended
changes.
Replace the * with 2 spaces, please.

 

[Stephanie]

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
together
Susie and John's "bucks". Anyway, I've named the query for ease (expect I
can't quite figure out how to use it within a second query as the only
field
name available is BaseName).

And I couldn't get the sorting part to work- there was a syntax error that
I
can't figure out. Thanks for the help!

Dear Stephanie:

The query I posted was intended only to illustrate how to get the
BaseName.
Does it get the BaseName correctly? If you want the "together" you would
need to sort by that:

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 [Base Name]
FROM Contacts c1
ORDER BY
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),
SignificantOtherID

I would prefer we get just this much straight before we leap too far
ahead.
Does this work as far as giving the correct BaseName and sorting them the
way you want?

I had intended to use this much:

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 [Base Name]
FROM Contacts c1

as a building block on which to build. It is not important whether this
sorts correctly. Rather, save this much as a separate query and use it
instead of the Contacts table to build the final query. Sorting can wait
till the final step, although it can be good to see ahead of time that it
will sort correctly.

Tom Ellison


Tom,
Thanks for your thoughtful reply.
I am certainly willing to get rid of PrimaryFamilyMember flag as long
as I
can get the query to return the data I want. The query posted isn't
doing
what I had hoped.

John Doe and Susie Anderson have a family membership. John Doe is
"primary"
so I want his name to be BaseName (if I get rid of "primary", I still
want
one in the couple to be the BaseName, not both of them). When Susie
has
cashed-in bucks, I need to relate them to the BaseName. The query that
you
posted does not keep all of their information together. In my query, I
select Doe, John as the BaseName and want to see all of the payments
associated with that family membership. The posted query does not
return
Susie's information with John's. Rather I have to run the query again
with
Anderson, Susie as the BaseName which means that my report will not
gather
the data from both John and Susie to give me a snapshot of their family
membership. That's why I want one (either John or Susie to always be
the
BaseName).

The query I''m posting gives me what I want- expect that I'm still
using
PrimaryFamilyMember and that I can't get the report to state that the
information is from both John and Susie (If I put on the report to list
BaseName, Susie's name- correctly- doesn't appear, but I would like her
name
listed as she is the SignificantOtherID associated with John. However,
John
is the SOID associated with Susie....).

SELECT Year([DateCreated]) AS [Date Year],
IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID
=
c1.SignificantOtherID)) AS [Base Name], C1.PrimaryFamilyMember,
C1.FirstName,
C1.LastName, C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount, DuesLineItem.DateRemitted,
DuesLineItem.DateCreated, PaymentTypes.Type, DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes INNER JOIN (DuesItemType INNER JOIN (Contacts AS C1
INNER
JOIN (DuesLineItem INNER JOIN Dues ON DuesLineItem.DuesRateID =
Dues.DuesRateID) ON C1.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) ON
PaymentTypes.TypeID = DuesLineItem.TypeID
WHERE (((IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE c2.ContactID
=
c1.SignificantOtherID)))=[Forms]![MemberParam]![FindMember]))
ORDER BY Year([DateCreated]) DESC;

Let me know when you've had enough!

:

Dear Stephanie:

The query I posted should have used SignificantOtherID instead of
PrimaryFamilyMember.

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 [Base Name]
FROM Contacts c1

Sorry. I mean to get away from using PrimaryFamilyMember. If your
convention is to have SignificantOtherID be NULL whenever
PrimaryFamilyMember is true, then this test is the same. However, I'm
not
sure how you constrain PrimaryFamilyMember to always be false when
SignificantOtherID is not NULL, or how you constrain
PrimaryFamilyMember
to
be always true when SignificantOtherID is NULL.

The problem is that, whenever PrimaryFamilyMember is false, the query
will
always try to use SignificantOtherID to find the BaseName from the
person
who is the Primary. If the SignificantOtherID is missing (NULL) in
this
case, then there is no BaseName available. On the other hand, when
SignificantOtherID is not NULL and PrimaryFamilyMember is true, what
does
that mean? What does it do? This also is nonsense. That's why I
suggest
you not even have PrimaryFamilyMember. The information it contains
must
agree with SignificantOtherID or your database is logically corrupt.
By
having only a single factor that determines whether someone is primary
or
not you can ensure consistency. So, as a step in that direction, I
strongly
recommend you start by eliminating the use of PrimaryFamilyMember, and
just
test SignificantOtherID.

The other, more difficult issue of logical consistency in the database
is
to
ensure that if some row is a primary, as defined by being the
SignificantOtherID of another row, then that row must always have
SignificantOtherID as NULL. I recommend that, in the form where these
rows
are added, you make the SignificantOther control invisible whenever
the
current row is in the SignificantOtherID of any other row. You could
replace that control on the form with a label that says this row is a
Primary. Based on a test using a query to see if the current row is
the
SignificantOtherID of any other row in the table, you can set the
visibility
so one of the other of these controls is visible.

What if you need to change from PersonA being the Primary in a family
to
having PersonB (formerly with SignificantOtherID set to PersonA) being
Primary? You will need to be able to change PersonB to be a primary
(SignificantOtherID changes from PersonA to NULL). For this, I would
use
a
UNION query to add a new line to the SignificantOther Combo Box with
the
value of NULL. After you change PersonB (and any others who may be
using
PersonA as Primary) to be a Primary (and setting all the "children" to
have
PersonB as their Primary) you would be able to then set PersonA to
have
PersonB as the primary.

To make this work well, it would be useful to be able to see all the
dependent rows of the currently displayed row whenever it is Primary.
This
could be a combo or a subform. I'd go with a subform in which the
SignificantOther combo is displayed, allowing a user to quickly change
from
PersonA to PersonB all the "children".

Now this is a fair amount of complexity, although it will be rather
easy
for
users to learn and appreciate (especially the first time they need
it).
However, I do not know of another way to prevent users from corrupting
the
data and getting spurious results. And don't forget to provide a
reasonably
easy way for user to perform every task they will eventually require,
while
providing the little or no way to screw it up.

Tom Ellison


Tom,
I'm so confused and I'm sorry to be so slow in picking up what you
are
trying to do.

I'm not happy with the BaseName separate query. It returns a
BaseName
only
for those who have a SignificantOtherID. However, I still need to
report
on
members who have individual memberships. This is more what I think
I
want:

SELECT IIf(PrimaryFamilyMember,Nz(NickName,FirstName) & " " &
LastName,(SELECT Nz(c2.[NickName], c2.[FirstName]) & " " &
c2.[LastName]
FROM Contacts c2
WHERE c2.ContactID = c1.SignificantOtherID)) AS [Base Name],
c1.Nickname,
c1.FirstName, c1.LastName, c1.ContactID, c1.SignificantOtherID
FROM Contacts AS c1;

This gives me John Doe with BaseName = John Doe;
Susie Anderson with BaseName = John Doe;
Peter Morgan (an individual membership) with BaseName = Peter
Morgan.

Two issues- 1) I'm tyring to get rid of PrimaryFamilyMember and use
SignificantOtherID instead. That means that I need to have a 2-step
query
(maybe) to find members with SignificantOtherID and those without a
SignificantOtherID so that I can report on members with both family
membership and individual memberships. I'm afraid I'll need help if
you

 

[Tom Ellison]

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
together
Susie and John's "bucks". Anyway, I've named the query for ease
(expect I
can't quite figure out how to use it within a second query as the only
field
name available is BaseName).

And I couldn't get the sorting part to work- there was a syntax error
that
I
can't figure out. Thanks for the help!

:

Dear Stephanie:

The query I posted was intended only to illustrate how to get the
BaseName.
Does it get the BaseName correctly? If you want the "together" you
would
need to sort by that:

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 [Base Name]
FROM Contacts c1
ORDER BY
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),
SignificantOtherID

I would prefer we get just this much straight before we leap too far
ahead.
Does this work as far as giving the correct BaseName and sorting them
the
way you want?

I had intended to use this much:

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 [Base Name]
FROM Contacts c1

as a building block on which to build. It is not important whether
this
sorts correctly. Rather, save this much as a separate query and use
it
instead of the Contacts table to build the final query. Sorting can
wait
till the final step, although it can be good to see ahead of time that
it
will sort correctly.

Tom Ellison


Tom,
Thanks for your thoughtful reply.
I am certainly willing to get rid of PrimaryFamilyMember flag as
long
as I
can get the query to return the data I want. The query posted isn't
doing
what I had hoped.

John Doe and Susie Anderson have a family membership. John Doe is
"primary"
so I want his name to be BaseName (if I get rid of "primary", I
still
want
one in the couple to be the BaseName, not both of them). When Susie
has
cashed-in bucks, I need to relate them to the BaseName. The query
that
you
posted does not keep all of their information together. In my
query, I
select Doe, John as the BaseName and want to see all of the payments
associated with that family membership. The posted query does not
return
Susie's information with John's. Rather I have to run the query
again
with
Anderson, Susie as the BaseName which means that my report will not
gather
the data from both John and Susie to give me a snapshot of their
family
membership. That's why I want one (either John or Susie to always
be
the
BaseName).

The query I''m posting gives me what I want- expect that I'm still
using
PrimaryFamilyMember and that I can't get the report to state that
the
information is from both John and Susie (If I put on the report to
list
BaseName, Susie's name- correctly- doesn't appear, but I would like
her
name
listed as she is the SignificantOtherID associated with John.
However,
John
is the SOID associated with Susie....).

SELECT Year([DateCreated]) AS [Date Year],
IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE
c2.ContactID
=
c1.SignificantOtherID)) AS [Base Name], C1.PrimaryFamilyMember,
C1.FirstName,
C1.LastName, C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount,
DuesLineItem.DateRemitted,
DuesLineItem.DateCreated, PaymentTypes.Type,
DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes INNER JOIN (DuesItemType INNER JOIN (Contacts AS
C1
INNER
JOIN (DuesLineItem INNER JOIN Dues ON DuesLineItem.DuesRateID =
Dues.DuesRateID) ON C1.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) ON
PaymentTypes.TypeID = DuesLineItem.TypeID
WHERE (((IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE
c2.ContactID
=
c1.SignificantOtherID)))=[Forms]![MemberParam]![FindMember]))
ORDER BY Year([DateCreated]) DESC;

Let me know when you've had enough!

:

Dear Stephanie:

The query I posted should have used SignificantOtherID instead of
PrimaryFamilyMember.

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 [Base Name]
FROM Contacts c1

Sorry. I mean to get away from using PrimaryFamilyMember. If your
convention is to have SignificantOtherID be NULL whenever
PrimaryFamilyMember is true, then this test is the same. However,
I'm
not
sure how you constrain PrimaryFamilyMember to always be false when
SignificantOtherID is not NULL, or how you constrain
PrimaryFamilyMember
to
be always true when SignificantOtherID is NULL.

The problem is that, whenever PrimaryFamilyMember is false, the
query
will
always try to use SignificantOtherID to find the BaseName from the
person
who is the Primary. If the SignificantOtherID is missing (NULL) in
this
case, then there is no BaseName available. On the other hand, when
SignificantOtherID is not NULL and PrimaryFamilyMember is true,
what
does
that mean? What does it do? This also is nonsense. That's why I
suggest
you not even have PrimaryFamilyMember. The information it contains
must
agree with SignificantOtherID or your database is logically
corrupt.
By
having only a single factor that determines whether someone is
primary
or
not you can ensure consistency. So, as a step in that direction, I
strongly
recommend you start by eliminating the use of PrimaryFamilyMember,
and
just
test SignificantOtherID.

The other, more difficult issue of logical consistency in the
database
is
to
ensure that if some row is a primary, as defined by being the
SignificantOtherID of another row, then that row must always have
SignificantOtherID as NULL. I recommend that, in the form where
these
rows
are added, you make the SignificantOther control invisible whenever
the
current row is in the SignificantOtherID of any other row. You
could
replace that control on the form with a label that says this row is
a
Primary. Based on a test using a query to see if the current row
is
the
SignificantOtherID of any other row in the table, you can set the
visibility
so one of the other of these controls is visible.

What if you need to change from PersonA being the Primary in a
family
to
having PersonB (formerly with SignificantOtherID set to PersonA)
being
Primary? You will need to be able to change PersonB to be a
primary
(SignificantOtherID changes from PersonA to NULL). For this, I
would
use
a
UNION query to add a new line to the SignificantOther Combo Box
with
the
value of NULL. After you change PersonB (and any others who may be
using
PersonA as Primary) to be a Primary (and setting all the "children"
to
have
PersonB as their Primary) you would be able to then set PersonA to
have
PersonB as the primary.

To make this work well, it would be useful to be able to see all
the
dependent rows of the currently displayed row whenever it is
Primary.
This
could be a combo or a subform. I'd go with a subform in which the
SignificantOther combo is displayed, allowing a user to quickly
change
from
PersonA to PersonB all the "children".

Now this is a fair amount of complexity, although it will be rather
easy
for
users to learn and appreciate (especially the first time they need
it).
However, I do not know of another way to prevent users from
corrupting
the
data and getting spurious results. And don't forget to provide a
reasonably
easy way for user to perform every task they will eventually
require,
while
providing the little or no way to screw it up.

Tom Ellison


Tom,
I'm so confused and I'm sorry to be so slow in picking up what
you
are
trying to do.

I'm not happy with the BaseName separate query. It returns a
BaseName
only
for those who have a SignificantOtherID. However, I still need
to
report
on
members who have individual memberships. This is more what I
think
I
want:

SELECT IIf(PrimaryFamilyMember,Nz(NickName,FirstName) & " " &
LastName,(SELECT Nz(c2.[NickName], c2.[FirstName]) & " " &
c2.[LastName]
FROM Contacts c2
WHERE c2.ContactID = c1.SignificantOtherID)) AS [Base Name],
c1.Nickname,
c1.FirstName, c1.LastName, c1.ContactID, c1.SignificantOtherID
FROM Contacts AS c1;

This gives me John Doe with BaseName = John Doe;
Susie Anderson with BaseName = John Doe;
Peter Morgan (an individual membership) with BaseName = Peter
Morgan.

Two issues- 1) I'm tyring to get rid of PrimaryFamilyMember and
use
SignificantOtherID instead. That means that I need to have a
2-step
query
(maybe) to find members with SignificantOtherID and those without
a
SignificantOtherID so that I can report on members with both
family
membership and individual memberships. I'm afraid I'll need help
if
you

 

[Stephanie]

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
together
Susie and John's "bucks". Anyway, I've named the query for ease
(expect I
can't quite figure out how to use it within a second query as the only
field
name available is BaseName).

And I couldn't get the sorting part to work- there was a syntax error
that
I
can't figure out. Thanks for the help!

:

Dear Stephanie:

The query I posted was intended only to illustrate how to get the
BaseName.
Does it get the BaseName correctly? If you want the "together" you
would
need to sort by that:

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 [Base Name]
FROM Contacts c1
ORDER BY
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),
SignificantOtherID

I would prefer we get just this much straight before we leap too far
ahead.
Does this work as far as giving the correct BaseName and sorting them
the
way you want?

I had intended to use this much:

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 [Base Name]
FROM Contacts c1

as a building block on which to build. It is not important whether
this
sorts correctly. Rather, save this much as a separate query and use
it
instead of the Contacts table to build the final query. Sorting can
wait
till the final step, although it can be good to see ahead of time that
it
will sort correctly.

Tom Ellison


Tom,
Thanks for your thoughtful reply.
I am certainly willing to get rid of PrimaryFamilyMember flag as
long
as I
can get the query to return the data I want. The query posted isn't
doing
what I had hoped.

John Doe and Susie Anderson have a family membership. John Doe is
"primary"
so I want his name to be BaseName (if I get rid of "primary", I
still
want
one in the couple to be the BaseName, not both of them). When Susie
has
cashed-in bucks, I need to relate them to the BaseName. The query
that
you
posted does not keep all of their information together. In my
query, I
select Doe, John as the BaseName and want to see all of the payments
associated with that family membership. The posted query does not
return
Susie's information with John's. Rather I have to run the query
again
with
Anderson, Susie as the BaseName which means that my report will not
gather
the data from both John and Susie to give me a snapshot of their
family
membership. That's why I want one (either John or Susie to always
be
the
BaseName).

The query I''m posting gives me what I want- expect that I'm still
using
PrimaryFamilyMember and that I can't get the report to state that
the
information is from both John and Susie (If I put on the report to
list
BaseName, Susie's name- correctly- doesn't appear, but I would like
her
name
listed as she is the SignificantOtherID associated with John.
However,
John
is the SOID associated with Susie....).

SELECT Year([DateCreated]) AS [Date Year],
IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", " &
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE
c2.ContactID
=
c1.SignificantOtherID)) AS [Base Name], C1.PrimaryFamilyMember,
C1.FirstName,
C1.LastName, C1.ContactID, Dues.DuesAmount, C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount,
DuesLineItem.DateRemitted,
DuesLineItem.DateCreated, PaymentTypes.Type,
DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes INNER JOIN (DuesItemType INNER JOIN (Contacts AS
C1
INNER
JOIN (DuesLineItem INNER JOIN Dues ON DuesLineItem.DuesRateID =
Dues.DuesRateID) ON C1.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) ON
PaymentTypes.TypeID = DuesLineItem.TypeID

 

[Tom Ellison]

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
together
Susie and John's "bucks". Anyway, I've named the query for ease
(expect I
can't quite figure out how to use it within a second query as the
only
field
name available is BaseName).

And I couldn't get the sorting part to work- there was a syntax
error
that
I
can't figure out. Thanks for the help!

:

Dear Stephanie:

The query I posted was intended only to illustrate how to get the
BaseName.
Does it get the BaseName correctly? If you want the "together" you
would
need to sort by that:

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 [Base Name]
FROM Contacts c1
ORDER BY
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),
SignificantOtherID

I would prefer we get just this much straight before we leap too
far
ahead.
Does this work as far as giving the correct BaseName and sorting
them
the
way you want?

I had intended to use this much:

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 [Base Name]
FROM Contacts c1

as a building block on which to build. It is not important whether
this
sorts correctly. Rather, save this much as a separate query and
use
it
instead of the Contacts table to build the final query. Sorting
can
wait
till the final step, although it can be good to see ahead of time
that
it
will sort correctly.

Tom Ellison


Tom,
Thanks for your thoughtful reply.
I am certainly willing to get rid of PrimaryFamilyMember flag as
long
as I
can get the query to return the data I want. The query posted
isn't
doing
what I had hoped.

John Doe and Susie Anderson have a family membership. John Doe
is
"primary"
so I want his name to be BaseName (if I get rid of "primary", I
still
want
one in the couple to be the BaseName, not both of them). When
Susie
has
cashed-in bucks, I need to relate them to the BaseName. The
query
that
you
posted does not keep all of their information together. In my
query, I
select Doe, John as the BaseName and want to see all of the
payments
associated with that family membership. The posted query does
not
return
Susie's information with John's. Rather I have to run the query
again
with
Anderson, Susie as the BaseName which means that my report will
not
gather
the data from both John and Susie to give me a snapshot of their
family
membership. That's why I want one (either John or Susie to
always
be
the
BaseName).

The query I''m posting gives me what I want- expect that I'm
still
using
PrimaryFamilyMember and that I can't get the report to state that
the
information is from both John and Susie (If I put on the report
to
list
BaseName, Susie's name- correctly- doesn't appear, but I would
like
her
name
listed as she is the SignificantOtherID associated with John.
However,
John
is the SOID associated with Susie....).

SELECT Year([DateCreated]) AS [Date Year],
IIf([PrimaryFamilyMember],[c1].[LastName] & ", " &
Nz([c1].[NickName],[c1].[FirstName]),(SELECT c2.[LastName] &", "
&
Nz(c2.[NickName], c2.[FirstName]) FROM Contacts c2 WHERE
c2.ContactID
=
c1.SignificantOtherID)) AS [Base Name], C1.PrimaryFamilyMember,
C1.FirstName,
C1.LastName, C1.ContactID, Dues.DuesAmount,
C1.SignificantOtherID,
DuesLineItem.Reference, DuesLineItem.Amount,
DuesLineItem.DateRemitted,
DuesLineItem.DateCreated, PaymentTypes.Type,
DuesItemType.DuesItemType,
Dues.MemberCategory
FROM PaymentTypes INNER JOIN (DuesItemType INNER JOIN (Contacts
AS
C1
INNER
JOIN (DuesLineItem INNER JOIN Dues ON DuesLineItem.DuesRateID =
Dues.DuesRateID) ON C1.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) ON
PaymentTypes.TypeID = DuesLineItem.TypeID