Share via

Sum Value in Query Returning Very Wrong

Anonymous
2016-11-03T20:44:11+00:00

I know my basic SQL, and I've picked up Access pretty well considering I just started. Although I'm not a pro, I feel like the following problem shouldn't be a problem.

I have four tables connect with joins (I let Access do the joining so I have no doubts on those.) I'm trying to pull a date, a calculated StandardSupport value, a calculated ActualSupport value, and a calculated SupportEfficiency value. This query is going to be used to create a report. The Daily version of this query works perfectly.

The following is my code (minus joins because they're a mess but correct):

SELECT C.FiscalMonth AS MTD, Sum(R.Quantity*L.TotalSupport) AS StandardSupport, Sum(Indirect.ActHours) AS ActualHours, Sum(R.Quantity*L.TotalSupport/Indirect.ActHours) AS SupportEfficiency

WHERE (((C.FiscalMonth)= 'January') AND ((Indirect.LaborType)='Support Hours'))

GROUP BY C.FiscalMonth;

My StandardSupport value comes out perfect. My SupportEfficiency is wrong. The biggest issue I'm having problems with is the sum of acthours.

Indirect Table:

ID LaborType Date ActHours Employees
1 Support Hours 1/4/2016 58.6 7
2 Support Hours 1/5/2016 50.7 6
3 Support Hours 1/6/2016 83.5 10
4 Support Hours 1/7/2016 92.1 11
5 Support Hours 1/8/2016 90.1 11
6 R&D 1/8/2016 8.4 1
7 Support Hours 1/11/2016 90.5 11

Query Result:

MTD StandardSupport ActualHours SupportEfficiency
January 574.87 21960.50 782.90%

*ActualHours should come out to 473.9

Clearly, the sum of those six Support Hours values does not equal what it's giving me in the query results. I've triple checked my data types - ActHours is double, and I don't believe anything else should affect the results. Doing it as a non-sum and putting it in the group by clause works fine, except that I want them all added up for the month. 

This is my first time posting a question also, so I apologize if this is difficult to read or understand or if my SQL is sloppy. Any help is appreciated!

-Makayla

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2016-11-03T23:29:54+00:00

    I'd be pretty sure this arises from the fact that you have four joined tables in the query.  Any aggregation operation operates over the set of rows in the result table.  If you have a row in a referenced table each of which is referenced by 10 rows in a referencing table and you sum a column from the former then the sum will not be the sum of the values in the relevant rows in the former table, but the sum of those values multiplied by 10.

    In situations like this you can, rather than joining to a base table, join to a subquery in which the summation is done, rather than in the outer query.  The aggregated values will then be those of the rows in the table in the subquery, so there will be no specious multiplication of the values.  A simpler approach for those less experienced in SQL is to create separate queries in each of which the summation is done and then to join those queries in another query for use as the report's RecordSource.

    6 people found this answer helpful.
    0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-11-03T22:30:26+00:00

    You're right, my total was wrong. My bad. Still though, I think you're right. I'll see about stripping and posting a version tomorrow. It's for work, so I don't have it readily availability now.

    0 comments
  2. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2016-11-03T22:17:09+00:00

    > ActualHours should come out to 473.9

    No, it should come out to 465.5, because line 6 is not support hours.

    I created a table based on your data, and ran a Totals query, and it comes out as just that.

    So something else fishy is going on. Maybe you can post your database, stripped to the bare essentials, in a public place like a free OneDrive account, so we can take a closer look.

    0 comments
  3. Anonymous
    2016-11-03T21:52:47+00:00

    Good catch, I appreciate the advice. The data you see in that indirect table is the only data in it however, so there's no data from other years or even other months. I have to load the data day by day, so I only loaded a few days to make sure my queries will work before I go through all that just to discover that I need to design my tables differently.

    I've deleted, recreated, and repopulated the indirect table a few times thinking that I'd made a mistake that I couldn't find somewhere along the line, but I continue to get the same incorrect results unfortunately.

    0 comments
  4. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2016-11-03T21:37:55+00:00

    Maybe it's adding up the January data from other years. Your where-clause should probably include:

    C.FiscalYear = 2016

    0 comments