Bryan Schmidt
asked on
Query total returns much higher value than expect in Access 2010
I have a simple query that correctly returns three records as shown below when no Group By is used. It simply lists each record separately.
Amt NbrAffordableUnitsCreated NbrJobsCreated
$70,000.00 140
$589.06 10
$1,074,047.00 80
When I add a Group By to sum the Amt, NbrAffordableUnitsCreated, and NbrJobsCreated columns the Amt appears as $1,444,638,537 instead of $1,144,636 while the other two colums sum correctly. Something is causing the Amt to increase roughly a 1,000 times more than it should. A copy of the query and output is attached (you may need to enlarge to read it).
Any suggestions on why this is happening would be greatly appreciated.
Amt NbrAffordableUnitsCreated NbrJobsCreated
$70,000.00 140
$589.06 10
$1,074,047.00 80
When I add a Group By to sum the Amt, NbrAffordableUnitsCreated,
Any suggestions on why this is happening would be greatly appreciated.
No attachment
Any join involved in this query, since that can be one of the reasons for odd sum?
ASKER
Sorry, I forgot to click Upload File. There is no join in the query.
SampleQuery.pdf
SampleQuery.pdf
Can you post the SQL query from the SQL view? it will be more clear , at least to me :)
ASKER
SELECT Sum([main table].[Dollar Amount]) AS Amt, Sum([main table].NbrAffordableUnitsC reated) AS NbrAffordableUnitsCreated, Sum([main table].NbrJobsCreated) AS NbrJobsCreated
FROM [main table]
WHERE ((([main table].[Activity Date])>=#1/1/2014# And ([main table].[Activity Date])<#9/30/2014#) AND (([main table].Approved)=Yes)) OR ((([main table].[Activity Date])>=#1/1/2014# And ([main table].[Activity Date])<#9/30/2014#) AND (([main table].Approved)=Yes))
GROUP BY [main table].Category
HAVING (((Sum([main table].NbrAffordableUnitsC reated)) Is Not Null) AND (([main table].Category)="Investme nts")) OR (((Sum([main table].NbrJobsCreated)) Is Not Null) AND (([main table].Category)="Investme nts"));
FROM [main table]
WHERE ((([main table].[Activity Date])>=#1/1/2014# And ([main table].[Activity Date])<#9/30/2014#) AND (([main table].Approved)=Yes)) OR ((([main table].[Activity Date])>=#1/1/2014# And ([main table].[Activity Date])<#9/30/2014#) AND (([main table].Approved)=Yes))
GROUP BY [main table].Category
HAVING (((Sum([main table].NbrAffordableUnitsC
It is most likely rounding errors adding up.
I guess your Dollar Amount field is of data type Double or Single and not Currency.
Try this modification:
SELECT Sum(CCur([main table].[Dollar Amount])) AS Amt
and watch the three records. Then sum these.
/gustav
I guess your Dollar Amount field is of data type Double or Single and not Currency.
Try this modification:
SELECT Sum(CCur([main table].[Dollar Amount])) AS Amt
and watch the three records. Then sum these.
/gustav
ASKER
I modified the SQL statement as shown below. When running it produces "Invalid use of Null". If I remove the Group By it won't run because the NbrAffordableUnitsCreated field is not part of an aggregate function. I'm sure the same applies to the field NbrJobsCreated.
Please tell me how to modify it more. The format of the Dollar Amount field in the table is currency.
SELECT Sum(CCur([main table].[Dollar Amount])) AS Amt, Sum([main table].NbrAffordableUnitsC reated) AS NbrAffordableUnitsCreated, Sum([main table].NbrJobsCreated) AS NbrJobsCreated
FROM [main table]
WHERE ((([main table].[Activity Date])>=#1/1/2014# And ([main table].[Activity Date])<#9/30/2014#) AND (([main table].Approved)=Yes)) OR ((([main table].[Activity Date])>=#1/1/2014# And ([main table].[Activity Date])<#9/30/2014#) AND (([main table].Approved)=Yes))
GROUP BY [main table].Category
HAVING (((Sum([main table].NbrAffordableUnitsC reated)) Is Not Null) AND (([main table].Category)="Investme nts")) OR (((Sum([main table].NbrJobsCreated)) Is Not Null) AND (([main table].Category)="Investme nts"));
Please tell me how to modify it more. The format of the Dollar Amount field in the table is currency.
SELECT Sum(CCur([main table].[Dollar Amount])) AS Amt, Sum([main table].NbrAffordableUnitsC
FROM [main table]
WHERE ((([main table].[Activity Date])>=#1/1/2014# And ([main table].[Activity Date])<#9/30/2014#) AND (([main table].Approved)=Yes)) OR ((([main table].[Activity Date])>=#1/1/2014# And ([main table].[Activity Date])<#9/30/2014#) AND (([main table].Approved)=Yes))
GROUP BY [main table].Category
HAVING (((Sum([main table].NbrAffordableUnitsC
The format of the field has no importance, only the data type.
If it contains Nulls, modify like this:
SELECT Sum(CCur(Nz([main table].[Dollar Amount],0))) AS Amt
Or how about just filtering out the Nulls:
SELECT
Sum(CCur([main table].[Dollar Amount])) AS Amt,
Sum([main table].NbrAffordableUnitsC reated) AS NbrAffordableUnitsCreated,
Sum([main table].NbrJobsCreated) AS NbrJobsCreated
FROM
[main table]
WHERE
((([main table].[Activity Date])>=#1/1/2014# And ([main table].[Activity Date])<#9/30/2014#) AND (([main table].Approved)=Yes)) OR ((([main table].[Activity Date])>=#1/1/2014# And ([main table].[Activity Date])<#9/30/2014#) AND (([main table].Approved)=Yes)) And [main table].NbrAffordableUnitsC reated Is Not Null
GROUP BY
[main table].Category
HAVING
Sum([main table].NbrJobsCreated) Is Not Null AND [main table].Category="Investmen ts";
/gustav
If it contains Nulls, modify like this:
SELECT Sum(CCur(Nz([main table].[Dollar Amount],0))) AS Amt
Or how about just filtering out the Nulls:
SELECT
Sum(CCur([main table].[Dollar Amount])) AS Amt,
Sum([main table].NbrAffordableUnitsC
Sum([main table].NbrJobsCreated) AS NbrJobsCreated
FROM
[main table]
WHERE
((([main table].[Activity Date])>=#1/1/2014# And ([main table].[Activity Date])<#9/30/2014#) AND (([main table].Approved)=Yes)) OR ((([main table].[Activity Date])>=#1/1/2014# And ([main table].[Activity Date])<#9/30/2014#) AND (([main table].Approved)=Yes)) And [main table].NbrAffordableUnitsC
GROUP BY
[main table].Category
HAVING
Sum([main table].NbrJobsCreated) Is Not Null AND [main table].Category="Investmen
/gustav
ASKER
The Dollar Amount field has no null values for records where Category = Investments.
When using the following SELECT statement the dollar amount still shows as $1,444,638,537, or a 1,000 times more than it should. The other two fields sum correctly. Can you think of something else I should try, or look at?
SELECT
Sum(CCur([main table].[Dollar Amount])) AS Amt,
Sum([main table].NbrAffordableUnitsC reated) AS NbrAffordableUnitsCreated,
Sum([main table].NbrJobsCreated) AS NbrJobsCreated
FROM
[main table]
WHERE
((([main table].[Activity Date])>=#1/1/2014# And ([main table].[Activity Date])<#9/30/2014#) AND (([main table].Approved)=Yes)) OR ((([main table].[Activity Date])>=#1/1/2014# And ([main table].[Activity Date])<#9/30/2014#) AND (([main table].Approved)=Yes)) And [main table].NbrAffordableUnitsC reated Is Not Null
GROUP BY
[main table].Category
HAVING
Sum([main table].NbrJobsCreated) Is Not Null AND [main table].Category="Investmen ts";
When using the following SELECT statement the dollar amount still shows as $1,444,638,537, or a 1,000 times more than it should. The other two fields sum correctly. Can you think of something else I should try, or look at?
SELECT
Sum(CCur([main table].[Dollar Amount])) AS Amt,
Sum([main table].NbrAffordableUnitsC
Sum([main table].NbrJobsCreated) AS NbrJobsCreated
FROM
[main table]
WHERE
((([main table].[Activity Date])>=#1/1/2014# And ([main table].[Activity Date])<#9/30/2014#) AND (([main table].Approved)=Yes)) OR ((([main table].[Activity Date])>=#1/1/2014# And ([main table].[Activity Date])<#9/30/2014#) AND (([main table].Approved)=Yes)) And [main table].NbrAffordableUnitsC
GROUP BY
[main table].Category
HAVING
Sum([main table].NbrJobsCreated) Is Not Null AND [main table].Category="Investmen
How many records holds "main table"?
What are the results if you remove the criteria?
/gustav
What are the results if you remove the criteria?
/gustav
ASKER
There are 87,709 records in the main table divided among three categories. The records go back to 1998. If I filter only on investments with activity dates from 1/1/2014 to 9/30/2014 I get 3300 records which, in Excel, still sum as the queries above. If I remove the filters for NbrAffordableUnitsCreated and NbrJobsCreated the dollar amount is the same as above. This suggests the filters for NbrAffordableUnitsCreated and NbrJobsCreated are being ignored. Only three records meet the selection criteria as originally written.
If I add a filter for NbrAffordableUnitsCreated (Is not Null) the result is correct (returns one record). If I replace the filter for NbrAffordableUnitsCreated with one for NbrJobsCreated (Is not Null) the result is also correct (returns two records).
The problem may be with the logic used to filter for NbrAffordableUnitsCreated and NbrJobsCreated. The filters as orginally written visually make sense but are not producing the correct result. Both of these fields are full of null values except for the three records returned. Can you suggest another way to write the filters that would fix this?
If I add a filter for NbrAffordableUnitsCreated (Is not Null) the result is correct (returns one record). If I replace the filter for NbrAffordableUnitsCreated with one for NbrJobsCreated (Is not Null) the result is also correct (returns two records).
The problem may be with the logic used to filter for NbrAffordableUnitsCreated and NbrJobsCreated. The filters as orginally written visually make sense but are not producing the correct result. Both of these fields are full of null values except for the three records returned. Can you suggest another way to write the filters that would fix this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will run it without trying to sum results. Your logic looks fine but for some reason I cannot get it to work. If the number of such records increases signficantly I can always export the results to Excel and sum results there.
I appreciate your help.
I appreciate your help.
ASKER
Even though I cannot get the logic to work as desired the concept as described by Gustav in his last posting is the solution. I will run separate queries to get what is needed. Instead of using
(([main table].NbrAffordableUnitsC reated Is Not Null) AND ([main table].Category="Investmen ts"))
OR
(([main table].NbrJobsCreated Is Not Null) AND ([main table].Category="Investmen ts"));
I will use something like
(([main table].NbrAffordableUnitsC reated Is Not Null) AND ([main table].Category="Investmen ts"));
in one query and the following in the second.
(([main table].NbrJobsCreated Is Not Null) AND ([main table].Category="Investmen ts"));
(([main table].NbrAffordableUnitsC
OR
(([main table].NbrJobsCreated Is Not Null) AND ([main table].Category="Investmen
I will use something like
(([main table].NbrAffordableUnitsC
in one query and the following in the second.
(([main table].NbrJobsCreated Is Not Null) AND ([main table].Category="Investmen
If both NbrAffordableUnitsCreated and NbrJobsCreated can be Null at the same time, that could explain the difference.
/gustav
/gustav
ASKER
For the three records that meet the selection criteria that is not the case, but it is for all others. In theory I could make the filter something like NbrAffordableUnitsCreated Is Not Null AND NbrJobsCreated Is Null on one line, and NbrAffordableUnitsCreated Is Null AND NbrJobsCreated Is Not Null on the second line as this matches the records as they currently exist. However, this approach returns no records.
OK, then I would leave it with the method that works.
/gustav
/gustav