Hi.  I'm frustrated!  I can't figure out why this won't work.  Must be user
error!  Here's the setup: both Contacts and SignificantOthers (these spouses
are themselves Contacts, hence the self join) can earn, use and lose bucks.
Ultimately, I'm trying to calculate how many bucks are avialable from Contact
and Spouse together.  Here's what I have:
[BucksEarned]+[BucksUsed]+[BucksLost]=BucksAvailable   and this part works.

[SpouseBucksEarned]+[SpouseBucksUsed]+[SpouseBucksLost]=SpouseBucksAvailabe  
does NOT work. Say I have 18-3-4=11  then SpouseBucksAvailable should be 11.

However, rather than adding together, all 3 numbers show up in one query
column: 18-3-4
I want to be able to add this SpouseBucksAvailable column (that should be
11) to the BucksAvailable column to get a total.  (really, these 3 step
queries kill me!)

Here's my query- I'd appreciate suggestions.  Thanks.

SELECT AllBucksByContactSumTotal.ContactID,
AllBucksByContactSumTotal.BucksEarned AS BucksEarned,
AllBucksByContactSumTotal.BucksUsed AS BucksUsed,
AllBucksByContactSumTotal.BucksLost AS BucksLost,
[BucksEarned]+[BucksUsed]+[BucksLost] AS BucksAvailable,
AllBucksByContactSumTotal.SignificantOtherID,
nz([AllBucksBySpouse].[BucksEarned],0) AS SpouseBucksEarned,
nz([AllBucksBySpouse].[BucksUsed],0) AS SpouseBucksUsed,
nz([AllBucksBySpouse].[BucksLost],0) AS SpouseBucksLost
FROM AllBucksByContactSumTotal LEFT JOIN AllBucksByContactSumTotal AS
AllBucksBySpouse ON AllBucksByContactSumTotal.SignificantOtherID =
AllBucksBySpouse.ContactID
WHERE (((AllBucksByContactSumTotal.SignificantOtherID)<>0) AND
((AllBucksByContactSumTotal.PrimaryFamilyMember)=True)) OR
(((AllBucksByContactSumTotal.SignificantOtherID)=0));