From: "Liz Ravenwood" <liz_ravenwood@beaerospace.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, March 5, 2014 10:59:09 AM
Subject: RE: [MS_AccessPros] RE: sql difference on "sum"
Thanks Bill, you know the thing I learned from this was that yes, even though I wasn't grouping by or showing the left(skillrquired,3) = "AMT" it was still multiplying the number of durationhours from my coursehistory table based on how many occurrences I had on the skillrequired table. So I learned from that!
Then I learned that creating a query to eliminate dups and inner join from something called AMTRequired in to the other tables still wouldn't work, so I have a quick mkAMTRequired make table query and then have that inner join in.
It was some good learning. Thanks to you and John for the help. I love this group.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of wrmosca@comcast.net
Sent: Wednesday, March 05, 2014 10:27 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [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
Microsoft Office Access MVP
My nothing-to-do-with-Access blog
---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
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.
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (9) |
Tidak ada komentar:
Posting Komentar