Complex average query

[ashg657]

Hey guys, hope someone can help me out here...

I have a single table with roughly 12,000 records. Each entry has a period
in which it was made (periods in occordance with our company calendar Period1
is October thru Period12 which is September), also each entry has a Week in
which it was made, again in occordance with a company calendar (Week1 starts
1st Oct thru to Week52 which starts on 23rd Sept).
I have been asked to generate a report/chart which will take each Week and
produce a cumulative average. i.e.:

Week1 - sum all entries and calculate average
Week2 - sum all entries from week1 AND week2 and calculate average
Week3 - sum all entries from week1 AND week2 AND week3
Week4 - sum all entreis from week1,week2, week3 and week4 and calculate
average. Perhaps then store these calculated averages in a temp table?

Then reset the average back to ZERO to begin the next PERIOD.
So in summary, calculate cumulative averages for week1-week4 for every
period, resetting averages back to zero after each period (4 weeks).

Hope that explains enough,
please help, i am well and truly stuffed on this one. If you need more info,
happy to oblige.
Cheers, Ash.

 

[Michel Walsh]

Assuming you meant, for week 4, to average all the data for the first four
weeks, not average the 4 averages of each weeks (which CAN produce different
result)


SELECT b.iota As weekNumber, AVG(a.dataToAverage)
FROM myTable AS a INNER JOIN iotas As b
ON a.weekNumber >= b.iota
GROUP BY b.Iota



where table Iotas has one field, iota, 4 records, with values 1, 2, 3 and 4.

I assumed your original table has a field weekNumber, giving the week
number.



Hoping it may help,
Vanderghast, Access MVP

 

[ashg657]

Hi Michael,
Working from what you gave me, I altered the SQL syntax to match my
fields/tables which results in the following:

SELECT b.iota As week, AVG(a.[Value (£)])
FROM All AS a INNER JOIN iotas As b
ON a.weekNumber >= b.iota
GROUP BY b.Iota

Thats with a table called "iotas" which has one field "iota" with values 1 -
52 (52 weeks in year). But I get a syntax error in FROM clause, and it
highlights "FROM All"

Hope you can help,
Thanks!

 

[Gary Walter]

you have a table named "All" (reserved word in Access)
with a field named "Value (£)" (where "Value" is again a
reserved word, plus punctuation in a name is discouraged).

It's only 12,000 records... if you cannot change these,
try making a copy of the table (data and structure)

name it "tblAll"
name field "ValueInPds"

the other fields are?

weekNumber
Period

In iotas table, add second field "Period"
and fill in correct values for iota as a
weeknumber.

then try

SELECT
b.iota As WeekGrp,
AVG(a.ValueInPds)
FROM
tblAll AS a
INNER JOIN
iotas As b
ON
a.Period = b.Period
AND
a.weekNumber >= b.iota
GROUP BY
b.Iota

Hi Michael,
Working from what you gave me, I altered the SQL syntax to match my
fields/tables which results in the following:

SELECT b.iota As week, AVG(a.[Value (£)])
FROM All AS a INNER JOIN iotas As b
ON a.weekNumber >= b.iota
GROUP BY b.Iota

Thats with a table called "iotas" which has one field "iota" with values
1 -
52 (52 weeks in year). But I get a syntax error in FROM clause, and it
highlights "FROM All"

Hope you can help,
Thanks!

Assuming you meant, for week 4, to average all the data for the first
four
weeks, not average the 4 averages of each weeks (which CAN produce
different
result)


SELECT b.iota As weekNumber, AVG(a.dataToAverage)
FROM myTable AS a INNER JOIN iotas As b
ON a.weekNumber >= b.iota
GROUP BY b.Iota



where table Iotas has one field, iota, 4 records, with values 1, 2, 3 and
4.

I assumed your original table has a field weekNumber, giving the week
number.



Hoping it may help,
Vanderghast, Access MVP

 

[Gary Walter]

In fact, if I was testing this (which I have not),
I might temporarily add some more
aggregates to verify my logic...

SELECT
b.iota As WeekGrp,
Max(a.weekNumber) As MaxWkNum,
Min(a.weekNumber) As MinWkNum,
Count(a.ValueInPds) As GrpCnt,
Sum(a.ValueInPds) As GrpSum,
AVG(a.ValueInPds) As GrpAvg
FROM
tblAll AS a
INNER JOIN
iotas As b
ON
a.Period = b.Period
AND
a.weekNumber >= b.iota
GROUP BY
b.Iota

you have a table named "All" (reserved word in Access)
with a field named "Value (£)" (where "Value" is again a
reserved word, plus punctuation in a name is discouraged).

It's only 12,000 records... if you cannot change these,
try making a copy of the table (data and structure)

name it "tblAll"
name field "ValueInPds"

the other fields are?

weekNumber
Period

In iotas table, add second field "Period"
and fill in correct values for iota as a
weeknumber.

then try

SELECT
b.iota As WeekGrp,
AVG(a.ValueInPds)
FROM
tblAll AS a
INNER JOIN
iotas As b
ON
a.Period = b.Period
AND
a.weekNumber >= b.iota
GROUP BY
b.Iota

Hi Michael,
Working from what you gave me, I altered the SQL syntax to match my
fields/tables which results in the following:

SELECT b.iota As week, AVG(a.[Value (£)])
FROM All AS a INNER JOIN iotas As b
ON a.weekNumber >= b.iota
GROUP BY b.Iota

Thats with a table called "iotas" which has one field "iota" with values
1 -
52 (52 weeks in year). But I get a syntax error in FROM clause, and it
highlights "FROM All"

Hope you can help,
Thanks!

Assuming you meant, for week 4, to average all the data for the first
four
weeks, not average the 4 averages of each weeks (which CAN produce
different
result)


SELECT b.iota As weekNumber, AVG(a.dataToAverage)
FROM myTable AS a INNER JOIN iotas As b
ON a.weekNumber >= b.iota
GROUP BY b.Iota



where table Iotas has one field, iota, 4 records, with values 1, 2, 3
and 4.

I assumed your original table has a field weekNumber, giving the week
number.



Hoping it may help,
Vanderghast, Access MVP



Hey guys, hope someone can help me out here...

I have a single table with roughly 12,000 records. Each entry has a
period
in which it was made (periods in occordance with our company calendar
Period1
is October thru Period12 which is September), also each entry has a
Week
in
which it was made, again in occordance with a company calendar (Week1
starts
1st Oct thru to Week52 which starts on 23rd Sept).
I have been asked to generate a report/chart which will take each Week
and
produce a cumulative average. i.e.:

Week1 - sum all entries and calculate average
Week2 - sum all entries from week1 AND week2 and calculate average
Week3 - sum all entries from week1 AND week2 AND week3
Week4 - sum all entreis from week1,week2, week3 and week4 and
calculate
average. Perhaps then store these calculated averages in a temp table?

Then reset the average back to ZERO to begin the next PERIOD.
So in summary, calculate cumulative averages for week1-week4 for every
period, resetting averages back to zero after each period (4 weeks).

Hope that explains enough,
please help, i am well and truly stuffed on this one. If you need more
info,
happy to oblige.
Cheers, Ash.

 

[Michel Walsh]

.... and an error from my part, should be


ON a.weekNumber <= b.iota

rather than

ON a.weekNumber >= b.iota


since, for week b.iota=2, as example, we want data for weeks 1 and 2, not
for week 2, 3, 4, ....



Vanderghast, Access MVP

you have a table named "All" (reserved word in Access)
with a field named "Value (£)" (where "Value" is again a
reserved word, plus punctuation in a name is discouraged).

It's only 12,000 records... if you cannot change these,
try making a copy of the table (data and structure)

name it "tblAll"
name field "ValueInPds"

the other fields are?

weekNumber
Period

In iotas table, add second field "Period"
and fill in correct values for iota as a
weeknumber.

then try

SELECT
b.iota As WeekGrp,
AVG(a.ValueInPds)
FROM
tblAll AS a
INNER JOIN
iotas As b
ON
a.Period = b.Period
AND
a.weekNumber >= b.iota
GROUP BY
b.Iota

Hi Michael,
Working from what you gave me, I altered the SQL syntax to match my
fields/tables which results in the following:

SELECT b.iota As week, AVG(a.[Value (£)])
FROM All AS a INNER JOIN iotas As b
ON a.weekNumber >= b.iota
GROUP BY b.Iota

Thats with a table called "iotas" which has one field "iota" with values
1 -
52 (52 weeks in year). But I get a syntax error in FROM clause, and it
highlights "FROM All"

Hope you can help,
Thanks!

Assuming you meant, for week 4, to average all the data for the first
four
weeks, not average the 4 averages of each weeks (which CAN produce
different
result)


SELECT b.iota As weekNumber, AVG(a.dataToAverage)
FROM myTable AS a INNER JOIN iotas As b
ON a.weekNumber >= b.iota
GROUP BY b.Iota



where table Iotas has one field, iota, 4 records, with values 1, 2, 3
and 4.

I assumed your original table has a field weekNumber, giving the week
number.



Hoping it may help,
Vanderghast, Access MVP



Hey guys, hope someone can help me out here...

I have a single table with roughly 12,000 records. Each entry has a
period
in which it was made (periods in occordance with our company calendar
Period1
is October thru Period12 which is September), also each entry has a
Week
in
which it was made, again in occordance with a company calendar (Week1
starts
1st Oct thru to Week52 which starts on 23rd Sept).
I have been asked to generate a report/chart which will take each Week
and
produce a cumulative average. i.e.:

Week1 - sum all entries and calculate average
Week2 - sum all entries from week1 AND week2 and calculate average
Week3 - sum all entries from week1 AND week2 AND week3
Week4 - sum all entreis from week1,week2, week3 and week4 and
calculate
average. Perhaps then store these calculated averages in a temp table?

Then reset the average back to ZERO to begin the next PERIOD.
So in summary, calculate cumulative averages for week1-week4 for every
period, resetting averages back to zero after each period (4 weeks).

Hope that explains enough,
please help, i am well and truly stuffed on this one. If you need more
info,
happy to oblige.
Cheers, Ash.

 

[Gary Walter]

or...

SELECT
b.iota As WeekGrp,
b.Period As GrpPeriod,
Max(a.weekNumber) As MaxWkNum,
Min(a.weekNumber) As MinWkNum,
Count(a.ValueInPds) As GrpCnt,
Sum(a.ValueInPds) As GrpSum,
AVG(a.ValueInPds) As GrpAvg
FROM
tblAll AS a
INNER JOIN
iotas As b
ON
a.Period = b.Period
AND
a.weekNumber >= b.iota
GROUP BY
b.Iota,
b.Period;

 

[ashg657]

thanks guys, as for the reserved access words, yes i know. This is only a
temporary database we are using to create a chart in the end and to get some
statistics from. afterward it is no longer needed, hence why i wanted 2 get
it done as quick as possible, and therefore didnt worry about fields names
etc.
Thanks again! Think we've managed to get there in the end with ur help.

.... and an error from my part, should be


ON a.weekNumber <= b.iota

rather than

ON a.weekNumber >= b.iota


since, for week b.iota=2, as example, we want data for weeks 1 and 2, not
for week 2, 3, 4, ....



Vanderghast, Access MVP

you have a table named "All" (reserved word in Access)
with a field named "Value (£)" (where "Value" is again a
reserved word, plus punctuation in a name is discouraged).

It's only 12,000 records... if you cannot change these,
try making a copy of the table (data and structure)

name it "tblAll"
name field "ValueInPds"

the other fields are?

weekNumber
Period

In iotas table, add second field "Period"
and fill in correct values for iota as a
weeknumber.

then try

SELECT
b.iota As WeekGrp,
AVG(a.ValueInPds)
FROM
tblAll AS a
INNER JOIN
iotas As b
ON
a.Period = b.Period
AND
a.weekNumber >= b.iota
GROUP BY
b.Iota

Hi Michael,
Working from what you gave me, I altered the SQL syntax to match my
fields/tables which results in the following:

SELECT b.iota As week, AVG(a.[Value (£)])
FROM All AS a INNER JOIN iotas As b
ON a.weekNumber >= b.iota
GROUP BY b.Iota

Thats with a table called "iotas" which has one field "iota" with values
1 -
52 (52 weeks in year). But I get a syntax error in FROM clause, and it
highlights "FROM All"

Hope you can help,
Thanks!

:

Assuming you meant, for week 4, to average all the data for the first
four
weeks, not average the 4 averages of each weeks (which CAN produce
different
result)


SELECT b.iota As weekNumber, AVG(a.dataToAverage)
FROM myTable AS a INNER JOIN iotas As b
ON a.weekNumber >= b.iota
GROUP BY b.Iota



where table Iotas has one field, iota, 4 records, with values 1, 2, 3
and 4.

I assumed your original table has a field weekNumber, giving the week
number.



Hoping it may help,
Vanderghast, Access MVP



Hey guys, hope someone can help me out here...

I have a single table with roughly 12,000 records. Each entry has a
period
in which it was made (periods in occordance with our company calendar
Period1
is October thru Period12 which is September), also each entry has a
Week
in
which it was made, again in occordance with a company calendar (Week1
starts
1st Oct thru to Week52 which starts on 23rd Sept).
I have been asked to generate a report/chart which will take each Week
and
produce a cumulative average. i.e.:

Week1 - sum all entries and calculate average
Week2 - sum all entries from week1 AND week2 and calculate average
Week3 - sum all entries from week1 AND week2 AND week3
Week4 - sum all entreis from week1,week2, week3 and week4 and
calculate
average. Perhaps then store these calculated averages in a temp table?

Then reset the average back to ZERO to begin the next PERIOD.
So in summary, calculate cumulative averages for week1-week4 for every
period, resetting averages back to zero after each period (4 weeks).

Hope that explains enough,
please help, i am well and truly stuffed on this one. If you need more
info,
happy to oblige.
Cheers, Ash.