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

Senin, 03 Maret 2014

Re: [MS_AccessPros] RE: sql difference on "sum"

Liz-

The reason is your GROUP BY:

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

You're getting the total by EmployeeNumber, Skill, AND the date. You're not using CompleteDate in your SELECT, so why group on it?

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Mar 3, 2014, at 2:03 PM, Liz Ravenwood <liz_ravenwood@beaerospace.com> wrote:

Oh the reason on the group by is because I'm trying to debug queries that are giving me inflated results.

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Liz Ravenwood
Sent: Monday, March 03, 2014 5:41 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] sql difference on "sum"

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
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, 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.

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.