I have a bunch of small queries that total dollar amounts up. I am trying to put all of the totals queries into one big query so I can create a report. It works fine for all of the queries that have a $$ amount in the total. When I add a query where the $$ amount is blank, the entire query comes back with nothing. Why does the empty $$ amount cause the query to show nothing? Is there an easier way to get all of these queries into a report? Thanks!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Totals Query
- Thread starter RonCT
- Start date
- Thread starter
- #3
Golom - How would I incorporate the SUM(NZ([AmountField])) into the queries? The queries are totals queries that query another query to get the $$ amount. My totals query looks like this
Field: SumOfAMOUNT
Table: Recovery
Total: Sum
When I run it it gives me the $$ all rolled up into one total. The queries with the blank/null values make the entire query blank when I try to put a few of these totals queries into one big query. Please let me know. Thanks!
Field: SumOfAMOUNT
Table: Recovery
Total: Sum
When I run it it gives me the $$ all rolled up into one total. The queries with the blank/null values make the entire query blank when I try to put a few of these totals queries into one big query. Please let me know. Thanks!
- Thread starter
- #6
- Thread starter
- #7
Golom - Below is the SQL for one of the totals query. Thanks!
SELECT Sum(COMPANY_NON_POS_INP.SumOfAMOUNT) AS SumOfSumOfAMOUNT
FROM COMPANY_NON_POS_INP
GROUP BY COMPANY_NON_POS_INP.XYZ, COMPANY_NON_POS_INP.BOB, COMPANY_NON_POS_INP.Product, COMPANY_NON_POS_INP.[Fund Code];
SELECT Sum(COMPANY_NON_POS_INP.SumOfAMOUNT) AS SumOfSumOfAMOUNT
FROM COMPANY_NON_POS_INP
GROUP BY COMPANY_NON_POS_INP.XYZ, COMPANY_NON_POS_INP.BOB, COMPANY_NON_POS_INP.Product, COMPANY_NON_POS_INP.[Fund Code];
- Thread starter
- #9
Thanks Nice! I will get another query. Do you know why it doesn't work? I have another database where I have a query totaling another query and it works fine even though some of the results for the fields are blank. I just placed all of the final total queries into one main totals query and I based my report on it. It won't work for me in this dtatbase. If I have 5 queries that have a result, then add the 6th which has nothing, the entire query comes back blank.
I've introduced a table alias "C" just to make it more readable
[blue][tt]
SELECT Sum(NZ(C.SumOfAMOUNT)) AS SumOfSumOfAMOUNT
FROM COMPANY_NON_POS_INP As C
GROUP BY C.XYZ, C.BOB, C.Product, C.[Fund Code];
[/tt][/blue]
This should give you multiple values for your computed SUM corresponding to unique concatenations of the fields in your GROUP BY clause. Since you have not included the GROUP BY fields in the select, you won't know which sum goes with which group however. If you are getting only one number from this then the conclusion would be that there is only one value for the concatenated GROUP BY fields. Try the following to check that out.
[blue][tt]
SELECT Sum(C.SumOfAMOUNT) AS SumOfSumOfAMOUNT,
C.XYZ, C.BOB, C.Product, C.[Fund Code]
FROM COMPANY_NON_POS_INP C
GROUP BY C.XYZ, C.BOB, C.Product, C.[Fund Code];
[/tt][/blue]
[blue][tt]
SELECT Sum(NZ(C.SumOfAMOUNT)) AS SumOfSumOfAMOUNT
FROM COMPANY_NON_POS_INP As C
GROUP BY C.XYZ, C.BOB, C.Product, C.[Fund Code];
[/tt][/blue]
This should give you multiple values for your computed SUM corresponding to unique concatenations of the fields in your GROUP BY clause. Since you have not included the GROUP BY fields in the select, you won't know which sum goes with which group however. If you are getting only one number from this then the conclusion would be that there is only one value for the concatenated GROUP BY fields. Try the following to check that out.
[blue][tt]
SELECT Sum(C.SumOfAMOUNT) AS SumOfSumOfAMOUNT,
C.XYZ, C.BOB, C.Product, C.[Fund Code]
FROM COMPANY_NON_POS_INP C
GROUP BY C.XYZ, C.BOB, C.Product, C.[Fund Code];
[/tt][/blue]
To sum it up as simple as possible
Null + 1 = Null
Null + Null = Null
1 + Null = Null
Because Access does not know what to do with Nulls in calulations it will always return a Null Value. Because a null value is Variant so unless you tell what value to return it will retrun nothing.
You first have to covert that null value to a something Access will understand like a number.
Example. IIF(IsNull([YOUR VALUE]),0,[YOUR VALUE])
So here we are telling Access to covert that Null value to a 0.
And if it was a text field it would look something like this.
IIF(IsNull([YOUR VALUE]),"Some Text",[YOUR VALUE])
Hope this helps on why it is not working.
Null + 1 = Null
Null + Null = Null
1 + Null = Null
Because Access does not know what to do with Nulls in calulations it will always return a Null Value. Because a null value is Variant so unless you tell what value to return it will retrun nothing.
You first have to covert that null value to a something Access will understand like a number.
Example. IIF(IsNull([YOUR VALUE]),0,[YOUR VALUE])
So here we are telling Access to covert that Null value to a 0.
And if it was a text field it would look something like this.
IIF(IsNull([YOUR VALUE]),"Some Text",[YOUR VALUE])
Hope this helps on why it is not working.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
