Summing a sum

[Stephanie]

Hi. I have a query that sums "Bucks" for each Contact. It works fine. Some
Contacts have Spouses that are also Contacts. In these "family" situations I
want to add together the sum of Bucks for the family. I can't quite figure
it out. Here's 2 queries that I'm working with:

To Sum "Bucks" by Contact:
SELECT DISTINCT Contacts.ContactID, Sum(Volunteering.ITABucks) AS
SumOfITABucks, Contacts.LastName, Contacts.FirstName
FROM (((Event LEFT JOIN Animals ON Event.AnimalsID = Animals.AnimalsID)
INNER JOIN Volunteering ON Event.VolunteeringID =
Volunteering.VolunteeringID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) INNER JOIN (Organizations INNER JOIN EventSponsors ON
Organizations.OrganizationID = EventSponsors.OrganizationID) ON
Volunteering.VolunteeringID = EventSponsors.VolunteeringID
GROUP BY Contacts.ContactID, Contacts.LastName, Contacts.FirstName;


To see Contacts with Spouses:
SELECT DISTINCT Contacts.ContactID, Sum(Volunteering.ITABucks) AS
SumOfITABucks, Contacts.LastName, Contacts.FirstName,
Contacts.SignificantOtherID, Sum(Volunteering.ITABucks) AS sumofbucks
FROM (((Event LEFT JOIN Animals ON Event.AnimalsID = Animals.AnimalsID)
INNER JOIN Volunteering ON Event.VolunteeringID =
Volunteering.VolunteeringID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) INNER JOIN (Organizations INNER JOIN EventSponsors ON
Organizations.OrganizationID = EventSponsors.OrganizationID) ON
Volunteering.VolunteeringID = EventSponsors.VolunteeringID
GROUP BY Contacts.ContactID, Contacts.LastName, Contacts.FirstName,
Contacts.SignificantOtherID
HAVING (((Contacts.SignificantOtherID) Is Not Null));

This 2nd query finds all Contact/Spouses and shows their individual Bucks,
but I can't figure out how to add them together. No doubt, the issue at the
end will be which record to store the total on, since I don't want both
partners to have that many bucks. One step at a time! Thanks for your time.

 

[David S via AccessMonster.com]

Hi Stephanie,

I don't think it's too bad to work through the steps to solve your specific
question, but as you point out at the end of your post, it might not cater
for your ultimate needs - in these "family" situations, can the children also
be involved?

I ask because you could solve it with the tables as is, or you could create a
new table based around families that you then use to group all this stuff
together - the "SignificantOtherID" in the Contacts table then becomes
obsolete, as you would define a family by adding a record to Families, whic
looks like:
FamilyID, ContactID, Role (eg. husband, wife, son, daughter, whatever)

It would also help if you could post the fields for all the tables involved:
I like to create those same tables in my own Access database so I can try out
some queries, and it's a lot harder if I have to reverse engineer them from
your queries...

 

[Stephanie]

David,
Thanks for the reply! I'd like to avoid creating a new table structure. I
realize that the method I'm using is "down and dirty" but there are so few
instances that I can't see restructuring. You are correct that there may be
1 or 2 instances where kids are involved, so let's think of SigificantOtherID
as FamilyID, but refer to it as SigificantOtherID. And if there are more
than 2 family members, it will have to become a manual process!

I've included my tables:
Contacts
ContactID (PK)
LastName
FirstName
SignificantOtherID

Event
EventVolunteerID (PK)
ContactID
VolunteeringID
AnimalsID

Animals
AnimalsID (PK)
AnimalsName

Volunteering
VolunteeringID (PK)
VolunteeringDate
ITABucks

Organizations
OrganizationID (PK)
OrganziationName

EventSponsors
OrganizationID (PK)
VolunteeringID (PK)

It goes like this: Contacts certify with their/someone else's Animal(s).
These Contacts (either with/without Animals) Volunteer for an Event put on by
an Organization. Each Volunteer opportunity offers a certain amount of
ITABucks. So I am able to sum the ITABucks for each Contact, based on what
they earned for all the Volunteer opportunities. What I want to do is add
together, for each ContactID with a SignificantOtherID, the sums (ContactID
ITABucks sum + SignificantOtherID ITABucks sum). Thanks for taking the time-
I appreciate the help!

 

[David S via AccessMonster.com]

David,
Thanks for the reply! I'd like to avoid creating a new table structure. I
realize that the method I'm using is "down and dirty" but there are so few
instances that I can't see restructuring.

OK, no problems - just wanted to make sure you were aware of it and making a
conscious decision about it, rather than stumbling into it later

I've created the tables and chucked some data into them, and the second query
isn't actually returning the Bucks for the spouse - it's returning the Bucks
for the Contact a second time. What you need to do to get the Bucks for the
spouse is add the table again. Because there are so many joins, I personally
think you're best off using your first query.

So, let's add SignificantOtherID to your BucksByContact query:
SELECT DISTINCT Contacts.ContactID, Sum(Volunteering.ITABucks) AS
SumOfITABucks, Contacts.LastName, Contacts.FirstName, Contacts.
SignificantOtherID
FROM (((Event LEFT JOIN Animals ON Event.AnimalsID = Animals.AnimalsID) INNER
JOIN Volunteering ON Event.VolunteeringID = Volunteering.VolunteeringID)
INNER JOIN Contacts ON Event.ContactID = Contacts.ContactID) INNER JOIN
(Organizations INNER JOIN EventSponsors ON Organizations.OrganizationID =
EventSponsors.OrganizationID) ON Volunteering.VolunteeringID = EventSponsors.
VolunteeringID
GROUP BY Contacts.ContactID, Contacts.LastName, Contacts.FirstName, Contacts.
SignificantOtherID;

This shows us the Contacts and their Spouse. We can then join this query to
itself, connecting SignificantOtherID with ContactID, to get the totals we
need. The main caution here is that we need to use a LEFT JOIN instead of an
INNER JOIN - otherwise, we would only get those Contacts who had spouses. The
SQL for BucksByContactWithSpouse looks like this:
SELECT BuckByContact.ContactID, BuckByContact.SumOfITABucks, BuckByContact.
LastName, BuckByContact.FirstName, BuckByContact.SignificantOtherID,
BuckBySpouse.LastName, BuckBySpouse.FirstName, BuckBySpouse.SumOfITABucks
FROM BuckByContact LEFT JOIN BuckByContact AS BuckBySpouse ON BuckByContact.
SignificantOtherID = BuckBySpouse.ContactID;

Now that you have the two columns, you can add them together. I always find
it a bit tricky adding columns that are calculated, so I like to use another
query, like BucksByFamily. Note that adding Null to anything results in a
Null, so the query has to convert these to zeros. Also, I've made the column
names something easier to reference:
SELECT BucksByContactWithSpouse.ContactID, BucksByContactWithSpouse.
BuckByContact.LastName AS ContactLastName, BucksByContactWithSpouse.
BuckByContact.FirstName AS ContactFirstName, BucksByContactWithSpouse.
BuckByContact.SumOfITABucks AS ContactBucks, BucksByContactWithSpouse.
SignificantOtherID AS SpouseID, IIf([BuckBySpouse].[LastName] Is Null,"",
[BuckBySpouse].[LastName]) AS SpouseLastName, IIf([BuckBySpouse].[FirstName]
Is Null,"",[BuckBySpouse].[FirstName]) AS SpouseFirstName, IIf([BuckBySpouse].
[SumOfITABucks] Is Null,0,[BuckBySpouse].[SumOfITABucks]) AS SpouseBucks,
[ContactBucks]+[SpouseBucks] AS FamilyBucks
FROM BucksByContactWithSpouse;

Let us know how that goes for you.

Cheers,
David...

 

[Stephanie]

Wow! You are a talent bucket! I panic when I have to string two queries
together, and I can't believe that you set up my database and worked out the
PERFECT 3 query solution. Completely fantastic! Thank you.

So now I press my luck on your goodwill, if you have time.
We use the Bucks to help defray the cost of our membership. Family
memberships are, let's say, $100 and if a Contact and a Spouse have Family
Bucks of 34 then they only need to pay $66. After they pay $66 cash, their
individual Bucks are both 0 again. For Individuals, memberships are, let's
say, $75 and you can use your individual Bucks to defray the cost, with Bucks
returning to 0. Individual members can only defray $35, and Family members
(combined) can only defray $45.

I set up a simple A/R, A/P table structure that works (I'm embarrassed to
tell you how very long it took me to set up!). So I have a form based on Dues
with a subform for Payments. I also devised a different method for assigning
Bucks (it's complicated- the Event/Volunteering method requires the user to
set up a specific event on a specific day and I can't see wanting to do that
everytime someone cashes in Bucks). The difficulty I had was in adding these
negative Bucks into the mix, along with the Contact/Spouse bucks addition
(that you fixed!), keeping the 'cashing-in' at the given limit and reseting
bucks to zero. Sigh. I have code that (sometimes) works but needs to be
adjusted based on the new query names...

If I haven't scared you off, here's the other tables and additional fields
to tables you have:
Contacts
ContactID
SpouseID...
MemberCategoryID

Volunteering (-same stuff as before-)
VolunteeringID
ITABucks

MemberCategory
MemberCategoryID
MemberType (Individual, Family)
CategoryDues ($75, $100)

DuesItemType
DuesItemTypeID
DuesItemType (membership dues)

DuesLineItem
DuesItemID
DuesItemTypeID
ContactID
AmountDue

PaymentsLineItem (also have a Payment Type table, no biggie)
PaymentItemID
DuesItemID
Amount

Bucks
BucksID
BucksEvent ("cash-in" bucks; I have this in case the user decides that the
whole Volunteering/Event ITABucks assignment is too hard and wants 'down and
dirty' bucks assigning on top of "cash-in" bucks assigning)

BucksMember
BucksMemberID
ActivityDate
ContactID
BucksID
Bucks (this is the cash-in amount, with family at 45 limit and individual at
35 limit)

Any ideas of how I can show what the Contact/Spouse owe without both of them
owing it? How to throw Bucks (cash-in) into the BuckFamily calculation, and
then reset Contact/Spouse ITABucks? How to handle the limit issue?

Hope I haven't chased you away or dumped too much. I appreciate the help
you've provided (I would NEVER have figured it out), and hope you can give me
more guidance. Cheers!

David,
Thanks for the reply! I'd like to avoid creating a new table structure. I
realize that the method I'm using is "down and dirty" but there are so few
instances that I can't see restructuring.

OK, no problems - just wanted to make sure you were aware of it and making a
conscious decision about it, rather than stumbling into it later

I've created the tables and chucked some data into them, and the second query
isn't actually returning the Bucks for the spouse - it's returning the Bucks
for the Contact a second time. What you need to do to get the Bucks for the
spouse is add the table again. Because there are so many joins, I personally
think you're best off using your first query.

So, let's add SignificantOtherID to your BucksByContact query:
SELECT DISTINCT Contacts.ContactID, Sum(Volunteering.ITABucks) AS
SumOfITABucks, Contacts.LastName, Contacts.FirstName, Contacts.
SignificantOtherID
FROM (((Event LEFT JOIN Animals ON Event.AnimalsID = Animals.AnimalsID) INNER
JOIN Volunteering ON Event.VolunteeringID = Volunteering.VolunteeringID)
INNER JOIN Contacts ON Event.ContactID = Contacts.ContactID) INNER JOIN
(Organizations INNER JOIN EventSponsors ON Organizations.OrganizationID =
EventSponsors.OrganizationID) ON Volunteering.VolunteeringID = EventSponsors.
VolunteeringID
GROUP BY Contacts.ContactID, Contacts.LastName, Contacts.FirstName, Contacts.
SignificantOtherID;

This shows us the Contacts and their Spouse. We can then join this query to
itself, connecting SignificantOtherID with ContactID, to get the totals we
need. The main caution here is that we need to use a LEFT JOIN instead of an
INNER JOIN - otherwise, we would only get those Contacts who had spouses. The
SQL for BucksByContactWithSpouse looks like this:
SELECT BuckByContact.ContactID, BuckByContact.SumOfITABucks, BuckByContact.
LastName, BuckByContact.FirstName, BuckByContact.SignificantOtherID,
BuckBySpouse.LastName, BuckBySpouse.FirstName, BuckBySpouse.SumOfITABucks
FROM BuckByContact LEFT JOIN BuckByContact AS BuckBySpouse ON BuckByContact.
SignificantOtherID = BuckBySpouse.ContactID;

Now that you have the two columns, you can add them together. I always find
it a bit tricky adding columns that are calculated, so I like to use another
query, like BucksByFamily. Note that adding Null to anything results in a
Null, so the query has to convert these to zeros. Also, I've made the column
names something easier to reference:
SELECT BucksByContactWithSpouse.ContactID, BucksByContactWithSpouse.
BuckByContact.LastName AS ContactLastName, BucksByContactWithSpouse.
BuckByContact.FirstName AS ContactFirstName, BucksByContactWithSpouse.
BuckByContact.SumOfITABucks AS ContactBucks, BucksByContactWithSpouse.
SignificantOtherID AS SpouseID, IIf([BuckBySpouse].[LastName] Is Null,"",
[BuckBySpouse].[LastName]) AS SpouseLastName, IIf([BuckBySpouse].[FirstName]
Is Null,"",[BuckBySpouse].[FirstName]) AS SpouseFirstName, IIf([BuckBySpouse].
[SumOfITABucks] Is Null,0,[BuckBySpouse].[SumOfITABucks]) AS SpouseBucks,
[ContactBucks]+[SpouseBucks] AS FamilyBucks
FROM BucksByContactWithSpouse;

Let us know how that goes for you.

Cheers,
David...

 

[David S via AccessMonster.com]

Any ideas of how I can show what the Contact/Spouse owe without both of them

owing it?

With your current table structure, the only way you're going to achive this
is by adding a new Yes/No column to Contacts called something like
PrimaryMember, so that we can determine who the primary member in a Contact /
Spouse relationship is. However, I also note this:

We use the Bucks to help defray the cost of our membership. Family
memberships are, let's say, $100 and if a Contact and a Spouse have Family
Bucks of 34 then they only need to pay $66. After they pay $66 cash, their
individual Bucks are both 0 again.

If a Family membership is $100, how much do each of the Contacts in that
family owe? I suppose we could assume a 50/50 split so that both owe $50 for
their membership, but then does it matter if one of them pays $25 and the
other pays $75?

I know you want to avoid creating a new table structure, but in this case I
really think it would be worth the effort to do so, because that will make
handling the above situation so much easier. Yes, it will be a bit more work
to set up - instead of just setting up a Contact and going from there, you
will need to set up a MembershipGroup and then Contacts within it. But it
would make this so much easier, and given that you've actually done a pretty
good job with your other table layouts, might not be as much work as you fear.


Membership table
MembershipID (PK)
MemberCategoryID (link to MemberCategory table)

Contacts table
(add MembershipID)
(MemberCategoryID becomes obsolete, but don't delete yet - we can use the
data to update our new table structures)
(SignificantOtherID also becomes obsolete, but don't delete yet, for the same
reasons)

Every Contact must have a MembershipID, even if they don't have Family
Membership.

Populating the fields in these tables is actually pretty easy, although
you'll need to go through the families to make some amendments. Although
potentially confusing, you could make the default MembershipID the same as
the ContactID, but you may be better off adding an M to the front of the
ContactID or, if it's a number, adding 1000 (if you have fewer than 1000
contacts in the table already) or 10000 (if you have up to 9999 contacts).
Assuming the former and that 1 = Individual and 2 = Family in MemberCategory:
AddIndividuals:
INSERT INTO Membership ( MembershipID, MemberCategoryID )
SELECT [ContactID]+1000 AS MembershipID, 2 AS MemberCategoryID
FROM Contacts
WHERE (((Contacts.SignificantOtherID) Is Null Or (Contacts.SignificantOtherID)
=0));

AddFamilies:
INSERT INTO Membership ( MembershipID, MemberCategoryID )
SELECT [ContactID]+1000 AS MembershipID, 2 AS MemberCategoryID
FROM Contacts
WHERE (((Contacts.SignificantOtherID) Is Not Null And (Contacts.
SignificantOtherID)<>0));

UpdateContacts:
UPDATE Contacts SET Contacts.MembershipID = [ContactID]+1000;

Then, the ones you need to check are shown in MembershipCheck:
SELECT Contacts.ContactID, Contacts.LastName, Contacts.FirstName, Contacts.
SignificantOtherID, Contacts.MembershipID, Membership.MemberCategoryID
FROM Contacts INNER JOIN Membership ON Contacts.MembershipID = Membership.
MembershipID
WHERE (((Membership.MemberCategoryID)=2));

Unfortunately, you can't just update the MembershipID in the query results -
or I can't, anyway. But at least it will tell you which ones need to be
changed.

 

[David S via AccessMonster.com]

(Alternatively, you may want to skip the whole creation of members for
families if there aren't too many, as it may be easier to add them manually
than to do sort out things afterwards, which will also include deleting or
reusing or something the redundant MembershipIDs that will get created...)

Anyway, now you can get BucksByMember with a slight variation on your
original query:
SELECT DISTINCT Membership.MembershipID, Sum(Volunteering.ITABucks) AS
SumOfITABucks
FROM Membership INNER JOIN ((((Event LEFT JOIN Animals ON Event.AnimalsID =
Animals.AnimalsID) INNER JOIN Volunteering ON Event.VolunteeringID =
Volunteering.VolunteeringID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) INNER JOIN (Organizations INNER JOIN EventSponsors ON
Organizations.OrganizationID = EventSponsors.OrganizationID) ON Volunteering.
VolunteeringID = EventSponsors.VolunteeringID) ON Membership.MembershipID =
Contacts.MembershipID
GROUP BY Membership.MembershipID;

You can then achieve your original query by linking it up with Contacts to
get BucksByMemberContacts:
SELECT BucksByMembership.MembershipID, Contacts.ContactID, Contacts.LastName,
Contacts.FirstName, BucksByMembership.SumOfITABucks
FROM BucksByMembership INNER JOIN Contacts ON BucksByMembership.MembershipID
= Contacts.MembershipID;

Now you have a much better shot at writing some queries to do what you want
to do with the accumulation and so on. It would still be tricky reducing the
Bucks balance earned by the individual Conacts, because if they have
accumulated more than they can spend, from whom do you deduct it first? It
may be better to consolidate the individual ContactBucks into a
MembershipBucks table, and just adding to it... have to think about that a
bit more.

Let me know if you don't want to do this any more, and I'll stop...

 

[Stephanie]

Thanks! I'm more worried that you'll want to stop!

I REALLY don't want to add a new table for the memberships- here's why:
I have 1700 Contacts, most are simply donors who aren't involved in this
Bucks thing. I have 350 true Bucks members and of those I can only think of
less than 5 who are affected by the SpouseID/ContactID Bucks thing.
Cashing-in of Bucks is a manual operation, so we can ask how many Bucks to
take from each Contact but I can't imagine the Contacts would really care in
the long run. I also have a plethera of complicated queries based on
SpouseID, throwing in Address1 and Address2 depending on where those lucky
folks are living, to get mailing labels that are addressed to both spouses...
I know. I'm a whiner.

What I had done before (still working out the details) was create a new
form, BucksMember, which has the Date, Activity, and the (negative)
BucksNumber. I added Bucks all together for each Contact (ITABucks +
BucksNumber) using code that still has issues, because I am even worse at vb
than sql (I know, it is hard to believe).

Private Sub BucksNumber_BeforeUpdate(Cancel As Integer)
'Use two DSum() expressions to get the total, e.g.:
Dim strWhere As String
Dim curTotal As Currency
Dim strMsg As String 'MsgBox message.
Dim bWarn As Boolean 'Flag to warn user.
Dim curBucksNumber As Currency
Dim curITABucks As Currency
Dim curAbsBucksNumber As Currency

strWhere = "[ContactID] = " & Nz([ContactID], 0)

curBucksNumber = Nz(DSum("BucksNumber", "BucksMember", strWhere), 0)
curITABucks = Nz(DLookup("SumOfITABucks", "ITABucks", strWhere), 0)
curTotal = curBucksNumber + curITABucks
curAbsBucksNumber = Abs(Me!BucksNumber)

If ((curTotal) < (curAbsBucksNumber)) Or ((Me!BucksNumber) < -30) Then
Cancel = True
strMsg = strMsg & "Member does not have enough Bucks to 'cash in'
that many." & vbCrLf
End If
Call CancelOrWarn(Cancel, bWarn, strMsg) 'used to deliver msg
End Sub

I realize that this method doesn't answer all the issues (particularly
having the Spouse showing as owing the membership fee as well- maybe I could
add a membership type of 'Family- no charge' with a 0$ membership fee for the
Spouse?)

Anyway, what do you think? Would you be able to help me with this alternate
plan? Thanks for your patience!

 

[David S via AccessMonster.com]

Anyway, what do you think?
I can't say that I'm enamoured of the idea, as the difficulties it creates
seems to make things messier than they ought to be - when we start
introducing odd things like the $0 family membership for the other members of
the family, it won't take much for a family to be charged twice or not at all
- even though it might be fine intitially, I feel that over time this sort of
thing has a way of coming back to bite you, especially if you don't have
anything to do with the code for 6 months.

What if I were to come up with something that didn't affect all your contacts,
but just the ones who were part of the Bucks scheme, including families? It
would still require a new table, but wouldn't have much impact on the things
you've already done...

 

[Stephanie]

OK, then. Sounds like it's best to just do it correctly. Sigh. If we just
add a table for the ones impacted, no doubt we'll have a whole slew of new
family members!

So, let me play with your back-to-back posts (I'll save a nice copy of what
I have now) and add the suggested tables. The membership dues thing will be
great to have working!

I'll post when I'm up to speed.
Here's the queries that I'll need to update without the SpouseID thing (they
may not seem complicated to you, but to me really difficult so Ken from the
group wrote them for me).
Labels by Ken:
SELECT Nz(c1.NickName,c1.FirstName) & " " & c1.LastName AS [Member Name],
(SELECT Nz(c2.[NickName],c2.[FirstName]) & " " & c2.[LastName] AS [SONm] FROM
Contacts c2 WHERE c2.ContactID = Nz(c1.SignificantOtherID,-9999)) AS [SO
Name], Nz(c1.CompanyName," ") AS [Company Name],
Nz(c1.ContactNickName,c1.ContactFirstName) & " " & c1.ContactLastName AS
ContactName, IIf([AddressFlag]=1,c1.MailingAddress1 & ",
"+c1.OptAddress1,c1.MailingAddress2 & ", "+c1.OptAddress2) AS Address,
IIf([AddressFlag]=1,c1.City1,c1.City2) AS City,
IIf([AddressFlag]=1,UCase(c1.StateOrProvince1),UCase(c1.StateOrProvince2)) AS
State, IIf([AddressFlag]=1,c1.PostalCode1,c1.PostalCode2) AS PostalCode,
c1.SignificantOtherID, c1.Hydrant
FROM Contacts AS c1
WHERE (((c1.SignificantOtherID)>c1.ContactID)) Or (((c1.SignificantOtherID)
Is Null) And ((c1.Hydrant)=Yes));

AND
SELECT (IIf(Len(Trim(LK.[Company Name]) & "")=0,IIf(Len(Trim(LK.[SO Name]) &
"")=0,Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.Address) & Chr(13)
& Chr(10) & Trim(LK.City & ", " & LK.State & " " & LK.PostalCode) & Chr(13)
& Chr(10),Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.[SO Name]) &
Chr(13) & Chr(10) & Trim(LK.Address) & Chr(13) & Chr(10) & Trim(LK.City & ",
" & LK.State & " " & LK.PostalCode)),Trim(LK.[Member Name]) & Chr(13) &
Chr(10) & Trim(LK.[Company Name]) & Chr(13) & Chr(10) & Trim(LK.Address) &
Chr(13) & Chr(10) & Trim(LK.City & ", " & LK.State & " " & LK.PostalCode)))
AS MyLabelContents
FROM [Labels by Ken Hydrant] AS LK;

Thanks for all the posts- I appreciate the technical assist and that you are
so nice in your posts.
Cheers!

 

[Stephanie]

Okay. I'm doing pretty well now. I've added the tables and fixed all of the
Membership issues so that my Family people have a Membership.MemberCategoryID
= 2.

I'm still struggling to get the queries back to where I think they need to
be. You originally wrote 3 queries.

I did this one correctly: BuckByMembership (shows all Members with their
individual Bucks)
SELECT DISTINCT Membership.MembershipID, Membership.MemberCategoryID,
Sum(Volunteering.ITABucks) AS SumOfITABucks, Contacts.LastName,
Contacts.FirstName, Contacts.SignificantOtherID
FROM ((((Event LEFT JOIN Animals ON Event.AnimalsID = Animals.AnimalsID)
INNER JOIN Volunteering ON Event.VolunteeringID =
Volunteering.VolunteeringID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) INNER JOIN (Organizations INNER JOIN EventSponsors ON
Organizations.OrganizationID = EventSponsors.OrganizationID) ON
Volunteering.VolunteeringID = EventSponsors.VolunteeringID) INNER JOIN
Membership ON Contacts.MembershipID = Membership.MembershipID
GROUP BY Membership.MembershipID, Membership.MemberCategoryID,
Contacts.LastName, Contacts.FirstName, Contacts.SignificantOtherID,
Contacts.ContactID;

Then the original 2nd query you wrote, BucksByContactWithSpouse brings back
all Contacts with their Bucks, plus for Spouses it shows the opposite Bucks
(John shows 13 of his own and 21 of Mary's in a separate column):
SELECT BuckByContact.ContactID, BuckByContact.SumOfITABucks,
BuckByContact.LastName, BuckByContact.FirstName,
BuckByContact.SignificantOtherID, BuckBySpouse.LastName,
BuckBySpouse.FirstName, BuckBySpouse.SumOfITABucks
FROM BuckByContact LEFT JOIN BuckByContact AS BuckBySpouse ON
BuckByContact.SignificantOtherID = BuckBySpouse.ContactID;

Do I still need this one fixed for Membership? I couldn't get it right and
would appreciate help.

Then the 3rd query, BucksByFamily shows the individual's Bucks, the Spouses'
Bucks and then adds the 2 columns together to get FamilyBucks:
SELECT BucksByContactWithSpouse.ContactID,
BucksByContactWithSpouse.BuckByContact.LastName AS ContactLastName,
BucksByContactWithSpouse.BuckByContact.FirstName AS ContactFirstName,
BucksByContactWithSpouse.BuckByContact.SumOfITABucks AS ContactBucks,
BucksByContactWithSpouse.SignificantOtherID AS SpouseID,
IIf([BuckBySpouse].[LastName] Is Null,"",[BuckBySpouse].[LastName]) AS
SpouseLastName, IIf([BuckBySpouse].[FirstName] Is
Null,"",[BuckBySpouse].[FirstName]) AS SpouseFirstName,
IIf([BuckBySpouse].[SumOfITABucks] Is Null,0,[BuckBySpouse].[SumOfITABucks])
AS SpouseBucks, [ContactBucks]+[SpouseBucks] AS FamilyBucks
FROM BucksByContactWithSpouse;

I couldn't get the 3rd query going without the 2nd query, and well probably
not at all. I'd appreciate help. I'm still trying to get the hang of
SpouseID now essentially being MembershipID. And I'm trying to have faith
that it will all be easier going forward...

Thanks for the step by step.

 

[David S via AccessMonster.com]

OK, then. Sounds like it's best to just do it correctly. Sigh. If we just

add a table for the ones impacted, no doubt we'll have a whole slew of new
family members!

AAAAHHH! And I just spent a chunk of my weekenbd thinking about how to make
it work

The idea I came up with was to add a single column to your Contacts table - a
checkbox called PrimaryFamilyMember. That way, you could stick with your
existing queries as much as possible, and you'd only need to update it for
the 5 or so family memberships that you actually have.

Using this method, it doesn't matter what sort of membership you assign to
the spouse (ie. those Contacts with a SpouseID but where PrimaryFamilyMember
= false), because we just ignore it - what's important is the membership type
of the main contact. Adding this to our original three queries gives:

BucksByContact:
SELECT DISTINCT Contacts.ContactID, Sum(Volunteering.ITABucks) AS
SumOfITABucks, Contacts.LastName, Contacts.FirstName, Contacts.
SignificantOtherID, Contacts.PrimaryFamilyMember
FROM (((Event LEFT JOIN Animals ON Event.AnimalsID = Animals.AnimalsID) INNER
JOIN Volunteering ON Event.VolunteeringID = Volunteering.VolunteeringID)
INNER JOIN Contacts ON Event.ContactID = Contacts.ContactID) INNER JOIN
(Organizations INNER JOIN EventSponsors ON Organizations.OrganizationID =
EventSponsors.OrganizationID) ON Volunteering.VolunteeringID = EventSponsors.
VolunteeringID
GROUP BY Contacts.ContactID, Contacts.LastName, Contacts.FirstName, Contacts.
SignificantOtherID, Contacts.PrimaryFamilyMember;

BucksByContactwithSpouse:
SELECT BuckByContact.ContactID, BuckByContact.SumOfITABucks, BuckByContact.
LastName, BuckByContact.FirstName, BuckByContact.SignificantOtherID,
BuckBySpouse.LastName, BuckBySpouse.FirstName, BuckBySpouse.SumOfITABucks
FROM BuckByContact LEFT JOIN BuckByContact AS BuckBySpouse ON BuckByContact.
SignificantOtherID = BuckBySpouse.ContactID
WHERE (((BuckByContact.SignificantOtherID)<>0) AND ((BuckByContact.
PrimaryFamilyMember)=True)) OR (((BuckByContact.SignificantOtherID)=0));

BucksByFamily:
SELECT BucksByContactWithSpouse.ContactID, BucksByContactWithSpouse.
BuckByContact.LastName AS ContactLastName, BucksByContactWithSpouse.
BuckByContact.FirstName AS ContactFirstName, BucksByContactWithSpouse.
BuckByContact.SumOfITABucks AS ContactBucks, BucksByContactWithSpouse.
SignificantOtherID AS SpouseID, nz(BuckBySpouse.LastName, "") AS
SpouseLastName, nz(BuckBySpouse.FirstName, "") AS SpouseFirstName, nz
(BuckBySpouse.SumOfITABucks, 0) AS SpouseBucks, [ContactBucks]+[SpouseBucks]
AS FamilyBucks
FROM BucksByContactWithSpouse;

(PS. I've learned about the NZ function just recently - very useful!)

 

[David S via AccessMonster.com]

See my reply to your previous post...

OK, with the last set of queries, I forgot to add in the MemberCategryID - we
can either link it in at the last stage, or change the earlier queries to
carry it through:

BucksByContact:
SELECT DISTINCT Contacts.ContactID, Sum(Volunteering.ITABucks) AS
SumOfITABucks, Contacts.LastName, Contacts.FirstName, Contacts.
SignificantOtherID, Contacts.PrimaryFamilyMember, Contacts.MemberCategoryID
FROM (((Event LEFT JOIN Animals ON Event.AnimalsID = Animals.AnimalsID) INNER
JOIN Volunteering ON Event.VolunteeringID = Volunteering.VolunteeringID)
INNER JOIN Contacts ON Event.ContactID = Contacts.ContactID) INNER JOIN
(Organizations INNER JOIN EventSponsors ON Organizations.OrganizationID =
EventSponsors.OrganizationID) ON Volunteering.VolunteeringID = EventSponsors.
VolunteeringID
GROUP BY Contacts.ContactID, Contacts.LastName, Contacts.FirstName, Contacts.
SignificantOtherID, Contacts.PrimaryFamilyMember, Contacts.MemberCategoryID;

BucksByContactWithSpouse:
SELECT BuckByContact.ContactID, BuckByContact.SumOfITABucks, BuckByContact.
LastName, BuckByContact.FirstName, BuckByContact.SignificantOtherID,
BuckBySpouse.LastName, BuckBySpouse.FirstName, BuckBySpouse.SumOfITABucks,
BuckByContact.MemberCategoryID
FROM BuckByContact LEFT JOIN BuckByContact AS BuckBySpouse ON BuckByContact.
SignificantOtherID = BuckBySpouse.ContactID
WHERE (((BuckByContact.SignificantOtherID)<>0) AND ((BuckByContact.
PrimaryFamilyMember)=True)) OR (((BuckByContact.SignificantOtherID)=0));

BucksByFamily:
SELECT BucksByContactWithSpouse.ContactID, BucksByContactWithSpouse.
BuckByContact.LastName AS ContactLastName, BucksByContactWithSpouse.
BuckByContact.FirstName AS ContactFirstName, BucksByContactWithSpouse.
BuckByContact.SumOfITABucks AS ContactBucks, BucksByContactWithSpouse.
SignificantOtherID AS SpouseID, nz(BuckBySpouse.LastName,"") AS
SpouseLastName, nz(BuckBySpouse.FirstName,"") AS SpouseFirstName, nz
(BuckBySpouse.SumOfITABucks,0) AS SpouseBucks, [ContactBucks]+[SpouseBucks]
AS FamilyBucks, BucksByContactWithSpouse.MemberCategoryID
FROM BucksByContactWithSpouse;

We can now use BucksByFamily as a basis for our other queries. To make things
easier, we should proabbly add a "MaxBucksUsable" to the MemberCategory table:

SELECT BucksByFamily.ContactID, BucksByFamily.ContactLastName, BucksByFamily.
ContactFirstName, BucksByFamily.FamilyBucks, MemberCategory.CategoryDues,
MemberCategory.MaxBucksUsable, IIf([FamilyBucks]<[MaxBucksUsable],
[FamilyBucks],[MaxBucksUsable]) AS SpendableBucks
FROM BucksByFamily INNER JOIN MemberCategory ON BucksByFamily.
MemberCategoryID = MemberCategory.MemberCategoryID;

You could then use this as the basis of some VBA code to do the actual
subtraction...

 

[Stephanie]

David,
Sorry to ruin your weekend ;(
Since you did all that work, I do like the idea of just adding a column for
PrimaryFamilyMember, that way I can use my existing ContactID/SpouseID
queries for mailing labels and still fix the "Bucks" queries. Truly, the
added Membership table was confusing me because it seemed as though the
Contact wasn't linked to their Family member. So, let me get back up to
speed.

Currently, I'm working in a version that doesn't include the Membership
table. I still have the version that does have it, but I've made many
changes to other things. So the question is, with the new
PrimaryFamilyMember check box method, do I still need the Membership table
(and therefore update my current db version)? And I'm a little confused- I
set SpouseID up so that it is only associated with "families". So it seems
to me that the PrimaryFamilyMember check box is the same as SpouseID not
null. What do you think?

Okay, your reply on 8/21 uses PrimaryFamilyMember so I would need to add
this field to the Contacts table, but not use a Membership table. Correct?

Your reply on 8/22 at 12:06 AM (scary!), requires the Membership table, but
no additional field. Correct? Just fix the queries to take into account
MemberCategoryID.

So- which is best? Taking into consideration all of my existing queries,
and where I'd like to go with "Bucks"...

Then I'll get my db back up to speed so we can both be on the same page.
Thanks for all the effort you've put into solving my dilemas!

OK, then. Sounds like it's best to just do it correctly. Sigh. If we just
add a table for the ones impacted, no doubt we'll have a whole slew of new
family members!

AAAAHHH! And I just spent a chunk of my weekenbd thinking about how to make
it work

The idea I came up with was to add a single column to your Contacts table - a
checkbox called PrimaryFamilyMember. That way, you could stick with your
existing queries as much as possible, and you'd only need to update it for
the 5 or so family memberships that you actually have.

Using this method, it doesn't matter what sort of membership you assign to
the spouse (ie. those Contacts with a SpouseID but where PrimaryFamilyMember
= false), because we just ignore it - what's important is the membership type
of the main contact. Adding this to our original three queries gives:

BucksByContact:
SELECT DISTINCT Contacts.ContactID, Sum(Volunteering.ITABucks) AS
SumOfITABucks, Contacts.LastName, Contacts.FirstName, Contacts.
SignificantOtherID, Contacts.PrimaryFamilyMember
FROM (((Event LEFT JOIN Animals ON Event.AnimalsID = Animals.AnimalsID) INNER
JOIN Volunteering ON Event.VolunteeringID = Volunteering.VolunteeringID)
INNER JOIN Contacts ON Event.ContactID = Contacts.ContactID) INNER JOIN
(Organizations INNER JOIN EventSponsors ON Organizations.OrganizationID =
EventSponsors.OrganizationID) ON Volunteering.VolunteeringID = EventSponsors.
VolunteeringID
GROUP BY Contacts.ContactID, Contacts.LastName, Contacts.FirstName, Contacts.
SignificantOtherID, Contacts.PrimaryFamilyMember;

BucksByContactwithSpouse:
SELECT BuckByContact.ContactID, BuckByContact.SumOfITABucks, BuckByContact.
LastName, BuckByContact.FirstName, BuckByContact.SignificantOtherID,
BuckBySpouse.LastName, BuckBySpouse.FirstName, BuckBySpouse.SumOfITABucks
FROM BuckByContact LEFT JOIN BuckByContact AS BuckBySpouse ON BuckByContact.
SignificantOtherID = BuckBySpouse.ContactID
WHERE (((BuckByContact.SignificantOtherID)<>0) AND ((BuckByContact.
PrimaryFamilyMember)=True)) OR (((BuckByContact.SignificantOtherID)=0));

BucksByFamily:
SELECT BucksByContactWithSpouse.ContactID, BucksByContactWithSpouse.
BuckByContact.LastName AS ContactLastName, BucksByContactWithSpouse.
BuckByContact.FirstName AS ContactFirstName, BucksByContactWithSpouse.
BuckByContact.SumOfITABucks AS ContactBucks, BucksByContactWithSpouse.
SignificantOtherID AS SpouseID, nz(BuckBySpouse.LastName, "") AS
SpouseLastName, nz(BuckBySpouse.FirstName, "") AS SpouseFirstName, nz
(BuckBySpouse.SumOfITABucks, 0) AS SpouseBucks, [ContactBucks]+[SpouseBucks]
AS FamilyBucks
FROM BucksByContactWithSpouse;

(PS. I've learned about the NZ function just recently - very useful!)

 

[David S via AccessMonster.com]

Hello again Stephanie,

Sorry about going missing over the last few days - I thoughgt I had posted a
reply, but it seems to have been eaten by AccessMonster - maybe the length of
it caused my log in to time out? Anyways, I've been absent from work for a
couple of days - my 2.5 year old son had his tonsils removed and stayed
overnight in hospital, so I took a day off work to help out at home. I
wouldn't worry about the timestamp of my posts - I'm from Australia, which is
10 hours ahead of GMT, so I'm definitely not up at those hours trying to
answer your quesstions!

Currently, I'm working in a version that doesn't include the Membership
table. I still have the version that does have it, but I've made many
changes to other things. So the question is, with the new
PrimaryFamilyMember check box method, do I still need the Membership table
(and therefore update my current db version)?

No, you won't need this table with the new PrimaryFamilyMember field - which
was the whole point of it

And I'm a little confused- I set SpouseID up so that it is only associated
with "families". So it seems to me that the PrimaryFamilyMember check
box is the same as SpouseID not null. What do you think?

Not quite, because the SpouseID goes both ways ie. the husband's SpouseID
points ot his wife and the wife's points to her husband. However, only one of
these Contacts will be the PrimaryFamilyMember, not both.

Okay, your reply on 8/21 uses PrimaryFamilyMember so I would need to add
this field to the Contacts table, but not use a Membership table. Correct?

Your reply on 8/22 at 12:06 AM (scary!), requires the Membership table, but
no additional field. Correct? Just fix the queries to take into account
MemberCategoryID.

So- which is best? Taking into consideration all of my existing queries,
and where I'd like to go with "Bucks"...

Um, the dates of the replies seems a bit odd, but I think it may have been
because I've replied to a post that was earlier in the thread, rather than
just replying to the latest post - the way AccessMonster is displaying things,
the latest entries are being displayed before that separate branch of the
thread, which is a bit confusing. Use the queries that have
PrimaryFamilyMember and not the ones requiring the Membership table.

As to which is "best", that depends on a bunch of factors. From the now
extensive history this thread now has, I'm pretty sure you'd be best off with
this PrimaryFamilyMember method rather than the creating a new Membership
table.

So, check out those queries and make sure they give you what they say they do
and that you understand them, and then re-post where you want to go from here
- we'll take that as our new & refreshed starting point

 

[David S via AccessMonster.com]

Okay, your reply on 8/21 uses PrimaryFamilyMember so I would need to add

this field to the Contacts table, but not use a Membership table. Correct?

[quoted text clipped - 5 lines]

So- which is best? Taking into consideration all of my existing queries,
and where I'd like to go with "Bucks"...

Um, the dates of the replies seems a bit odd, but I think it may have been
because I've replied to a post that was earlier in the thread, rather than
just replying to the latest post - the way AccessMonster is displaying things,
the latest entries are being displayed before that separate branch of the
thread, which is a bit confusing. Use the queries that have
PrimaryFamilyMember and not the ones requiring the Membership table.

Oops - looking at the thread again, I remember what I did now... yes, the
reply I posted on 22/8 supersedes the one on 21/8, but it doesn't actually
use the Membership table - it was adding the membership categories, not
adding a new table altogether. I posted it at the end of the thread so that
the last reply would appear at the bottom of my display, rather than the
latest entries appearing in the middle of thread. to try and prevent the
confusion from the two branches... and now, I've started posting in the
middle again Use the queries from 22/8, and then see the end of my last
post *phew* maybe we should start a new thread and put a llink referring to
this one in it

 

[Stephanie]

David,
Hope your son is feeling better- having a child in the hospital is stressful
for everyone. This soon-to-be database is for my animal therapy group- one
of our programs is taking qualified dogs and cats to visit sick kids in the
hospital to help make their stay a little less stressful!

Thanks for the posts. I LOVE the PrimaryFamilyMember solution. I can see
that on my non-primary family member, I'm tracking the individual bucks only,
but on the actual primary family member, I'm tracking both the individual and
family bucks. Very slick, very clever! I would have never figured it out.
Thanks so much- I think that is really going to help. As for a new post, of
course I have more questions! I'll post as "Summing a sum2: David S" as a
new question in the Queries. Hope you have time to play with Bucks a bit
more! Cheers!

Okay, your reply on 8/21 uses PrimaryFamilyMember so I would need to add
this field to the Contacts table, but not use a Membership table. Correct?

[quoted text clipped - 5 lines]

So- which is best? Taking into consideration all of my existing queries,
and where I'd like to go with "Bucks"...

Um, the dates of the replies seems a bit odd, but I think it may have been
because I've replied to a post that was earlier in the thread, rather than
just replying to the latest post - the way AccessMonster is displaying things,
the latest entries are being displayed before that separate branch of the
thread, which is a bit confusing. Use the queries that have
PrimaryFamilyMember and not the ones requiring the Membership table.

Oops - looking at the thread again, I remember what I did now... yes, the
reply I posted on 22/8 supersedes the one on 21/8, but it doesn't actually
use the Membership table - it was adding the membership categories, not
adding a new table altogether. I posted it at the end of the thread so that
the last reply would appear at the bottom of my display, rather than the
latest entries appearing in the middle of thread. to try and prevent the
confusion from the two branches... and now, I've started posting in the
middle again Use the queries from 22/8, and then see the end of my last
post *phew* maybe we should start a new thread and put a llink referring to
this one in it