ACCESS FEED: [MS_AccessPros] RE: sql difference on "sum"

Rabu, 05 Maret 2014

[MS_AccessPros] RE: sql difference on "sum"

Liz

Obviously, something is causing the Sum() to double, but I can't see it. First, use SkillRequired instead of the LEFT(). That might give you a clue as to why it is doubling.

Next, remove the group by stuff from your first query seeing how you aren't doing any aggregating. That way you can see if the numbers change.

Regards,

Bill Mosca, Founder - MS_Access_Professionals

http://www.thatlldoit.com

Microsoft Office Access MVP

http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

---In MS_Access_Professionals@yahoogroups.com, <liz_ravenwood@beaerospace.com> wrote :

Pros, I'm trying to do a simple sum and am getting some strange results. Why does this total sum up like it does?

SELECT CompletionsTucson.EmployeeNumber, CourseHistoryTucson.Duration, Left([SkillRequired],3) AS Expr1

FROM EmployeesTucson INNER JOIN ((CourseHistoryTucson INNER JOIN CompletionsTucson ON CourseHistoryTucson.CourseHistID = CompletionsTucson.CourseHistID) INNER JOIN EmployeeRequirementsTucson ON CompletionsTucson.EmployeeNumber = EmployeeRequirementsTucson.EmployeeNumber) ON EmployeesTucson.EmployeeNumber = CompletionsTucson.EmployeeNumber

GROUP BY CompletionsTucson.EmployeeNumber, CourseHistoryTucson.Duration, Left([SkillRequired],3), CompletionsTucson.CompleteDate

HAVING (((CompletionsTucson.EmployeeNumber)=18702) AND ((Left([SkillRequired],3))="AMT") AND ((CompletionsTucson.CompleteDate)>#12/31/2013#));

Giving results:

Query1
EmployeeNumber	Duration	Expr1
18702	0.75	AMT
18702	1	AMT
18702	1	AMT
18702	1.5	AMT

To something I'm expecting for a 4.25: of

SELECT CompletionsTucson.EmployeeNumber, Sum(CourseHistoryTucson.Duration) AS SumOfDuration, Left([SkillRequired],3) AS Expr1

GROUP BY CompletionsTucson.EmployeeNumber, Left([SkillRequired],3), CompletionsTucson.CompleteDate

HAVING (((CompletionsTucson.EmployeeNumber)=18702) AND ((Left([SkillRequired],3))="AMT") AND ((CompletionsTucson.CompleteDate)>#12/31/2013#));

Query1
EmployeeNumber	SumOfDuration	Expr1
18702	2	AMT
18702	2	AMT
18702	1.5	AMT
18702	3	AMT

Respectfully,

Liz Ravenwood

Programmer / Analyst

B/E Aerospace | Super First Class Environments

1851 S Pantano Road | Tucson, Arizona 85710

Office +1.520.239.4808 |

beaerospace.com

Passion to Innovate. Power to Deliver

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (7)

Visit Your Group