Summing a sum2:David S

[Stephanie]

Hi, David! Hope you found this continuation post and have time to play...
I'm still loving the PrimaryFamilyMember method. So, I think the next step
(big picture) is to be able to incorporate the FamilyBucks query, the
membership dues, and the "cashed-in" Bucks into consideration so that dues
get shown as paid for the primary member and the bucks family count get
appropriately decreased (hmm, really individual bucks need to get decreased,
but I can't see that it matters which family member gets hit for their bucks-
it will likely be both members that need to cash in and there is no advantage
to "bucks hoarding"), all while keeping in mind that for a family membership
45 bucks can be cashed in but for any other category type membership, only 35
bucks can be cashed in.

Maybe the first thing is to fix the membership dues so that if there is a
PrimaryFamilyMember selected, the dues for the other family member aren't
visible (so that we don't think that both family members owe the $100 family
dues).

Contacts
ContactID
PrimaryFamilyMember
MemberCategoryID (1= Individual; 2 = Family)

DuesLineItem
DuesLineItemID
ContactsID
DuesItemTypeID (1 = Membership Dues)
AmountDue ($75 for Individual, $100 for Family)
DateCreated

DuesItemType
DuesItemType (bad naming!)

PaymentsLineItem
PaymentItemID
DuesItemID
TypeID
Amount
DateRemitted

PaymentTypes
TypeID
Type (1 = cash...., 6= ITA Bucks)

When you select the Member Category, the Dues are populated:
SELECT MemberCategory.MemberCategoryID, MemberCategory.MemberCategory,
Format([CategoryDues],"$0.00") AS DollarAmt
FROM MemberCategory;

How can I modify what I'm doing so that if there is a PrimaryFamilyMember,
the dues are populated only for the primary in the family and not for the
non-primary, but that those without a PrimaryFamilyMember, the dues are
populated. I think for the non-primary in a family relationship, I'd like to
see the Dues field[cboMemCat].column(2) "grayed-out" and have no dollar
value in it. Could you please help me with this, if it makes sense to you.

Thanks for the help!

 

[Stephanie]

Oops! I am having a problem. On my main "Contacts" form, I have created two
subforms: one to show individual bucks (record source: BuckbyContact) and one
to show family bucks (record source: BucksbyFamily). The family bucks is on
the tab that shows membership dues and where we cash-in bucks. For
inexplicable reasons, I assumed that the correct family bucks, as well as
individual bucks for those non-families would show here (even though the
record source doesn't support my thought!)

I'd like 1) the family subform to show individual bucks when there are no
family members involved, which seems silly or 2) to have the individual bucks
subform next to the family bucks subform and have the inappropriate subform
grayed-out and "empty". That way we'll look for "total" bucks (whether
actually totalled for family, or just an individual's "total") in a
consistent space.

Can that be done? I'll post the 2 (really 3) queries since the original
post is so far away! Although I'm guessing that true, enabled and visible
will come to play and that I'll choose the incorrect Event if left to my own
devices! Thanks.

BuckByContact
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 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;

BucksByFamily
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;

 

[David S via AccessMonster.com]

How can I modify what I'm doing so that if there is a PrimaryFamilyMember,

the dues are populated only for the primary in the family and not for the
non-primary, but that those without a PrimaryFamilyMember, the dues are
populated. I think for the non-primary in a family relationship, I'd like to
see the Dues field[cboMemCat].column(2) "grayed-out" and have no dollar
value in it. Could you please help me with this, if it makes sense to you.

I'd think you'd need to check whether SpouseID is null (or 0) or
PrimaryFamilyMember is true, in which case populate the Dues field; otherwise,
grey it out. To be honest, though, I'm not very familiar with this area -
table design and queries I know about, but form creation and operation isn't
something I've done with Access. So I can't really help you with this or with
your other question - sorry Try asking over in the Forms or Forms
Programming forums...

 

[Stephanie]

Fair enough- knowing queries is a gift !
So a query question for you, if you have time.
Since I have the alternate method of using Bucks (the 'cashing-in' method),
I'd like to add the (negative, or in other instances it could be positive)
Bucks into the mix. Unfortunately, the tables are very far away.

Contacts
ContactID
PrimaryFamilyMember
MemberCategoryID (1= Individual; 2 = Family)

DuesLineItem
DuesLineItemID
ContactsID
DuesItemTypeID (1 = Membership Dues)
AmountDue ($75 for Individual, $100 for Family)
DateCreated

DuesItemType
DuesItemType (bad naming!)

PaymentsLineItem
PaymentItemID
DuesItemID
TypeID
Amount (this is the "alternate" Bucks field)
DateRemitted

PaymentTypes
TypeID
Type (1 = cash...., 6= ITA Bucks)

Volunteering
VolunteeringID
ITABucks

Event
ContactID
VolunteeringID


Here's my ITABucksByContact query (using Amount as the alternate Bucks
method):
SELECT Contacts.ContactID, PaymentsLineItem.Amount, Contacts.LastName,
Contacts.FirstName, Contacts.PrimaryFamilyMember,
Contacts.SignificantOtherID, PaymentTypes.Type
FROM PaymentTypes INNER JOIN ((DuesItemType INNER JOIN (Contacts INNER JOIN
DuesLineItem ON Contacts.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) INNER JOIN
PaymentsLineItem ON DuesLineItem.DuesItemID = PaymentsLineItem.DuesItemID) ON
PaymentTypes.TypeID = PaymentsLineItem.TypeID
WHERE (((PaymentTypes.Type)="ITA Bucks"));

How can I add the "Amount" field in PaymentLineItems to the "ITA Bucks"
field in Volunteering for the correct ContactID? When I tried to add the
fields for Amount and PaymentTypes to the mix, I had an ambiguous join. So I
tried the new query separately (as above), but then the total query only
brought back what the above query returned (so there is no adding of negative
Bucks-Amount-, and no Bucks shown for those not involved in negative Bucks).

Here's the 3 original queries again. Can they be modified to include
negative Bucks (Amount) where appropriate? That way, the total Bucks will
show the correct number of Bucks.
Also, since these cashed-in Bucks (Amount) for a Family come from 2 members,
I'm curious how you envision it working- it seems as though the BucksByFamily
would take the cashed-in Bucks into consideration. But what about the
individuals within the Family? I think I would need the individual Bucks
decreased as well?

BuckByContact:
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;

BuckByContactWithSpouse:
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;

Thanks for the query help, if you have time!
Cheers!

How can I modify what I'm doing so that if there is a PrimaryFamilyMember,
the dues are populated only for the primary in the family and not for the
non-primary, but that those without a PrimaryFamilyMember, the dues are
populated. I think for the non-primary in a family relationship, I'd like to
see the Dues field[cboMemCat].column(2) "grayed-out" and have no dollar
value in it. Could you please help me with this, if it makes sense to you.

I'd think you'd need to check whether SpouseID is null (or 0) or
PrimaryFamilyMember is true, in which case populate the Dues field; otherwise,
grey it out. To be honest, though, I'm not very familiar with this area -
table design and queries I know about, but form creation and operation isn't
something I've done with Access. So I can't really help you with this or with
your other question - sorry Try asking over in the Forms or Forms
Programming forums...

 

[David S via AccessMonster.com]

Here's my ITABucksByContact query (using Amount as the alternate Bucks

method):
SELECT Contacts.ContactID, PaymentsLineItem.Amount, Contacts.LastName,
Contacts.FirstName, Contacts.PrimaryFamilyMember,
Contacts.SignificantOtherID, PaymentTypes.Type
FROM PaymentTypes INNER JOIN ((DuesItemType INNER JOIN (Contacts INNER JOIN
DuesLineItem ON Contacts.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) INNER JOIN
PaymentsLineItem ON DuesLineItem.DuesItemID = PaymentsLineItem.DuesItemID) ON
PaymentTypes.TypeID = PaymentsLineItem.TypeID
WHERE (((PaymentTypes.Type)="ITA Bucks"));

Let's start with this - this query works, but it joins some tables we
probably don't need it to. For example, it links to the DuesItemType table,
which we aren't really interested in for this purpose. It also links to the
PaymentTypes table, which is not such a good idea - if the Payment Type
description changes from "ITA Bucks", this query would stop working; we'd be
better off just checking for Payment Type ID 6. And finally, we don't really
need to link it to the Contacts table, since all that does is display the
first and last name - when we use this query later on, we'll already have
that info. Lastly, we only want a total figure for Bucks used, so we need a
GROUP BY clause. With all those changes, ITABucksByContact becomes much
simpler:
SELECT DuesLineItem.ContactID, Sum(PaymentsLineItem.Amount) AS SumOfAmount
FROM DuesLineItem INNER JOIN PaymentsLineItem ON DuesLineItem.DuesItemID =
PaymentsLineItem.DuesItemID
WHERE (((PaymentsLineItem.TypeID)=6))
GROUP BY DuesLineItem.ContactID;

How can I add the "Amount" field in PaymentLineItems to the "ITA Bucks"
field in Volunteering for the correct ContactID? When I tried to add the
fields for Amount and PaymentTypes to the mix, I had an ambiguous join. So I
tried the new query separately (as above), but then the total query only
brought back what the above query returned (so there is no adding of negative
Bucks-Amount-, and no Bucks shown for those not involved in negative Bucks).

Here's the 3 original queries again. Can they be modified to include
negative Bucks (Amount) where appropriate? That way, the total Bucks will
show the correct number of Bucks.

Since the ITABucksByContact query is by ContactID, we'd be best off joining
it to the BucksByContact query. Again, given that BucksByContact has some
complexity in itself, I prefer to create a new query using the two, say
AllBucksByContact:
SELECT BuckByContact.ContactID, BuckByContact.SumOfITABucks AS BucksEarned,
ITABucksByContact.SumOfAmount AS BucksUsed, BuckByContact.LastName,
BuckByContact.FirstName, BuckByContact.SignificantOtherID, BuckByContact.
PrimaryFamilyMember, BuckByContact.MemberCategoryID
FROM BuckByContact LEFT JOIN ITABucksByContact ON BuckByContact.ContactID =
ITABucksByContact.ContactID;

I've renamed the various Bucks columns to better reflect what they mean:
otherwise, we'd have lots of ITABucks fields confusing things. In using the
Left Join here, we are making the assumption that a Contact must earn some
ITA Bucks before they can spend any - if this isn't the case, then we would
need to have an additional query to get those records too and use a UNION
query.

We then need to use this for our next few queries -
AllBucksByContactWithSpouse (with some additional column renaming to make
things a bit easier later on and doing the null comversion straight away):
SELECT AllBucksByContact.ContactID, AllBucksByContact.BucksEarned,
AllBucksByContact.BucksUsed, AllBucksByContact.LastName, AllBucksByContact.
FirstName, AllBucksByContact.SignificantOtherID, AllBucksBySpouse.LastName AS
SpouseLastName, AllBucksBySpouse.FirstName AS SpouseFirstName, nz(
[AllBucksBySpouse].[BucksEarned],0) AS SpouseBucksEarned, nz(
[AllBucksBySpouse].[BucksUsed],0) AS SpouseBucksUsed, AllBucksByContact.
MemberCategoryID
FROM AllBucksByContact LEFT JOIN AllBucksByContact AS AllBucksBySpouse ON
AllBucksByContact.SignificantOtherID = AllBucksBySpouse.ContactID
WHERE (((AllBucksByContact.SignificantOtherID)<>0) AND ((AllBucksByContact.
PrimaryFamilyMember)=True)) OR (((AllBucksByContact.SignificantOtherID)=0));

AllBucksByFamily (now add together the Bucks):
SELECT AllBucksByContactWithSpouse.ContactID, [BucksEarned]+
[SpouseBucksEarned] AS FamilyBucksEarned, [BucksUsed]+[SpouseBucksUsed] AS
FamilyBucksUsed, [FamilyBucksEarned]+[FamilyBucksUsed] AS
FamilyBucksAvailable, AllBucksByContactWithSpouse.LastName,
AllBucksByContactWithSpouse.FirstName, AllBucksByContactWithSpouse.
SignificantOtherID, AllBucksByContactWithSpouse.SpouseLastName,
AllBucksByContactWithSpouse.SpouseFirstName, AllBucksByContactWithSpouse.
MemberCategoryID
FROM AllBucksByContactWithSpouse;

Also, since these cashed-in Bucks (Amount) for a Family come from 2 members,
I'm curious how you envision it working- it seems as though the BucksByFamily
would take the cashed-in Bucks into consideration. But what about the
individuals within the Family? I think I would need the individual Bucks
decreased as well?

Probably the key question here is whether you want to store separately how
many Bucks are available, as opposed to calculating it every time. The
advantage of the latter is that you can get to the figure without having to
go through a whole bunch of queries, which could be time consuming as well as
a bit tricky to set up. The disadvantage is that you would have to run an
update at some point to update the total in that query. Given the volume
involved, I'd prefer to use the queries. Either way, you're going to need to
be careful about how you go about restricting the number of bucks people can
redeem at any one point in time - neither approach helps with that issue...
you also have to consider whether, for a family, they can cash in their
partner's Bucks at the same time. From an end user point of view, I'd think
they would expect to be able to do so, which adds an additional levle of
complexity - either we allow an individual contact to spend more bucks than
they earn, or we have some logic to start subtracting from the spouse's
balance once their own Bucks have all been spent...

 

[Stephanie]

Thank you so much! In my struggles, I managed to slop together something,
but the streamlined approach is much better! And I must remember to use AS
in my queries- much easier to follow along.

As a last Bucks query question (I hope!), on ITABucksByContact query I'd
like to add together Bucks Earned and Bucks Used (because I think I'm going
to end up cashing in Bucks per Individual...). This is the part that was so
difficult for me. Thanks.

ITABucksByContact:
SELECT BuckByContact.ContactID, BuckByContact.SumOfITABucks AS BucksEarned,
ITABucksByContact.SumOfAmount AS BucksUsed, BuckByContact.LastName,
BuckByContact.FirstName, BuckByContact.SignificantOtherID,
BuckByContact.PrimaryFamilyMember
FROM BuckByContact LEFT JOIN ITABucksByContact ON BuckByContact.ContactID =
ITABucksByContact.ContactID;

Here's my ITABucksByContact query (using Amount as the alternate Bucks
method):
SELECT Contacts.ContactID, PaymentsLineItem.Amount, Contacts.LastName,
Contacts.FirstName, Contacts.PrimaryFamilyMember,
Contacts.SignificantOtherID, PaymentTypes.Type
FROM PaymentTypes INNER JOIN ((DuesItemType INNER JOIN (Contacts INNER JOIN
DuesLineItem ON Contacts.ContactID = DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID = DuesLineItem.DuesItemTypeID) INNER JOIN
PaymentsLineItem ON DuesLineItem.DuesItemID = PaymentsLineItem.DuesItemID) ON
PaymentTypes.TypeID = PaymentsLineItem.TypeID
WHERE (((PaymentTypes.Type)="ITA Bucks"));

Let's start with this - this query works, but it joins some tables we
probably don't need it to. For example, it links to the DuesItemType table,
which we aren't really interested in for this purpose. It also links to the
PaymentTypes table, which is not such a good idea - if the Payment Type
description changes from "ITA Bucks", this query would stop working; we'd be
better off just checking for Payment Type ID 6. And finally, we don't really
need to link it to the Contacts table, since all that does is display the
first and last name - when we use this query later on, we'll already have
that info. Lastly, we only want a total figure for Bucks used, so we need a
GROUP BY clause. With all those changes, ITABucksByContact becomes much
simpler:
SELECT DuesLineItem.ContactID, Sum(PaymentsLineItem.Amount) AS SumOfAmount
FROM DuesLineItem INNER JOIN PaymentsLineItem ON DuesLineItem.DuesItemID =
PaymentsLineItem.DuesItemID
WHERE (((PaymentsLineItem.TypeID)=6))
GROUP BY DuesLineItem.ContactID;

How can I add the "Amount" field in PaymentLineItems to the "ITA Bucks"
field in Volunteering for the correct ContactID? When I tried to add the
fields for Amount and PaymentTypes to the mix, I had an ambiguous join. So I
tried the new query separately (as above), but then the total query only
brought back what the above query returned (so there is no adding of negative
Bucks-Amount-, and no Bucks shown for those not involved in negative Bucks).

Here's the 3 original queries again. Can they be modified to include
negative Bucks (Amount) where appropriate? That way, the total Bucks will
show the correct number of Bucks.

Since the ITABucksByContact query is by ContactID, we'd be best off joining
it to the BucksByContact query. Again, given that BucksByContact has some
complexity in itself, I prefer to create a new query using the two, say
AllBucksByContact:
SELECT BuckByContact.ContactID, BuckByContact.SumOfITABucks AS BucksEarned,
ITABucksByContact.SumOfAmount AS BucksUsed, BuckByContact.LastName,
BuckByContact.FirstName, BuckByContact.SignificantOtherID, BuckByContact.
PrimaryFamilyMember, BuckByContact.MemberCategoryID
FROM BuckByContact LEFT JOIN ITABucksByContact ON BuckByContact.ContactID =
ITABucksByContact.ContactID;

I've renamed the various Bucks columns to better reflect what they mean:
otherwise, we'd have lots of ITABucks fields confusing things. In using the
Left Join here, we are making the assumption that a Contact must earn some
ITA Bucks before they can spend any - if this isn't the case, then we would
need to have an additional query to get those records too and use a UNION
query.

We then need to use this for our next few queries -
AllBucksByContactWithSpouse (with some additional column renaming to make
things a bit easier later on and doing the null comversion straight away):
SELECT AllBucksByContact.ContactID, AllBucksByContact.BucksEarned,
AllBucksByContact.BucksUsed, AllBucksByContact.LastName, AllBucksByContact.
FirstName, AllBucksByContact.SignificantOtherID, AllBucksBySpouse.LastName AS
SpouseLastName, AllBucksBySpouse.FirstName AS SpouseFirstName, nz(
[AllBucksBySpouse].[BucksEarned],0) AS SpouseBucksEarned, nz(
[AllBucksBySpouse].[BucksUsed],0) AS SpouseBucksUsed, AllBucksByContact.
MemberCategoryID
FROM AllBucksByContact LEFT JOIN AllBucksByContact AS AllBucksBySpouse ON
AllBucksByContact.SignificantOtherID = AllBucksBySpouse.ContactID
WHERE (((AllBucksByContact.SignificantOtherID)<>0) AND ((AllBucksByContact.
PrimaryFamilyMember)=True)) OR (((AllBucksByContact.SignificantOtherID)=0));

AllBucksByFamily (now add together the Bucks):
SELECT AllBucksByContactWithSpouse.ContactID, [BucksEarned]+
[SpouseBucksEarned] AS FamilyBucksEarned, [BucksUsed]+[SpouseBucksUsed] AS
FamilyBucksUsed, [FamilyBucksEarned]+[FamilyBucksUsed] AS
FamilyBucksAvailable, AllBucksByContactWithSpouse.LastName,
AllBucksByContactWithSpouse.FirstName, AllBucksByContactWithSpouse.
SignificantOtherID, AllBucksByContactWithSpouse.SpouseLastName,
AllBucksByContactWithSpouse.SpouseFirstName, AllBucksByContactWithSpouse.
MemberCategoryID
FROM AllBucksByContactWithSpouse;

Also, since these cashed-in Bucks (Amount) for a Family come from 2 members,
I'm curious how you envision it working- it seems as though the BucksByFamily
would take the cashed-in Bucks into consideration. But what about the
individuals within the Family? I think I would need the individual Bucks
decreased as well?

Probably the key question here is whether you want to store separately how
many Bucks are available, as opposed to calculating it every time. The
advantage of the latter is that you can get to the figure without having to
go through a whole bunch of queries, which could be time consuming as well as
a bit tricky to set up. The disadvantage is that you would have to run an
update at some point to update the total in that query. Given the volume
involved, I'd prefer to use the queries. Either way, you're going to need to
be careful about how you go about restricting the number of bucks people can
redeem at any one point in time - neither approach helps with that issue...
you also have to consider whether, for a family, they can cash in their
partner's Bucks at the same time. From an end user point of view, I'd think
they would expect to be able to do so, which adds an additional levle of
complexity - either we allow an individual contact to spend more bucks than
they earn, or we have some logic to start subtracting from the spouse's
balance once their own Bucks have all been spent...

 

[David S via AccessMonster.com]

As a last Bucks query question (I hope!), on ITABucksByContact query I'd

like to add together Bucks Earned and Bucks Used (because I think I'm going
to end up cashing in Bucks per Individual...). This is the part that was so
difficult for me. Thanks.

Um, I'm pretty sure that's what the BucksAvailable field in the
AllBucksByFamily query in my earlier post does...

Glad to be have help, Stephanie - cheers!

 

[Stephanie]

Hello! Wow, I tried to make that way too complicated!
Thanks for your patience and great amounts of help- I really learned a great
deal about structuring queries (although the complicated, multi-part queries
still give me grief!)
I appreciate your help and kind replies.

 

[Stephanie]

Actually, David, there is a problem with AllBucksByFamily. If there are no
FamilyBucksUsed then FamilyBucksAvailable is blank even if there are
FamilyBucksEarned. Meaning that FamilyBucksAvailable should be the same in
this case as FamilyBucksEarned.

I've been spinning in circles and can't seem to fix it. I'd appreciate help
if you have time. Thanks.

 

[Stephanie]

Sorry to bombard you with replies. I solved my issue. I used your famous nz
in AllBucksByFamily: [FamilyBucksEarned]+nz([FamilyBucksUsed],0) AS
FamilyBucksAvailable and was able to get a value in FamilyBucksAvailable.

All is well! Thanks so much.