Hi Raj,
Correlated subqueries were difficult for me to
understand at first until I "slowed everything
down" into a pseudo step-by-step process.
{first I'm going to change your field names
to "AYear", "AMonth", and "AValue" since
all 3 of your test case fields are reserved names
in Access}
Ignoring the subquery for now, imagine the
step-by-step retrieval of records for
SELECT
AYear,
AMonth,
AValue
FROM
Table1
ORDER BY
AYear,
AMonth,
AValue;
- get first record and return
AYear=2003
AMonth =1
AValue = 10
- get next record and return
AYear=2003
AMonth =2
AValue = 20
etc...easy enough to visualize...
step-by-step until reach last record
in Table1
Now, let's choose one "step" in the process
where
Table1.AYear=2004
Table1.AMonth =2
Table1.AValue = 6
Imagine that before we move to the
next "step" we'd like to compute the
Avg of "AValue" for this AYear/AMonth
and the 11 previous records.
Let's design the query for this *one step.*
Start a new query in query designer (QBE)
Click on "Create Query in Design View"
In the Show Table dialog box,
click on your table,
click Add,
and then click Close.
Right-mouse click on the table
and choose Properties.
In the Alias row, type in
t
then close the Properties dialog box.
Drag and Drop AYear field from
table down in to field row
of first column of grid.
Drag and Drop AMonth field from
table down in to field row
of second column of grid.
Drag and Drop AValue field from
table down in to field row
of third column of grid.
In the the field row of fourth column
of grid, type the following (all one line):
DiffMonths:
DateDiff("m",DateSerial([t].[AYear],[t].[AMonth],1),DateSerial(2004,2,1))
take a look at the results so far
AYear AMonth AValue DiffMonths
2003 1 10 13
2003 2 20 12
2003 3 30 11 <--***
2003 4 35 10
2003 5 50 9
2003 6 51 8
2003 7 34 7
2003 8 67 6
2003 9 44 5
2003 10 36 4
2003 11 65 3
2003 12 49 2
2004 1 52 1
2004 2 6 0 <---***
2004 3 2 -1
2004 4 39 -2
2004 5 41 -3
2004 6 30 -4
2004 7 46 -5
2004 8 32 -6
2004 9 61 -7
2004 10 47 -8
2004 11 37 -9
2004 12 50 -10
It should be apparent that if we put
the following in the Criteria row under
DiffMonths column
BETWEEN 0 AND 11
we will get AValue's for the last 12 months
for specific record "2004/2"
To get the AVG, all we have to do is
change to a Totals query by clicking on
"Sigma" icon in top toolbar,
delete first 2 columns,
change "Total:" row under "AValue"
to "Avg",
change "Total:" row under "DiffMonths"
to "WHERE"
save query and run... AvgAValue = 43.25
Change to SQL view
SELECT Avg(t.AValue)
FROM Table1 AS t
WHERE
DateDiff("m",DateSerial([t].[AYear],[t].[AMonth],1),
DateSerial(2004,2,1))
Between 0 And 11;
You just constructed your "uncorrelated" subquery.
This is the *specific* subquery we want
to run when we reach the "step" in our original
query where
Table1.AYear=2004
Table1.AMonth =2
Table1.AValue = 6
Back to our "step-by-step" query,
each time we look at a record in Table1,
we want to compute this AVG
using Table1's [AYear] and [AMonth].
So...we *correlate* it back substituting
Table1.AYear for "2004"
and Table1.AMonth for "2"
In our "step-by-step" query,
we type the following in a Field row
of a separate column (all on one line):
12MoAvg: (SELECT Avg(t.AValue)
FROM Table1 AS t
WHERE
DateDiff("m",DateSerial([t].[AYear],[t].[AMonth],1),
DateSerial(Table1.AYear ,Table1.AMonth ,1))
Between 0 And 11)
Your full SQL should now look like:
SELECT
AYear,
AMonth,
AValue,
(SELECT Avg(t.AValue)
FROM Table1 AS t
WHERE
DateDiff("m",DateSerial([t].[AYear],[t].[AMonth],1),
DateSerial(Table1.AYear ,Table1.AMonth ,1))
Between 0 And 11) AS 12MoAvg
FROM
Table1
ORDER BY
AYear,
AMonth,
AValue;
the results may need some further tinkering
AYear AMonth AValue 12MoAvg
2003 1 10 10
2003 2 20 15
2003 3 30 20
2003 4 35 23.75
2003 5 50 29
2003 6 51 32.6666666666667
2003 7 34 32.8571428571429
2003 8 67 37.125
2003 9 44 37.8888888888889
2003 10 36 37.7
2003 11 65 40.1818181818182
2003 12 49 40.9166666666667
2004 1 52 44.4166666666667
2004 2 6 43.25
2004 3 2 40.9166666666667
2004 4 39 41.25
2004 5 41 40.5
2004 6 30 38.75
2004 7 46 39.75
2004 8 32 36.8333333333333
2004 9 61 38.25
2004 10 47 39.1666666666667
2004 11 37 36.8333333333333
2004 12 50 36.9166666666667
I'd probably just "tinker" in a report based on the query.
good luck,
gary
jar said:
I used the code that was on this group in MS Access but i get the same
value in each row for 12moAvg calculated using the code below. I am not
able to reproduce the results for the test case. I am going in circles.
Can anyone please help me.
My code is as below followed by one in this group.
Thanks in advance for all your help.
Raj
------
SELECT Table1.*, (SELECT AVG( j.[value] ) FROM table1 j WHERE
DateDiff("m",DateSerial(j.Year,j.month,1),DateSerial(j.Year,j.Month,1))
Between 0 and 11) AS a
FROM Table1;
----------------------------------
Year Month Value 12MoAvg
2003 1 10
2003 2 20
2003 3 30
2003 4 35
2003 5 50
2003 6 51
2003 7 34
2003 8 67
2003 9 44
2003 10 36
2003 11 65
2003 12 49 40.92
2004 1 52 44.42
2004 2 6 43.25
2004 3 2 40.92
2004 4 39 41.25
2004 5 41 40.50
2004 6 30 38.75
2004 7 46 39.75
2004 8 32 36.83
2004 9 61 38.25
2004 10 47 39.17
2004 11 37 36.83
2004 12 50 36.92
--------------------------------------------
Try starting with something like:
SELECT tblJohnstone.*,
(SELECT AVG(Value)
FROM tblJohnstone j
WHERE DateDiff("m",DateSerial(j.Year,j.month,1),
DateSerial(tblJohnstone.Year,tblJohnstone.Month,1))
Between 0 and 11) AS MA12
FROM tblJohnstone;
