Liz-
Okay Thanks John. That seems logical.
Now here's another one that is the same query with the totals taken off. Why am I getting so many records?
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
WHERE (((CompletionsTucson.EmployeeNumber)=18702) AND ((Left([SkillRequired],3))="AMT") AND ((CompletionsTucson.CompleteDate)>#12/31/2013#));
Query1
EmployeeNumber
Duration
Expr1
18702
1
AMT
18702
1
AMT
18702
1
AMT
18702
1
AMT
18702
0.75
AMT
18702
0.75
AMT
18702
1.5
AMT
18702
1.5
AMT
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Monday, March 03, 2014 6:19 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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
(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 |
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.
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 (5) |
Tidak ada komentar:
Posting Komentar