Self-join field addition

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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));
 
Evidently, your spousebucks fields are being treated as text strings vice
numbers. The culprit is probably the NZ function. Try Wrapping the NZ()
inside of CCUR.

CCUR(nz([AllBucksBySpouse].[BucksEarned],0)) AS SpouseBucksEarned

Repeat on the other columns (fields).
 
You are fabulous! That did the trick. Thanks for the help.

John Spencer said:
Evidently, your spousebucks fields are being treated as text strings vice
numbers. The culprit is probably the NZ function. Try Wrapping the NZ()
inside of CCUR.

CCUR(nz([AllBucksBySpouse].[BucksEarned],0)) AS SpouseBucksEarned

Repeat on the other columns (fields).


Stephanie said:
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));
Click to expand...
Click to expand...
 
Back
Top