Sum of multiple fields into single field from subqueries

[Jason]

I am using Access to write reports for our SQL database using ODBC
connections. Currently I am working on a complex query to summarize
conference registration data that is stored in a single table, in individual
rows identified by the Person ID and Item ID.

To do this, I've setup subqueries to get the Person ID, Item Name and
Payment Amount for each Item ID. Then I have the query LEFT JOIN the full
conference registration list to each subquery on the PersonID, and populate
two fields with the data from the subqueries: One field has Item Names
(ActivityID) and the second is supposed to be the sum of the Payment Amounts
(AG_Current_Price).

It is working well when there is only one item to populate in each field.
But when there are multiple items, the sum function is not working. For
example, a person appears in both the CEU FEE subquery and the DONATIONS
subquery.

The CEU FEE subquery returns the result PersonID; CEU FEE ; 25
The DONATIONS subquery returns PersonID; DONATIONS ; 10
The aggregate result in the query should be: PersonID; CEU FEE
DONATIONS; 35
But instead I'm getting this result: PersonID; CEU FEE
DONATIONS; 2510

I cannot for the life of me figure out why the Sum function is not adding
the Payment Amounts, but I am getting a concatenated string instead. Any
ideas are appreciated.

Here's the SQL code:

SELECT dbo_AG_Person_MSTR.AG_Person_ID, [JAS Agora Conf Item -
CEU]![AG_ActivityID] & [JAS Agora Conf Item - Exhibitor]![AG_ActivityID] &
[JAS Agora Conf Item - Tables Fellows]![AG_ActivityID] & [JAS Agora Conf Item
- Exhibitor Commercial]![AG_ActivityID] & [JAS Agora Conf Item - CEU
Comp]![AG_ActivityID] & [JAS Agora Conf Item - Donation]![AG_ActivityID] AS
Expr1, Sum([JAS Agora Conf Item - CEU]![AG_Current_Price] & [JAS Agora Conf
Item - Exhibitor]![AG_Current_Price] & [JAS Agora Conf Item - Tables
Fellows]![AG_Current_Price] & [JAS Agora Conf Item - Exhibitor
Commercial]![AG_Current_Price] & [JAS Agora Conf Item - CEU
Comp]![AG_Current_Price] & [JAS Agora Conf Item -
Donation]![AG_Current_Price]) AS Expr2 INTO [JAS AG CRP3]
FROM ((((((dbo_AG_Person_MSTR INNER JOIN dbo_AG_EvtRegistrant_WORK ON
dbo_AG_Person_MSTR.AG_Person_ID = dbo_AG_EvtRegistrant_WORK.AG_Person_ID)
LEFT JOIN [JAS Agora Conf Item - CEU] ON dbo_AG_Person_MSTR.AG_Person_ID =
[JAS Agora Conf Item - CEU].AG_Person_ID) LEFT JOIN [JAS Agora Conf Item -
Tables Fellows] ON dbo_AG_Person_MSTR.AG_Person_ID = [JAS Agora Conf Item -
Tables Fellows].AG_Person_ID) LEFT JOIN [JAS Agora Conf Item - Exhibitor] ON
dbo_AG_Person_MSTR.AG_Person_ID = [JAS Agora Conf Item -
Exhibitor].AG_Person_ID) LEFT JOIN [JAS Agora Conf Item - Exhibitor
Commercial] ON dbo_AG_Person_MSTR.AG_Person_ID = [JAS Agora Conf Item -
Exhibitor Commercial].AG_Person_ID) LEFT JOIN [JAS Agora Conf Item - CEU
Comp] ON dbo_AG_Person_MSTR.AG_Person_ID = [JAS Agora Conf Item - CEU
Comp].AG_Person_ID) LEFT JOIN [JAS Agora Conf Item - Donation] ON
dbo_AG_Person_MSTR.AG_Person_ID = [JAS Agora Conf Item -
Donation].AG_Person_ID
GROUP BY dbo_AG_Person_MSTR.AG_Person_ID, [JAS Agora Conf Item -
CEU]![AG_ActivityID] & [JAS Agora Conf Item - Exhibitor]![AG_ActivityID] &
[JAS Agora Conf Item - Tables Fellows]![AG_ActivityID] & [JAS Agora Conf Item
- Exhibitor Commercial]![AG_ActivityID] & [JAS Agora Conf Item - CEU
Comp]![AG_ActivityID] & [JAS Agora Conf Item - Donation]![AG_ActivityID];

 

[Peter YangMSFT]]

Hello Jason,

It seems that it's normal behavior since you use the following syntax for
SUM:


Sum([JAS Agora Conf Item - CEU]![AG_Current_Price] & [JAS Agora Conf Item -
Exhibitor]![AG_Current_Price] & [JAS Agora Conf Item - Tables
Fellows]![AG_Current_Price] & [JAS Agora Conf Item - Exhibitor
Commercial]![AG_Current_Price] & [JAS Agora Conf Item - CEU
Comp]![AG_Current_Price] & [JAS Agora Conf Item -
Donation]![AG_Current_Price]) AS Expr2


You may try the following Expr2 to see if it helps

Sum([JAS Agora Conf Item - CEU]![AG_Current_Price])+Sum ([JAS Agora Conf
Item - Exhibitor]![AG_Current_Price]) +...
Sum([JAS Agora Conf Item - Donation]![AG_Current_Price]) as Expr2

If you have any further comments, please feel free to let's know. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jason]

Hello Peter,

I tried this yesterday, and again just now, but what happens when I change
the syntax to Sum([...]) + Sum([...]) + etc. is that it then returns a null
result. That is actually worse, because even the records with only a single
entry then have no data.

Thanks for trying. Any other ideas?


Jason

 

[Peter YangMSFT]]

Hello Jason,

I think it's becasue there is NULL for the result of a Sum([...]). You may
try the following:

isnull (Sum([...]), 0) + isnull(Sum([...]), 0)...

Hope this is helpful.


Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[John W. Vinson]

Hello Jason,

I think it's becasue there is NULL for the result of a Sum([...]). You may
try the following:

isnull (Sum([...]), 0) + isnull(Sum([...]), 0)...

Hope this is helpful.

Peter, shouldn't that be NZ() rather than IsNull()?

John W. Vinson [MVP]

 

[Jason]

Hey, that worked! Thanks!!!


Jason

Hello Jason,

I think it's becasue there is NULL for the result of a Sum([...]). You may
try the following:

isnull (Sum([...]), 0) + isnull(Sum([...]), 0)...

Hope this is helpful.

Peter, shouldn't that be NZ() rather than IsNull()?

John W. Vinson [MVP]