Summing a sum

[Guest]

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...

 

[Guest]

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...

 

[Guest]

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...

 

[Guest]

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!