Hi,
eh, well, what it does, well, eh, it does a running average... no?
just kidding.
For all records in table "a" (which is another name for myTable) that have
the same value for a.year, a.week and a.person (the GROUP BY clause), we
*assume* there is just one such record (no dup) given this set of values,
consider all records in table "b" (which, by the way, here, is the same
table, myTable) but, in fact, only those where "a.year=b.year AND a.person=
b.person AND a.week >= b.week"
and average the b.stat1 values for these records.
Repeat for another set of a.year, a.person, a.week.
Now, if you want to know how it is technically done, we have to start from a
cross join:
FROM tableA, tableB
will produce a result where each record in tableA is "matched" (joined)
with each record of tableB. If tableA is a set of possible clothes and
tableB is a set of possible colors, FROM tableA, tableB produces a set with
each possible cloth of each possible color. A little bit like:
Foreach ( recordA in tableA)
Foreach (recordB in tableB)
Print recordA, recordB
Next
Next
An inner join is LOGICALLY the same as starting from the cross join, but
only keeping those "joined" result for which the ON clause evaluates to TRUE
(not False, not Null). A little bit like:
Foreach ( recordA in tableA)
Foreach (recordB in tableB)
If( ON clause) then Print recordA, recordB
Next
Next
I say logically because the optimizer may use indexes to reduce the number
of tests (or other techniques). Example, if you have a telephone book of
this year and of last year, and wish to find new entries in this year book,
then: when you are in the middle of the process, at the "M" names, on the
new book, you won't scan the last year book "from start", from letter "A",
but you are more likely to run your left finger on one book and your right
finger on the other book and doing some synchronization between your two
indexes, you will do the same job, but much faster than doing a double dumb
"for each" (which imply to restart from scratch, with recordB, for each
entry in tableA).
So, in our case (assuming there is no duplicated record, recordA, given a
person, a year and a week) :
Foreach recordA in tableA
count=0
sum=0
Foreach recordA in tableB (same as tableA, but with another name)
If recordB.year=recordA.year AND recordB.person = recordA.person
AND recordB.week <= recordA.week
count = count + 1
sum = sum + recordB.stat1
end if
Next
avg=sum / count
Print recordA.year, recordA.person, recordA.week, avg
Next
would be what our SQL does for us (or logically does for us). With SQL you
describe what you want, but let the optimizer decides about if it is using a
table scan, or indexes, of other trick it may knows, dependant of the data
on hand. It is a little bit as if you were writing 3 or 4 programs to do the
job, each program better fit for a, one, particular case. By comparison,
the usual way is telling ONE way how to do the things, and hoping it will be
the best way to do it in most of the situations (or not to bad in the worst
case). SQL target is to be the best in all the cases. (Sure, it happens we
know something SQL does not and sometimes, we can come with a better
solution than SQL, given what we told to SQL).
Hoping it may help,
Vanderghast, Access MVP
