Access SUMs Full Column When Using Subquery

[Chris2]

I'm running Win2k SP-4, MS Access 2000 SP-3, and JET 4.0 SP-8.

While working on a question posted earlier, Access gave me results I
couldn't figure out.

I jumped over to SQL Server, and it gave me the results I wanted, so
it must be based in something about JET that I don't understand.


The problem is that when I use a subquery against a table that does
SELECT SUM(column) FROM MyTable, where the match retrieves but one
record, Access still runs the SUM against the whole column in MyTable.

Here's the DDL, Data, and SQL

--------------------------

CREATE TABLE Students_02272005_1
(Stud_ID LONG
,CONSTRAINT pk_Students_02272005_1 PRIMARY KEY (Stud_ID)
)

Sample Data: Students_02272005_1

1
2
3


CREATE TABLE Lessons_02272005_1
(Lesson TEXT(10)
,CONSTRAINT pk_Lessons PRIMARY KEY (Lesson)
)

Sample Data: Lesson_02272005_1

Math
English
Biology


CREATE TABLE Exams_02272005_1
(ExamID AUTOINCREMENT
,Stud_ID LONG
,Lesson TEXT(10)
,Exam DOUBLE
,CONSTRAINT pk_Exams_02272005_1 PRIMARY KEY (ExamID)
,CONSTRAINT fk_Exams_Students_02272005_1
FOREIGN KEY (Stud_ID)
REFERENCES Students_02272005_1 (Stud_ID)
,CONSTRAINT fk_Exams_Lessons_02272005_1
FOREIGN KEY (Lesson)
REFERENCES Lessons_02272005_1 (Lesson)
)


Sample Data: Exams_02272005_1

ExamID Stud_ID Lesson Exam
1 1 Math 4
2 1 Math 6
3 1 English 3
4 1 English 4
5 1 Biology 6
6 1 Biology 6
7 1 Biology 6
8 2 Math 6
9 2 Math 6
10 2 English 2
11 2 English 2
12 2 Biology 4
13 2 Biology 4
14 2 Biology 6
15 3 Math 1
16 3 Math 1
17 3 English 6
18 3 English 6
19 3 Biology 4
20 3 Biology 6


Query: Exams_02272005_1_Query_1
SELECT E1.Stud_ID
,E1.Lesson
,COUNT(*) AS LessonCount
FROM Exams_02272005_1 AS E1
GROUP BY E1.Stud_ID
,E1.Lesson


Query: Exams_02272005_1_Query_2
SELECT E1.Stud_ID
,E1.Lesson
,(COUNT(*) /
(SELECT SUM(E01.LessonCount)
FROM Exams_02272005_1_Query_1 AS E01
WHERE E01.Stud_ID = E1.Stud_ID
AND E01.Lesson = E1.Lesson)) AS PerecentAbove5
FROM Exams_02272005_1 AS E1
WHERE E1.Exam > 5
GROUP BY E1.Stud_ID
,E1.Lesson

Query_2 above should only be pulling one row from the subquery on the
SELECT clause due to conditions specified on the subquery's WHERE
clause.

When I execute it, though, it returns the value 20 for every single
row.

Stud_ID Lesson PercentAbove5
1 Biology 20
1 Math 20
2 Biology 20
2 Math 20
3 Biology 20
3 English 20



20 is the SUM of the full LessonCount column from the Query:
Exams_02272005_1_Query_1. Given the conditions on the subquery's
WHERE clause, how is this happening?

The above query, in SQL Server, operating on the same data, produces
the results I'd expect (with some CAST statements tossed in to force
use of DECIMAL(10,2)):

Stud_ID Lesson PercentAbove5
----------- ---------- ---------------------------------------
1 Biology 1.000000000000000
1 Math .500000000000000
2 Biology .333333333333300
2 Math 1.000000000000000
3 Biology .500000000000000
3 English 1.000000000000000
Command(s) completed successfully.


If anyone has an explanation of why JET is doing this, I'd appreciate
it.


Sincerely,

Chris O.

 

[Michel Walsh]

Hi,


Something is definitively out of place, since a similar statement, in
Northwind, works fine:

SELECT a.OrderID,
a.ProductID,
(SELECT SUM(b.Quantity)
FROM [Order Details] As b
WHERE b.OrderID=a.OrderID and b.ProductID=a.ProductID)
FROM [Order Details] As a
GROUP BY a.OrderID, a.ProductID


Hoping it may help,
Vanderghast, Access MVP

 

[Chris2]

Hi,


Something is definitively out of place, since a similar statement, in
Northwind, works fine:

SELECT a.OrderID,
a.ProductID,
(SELECT SUM(b.Quantity)
FROM [Order Details] As b
WHERE b.OrderID=a.OrderID and b.ProductID=a.ProductID)
FROM [Order Details] As a
GROUP BY a.OrderID, a.ProductID


Hoping it may help,
Vanderghast, Access MVP

Michel Walsh,

Thank you. I'll continue to ponder it for awhile.


Sincerely,

Chris O.