Hall of Fame
Microsoft Access - Sum Function Not Working Properly
on 18-Jan-12 03:23 PM
I believe this is my error due to how I have designed the database but I am not able to figure out what could be going wrong. I have a report based off of a query where I am summing various expressions based on the entered salesperson. If I do not sum these expressions, the query displays the desired results but naturally it is on many rows. The problem with this is that the report only selects the data in the first row of the expression. If I set all of the expressions to sum it is only on one row for the specified sales person. However, this does not sum how I would like it and instead the number is a multiple of what it should be. In most cases it is 4 times greater than what it should be. When I remove all of the tables but just two, and leave only the one expression based on those two tables, I am able to use the sum function. I'm guessing this is related to the relationships between my tables/queries? I'm not sure but if anyone has any suggestions it would be greatly appreciated. I would even go the wrote of not summing the expressions in the query and using something in the report to correctly sum them. Or I would also gladly go the vba route if there is one.
on 18-Jan-12 06:06 PM
You'll have better luck posting your SQL. Noone is going to be able to make any sense of what you've written here.
on 18-Jan-12 06:09 PM
It sounds to me that you are having issues with the joins or conditions. You might try not grouping in the query and see if you have multiple rows. If you do, you will need to either put conditions (WHERE) or tighten up the joins.
on 19-Jan-12 08:13 AM
If u r using SUM function, then u should take care of other field should be in group by, as this fumction is aggregate function.
might be u have missed some columns to group by.
ur query should be
select A, B, SUM(C) from table1
group by A, B
on 19-Jan-12 12:44 PM
Ended up figuring this one out. Had something to do with how I constructed the queries used in the query that the report was based off of.
Access over 40 UI widgets with everything from interactive menus to rich charts.