Calculated query help please

[Flynny]

G'day, I have a table in my database with the following fields:

Centre
Month
Sales
Budget

There are more fields but these are the only relevant ones. Basically the
table records sales and budget figures for each of our 71 centres. I could
not create a field for every month because the data goes back to July 99 and
also found it difficult to create a graph mapping sales against budget when
each month was a field, so I have done it that way.

Essentially I want to try and create a report that automatically does what I
manually do in Excel, which is a determination of a monthly award for the
best growth. Growth is determined for a particular month, lets say Dec 05, by
adding sales figures for the six months Jan 05 to June 05, then add Jul 05 x
growth factor of 1.5, then add Aug 05 x GF of 2.25 and so on to Nov 05 X GF
of 11.59, then divide the resulting figure by the total of the GF's, in this
case 37.17. This growth factor is then calcaluted as a percentage against
actual sales and the centre with the highest percentage wins the monthly
award - make sense, hope so.

So you can see that within the [month] field i need to calculate against
sales recorded for each month going back 12 months for each current month.
Crikey I hope this makes sense to someone. It does to me but I'm writing it.

Anyway, does anyone get what I am saying and if so, please advise of a
solution if there is one. Cheers.

 

[Tom Ellison]

Dear Flynny:

Your decision to put a "Month" column in the table is best anyway. You
don't want to have to add a new column to the table every month. This is
actually the best practice.

Your problem makes very good sense. The solution will be a subquery
accessing the "previous" month's figures. I presume it will be the maximum
value of Month that is less than the current value of Month, and for the
same Centre.

"Crikey"? If it weren't for that guy on TV who wrestles alligators in
Australia, I wouldn't know what that is. So, you're saying there's an
alligator in your code. That's very descriptive!

A bit of detail, please. What kind of data is in the Month column?

I'll give it a try, anyway:

SELECT Centre, Month, Sales, Budget,
(SELECT Sales
FROM YourTable T1
WHERE T1.Centre = T.Centre
AND T1.Month =
(SELECT MAX(Month)
FROM YourTable T2
WHERE T2.Centre = T.Centre
AND T2.Month < T.Month))
AS PrevSales
FROM YourTable T
ORDER BY Centre, Month

I believe this may return the value of Sales (or any other column needed)
from that previous month's row. Warning: Access Jet sometines has problems
with this sort of thing. I've tried to steer clear of that and I think I
have avoided the known problems, but I'm not sure. This isn't documented to
my knowledge - it's just based on experience. You may need to build a
separate query for the two inner levels of subquery, then use that in place
of the whole subquery structure above. More on that later, if you have the
problem.

You could then use this in whatever calculation you have.

What I'm saying is, the above is (I certainly hope) good SQL, but may not
work in Jet. If you have a problem with that, please get back to me with
details.

Be sure to substitute the actual name of your table where the above says
YourTable, OK?

Tom Ellison

G'day, I have a table in my database with the following fields:

Centre
Month
Sales
Budget

There are more fields but these are the only relevant ones. Basically the
table records sales and budget figures for each of our 71 centres. I could
not create a field for every month because the data goes back to July 99
and
also found it difficult to create a graph mapping sales against budget
when
each month was a field, so I have done it that way.

Essentially I want to try and create a report that automatically does what
I
manually do in Excel, which is a determination of a monthly award for the
best growth. Growth is determined for a particular month, lets say Dec 05,
by
adding sales figures for the six months Jan 05 to June 05, then add Jul 05
x
growth factor of 1.5, then add Aug 05 x GF of 2.25 and so on to Nov 05 X
GF
of 11.59, then divide the resulting figure by the total of the GF's, in
this
case 37.17. This growth factor is then calcaluted as a percentage against
actual sales and the centre with the highest percentage wins the monthly
award - make sense, hope so.

So you can see that within the [month] field i need to calculate against
sales recorded for each month going back 12 months for each current month.
Crikey I hope this makes sense to someone. It does to me but I'm writing
it.

Anyway, does anyone get what I am saying and if so, please advise of a
solution if there is one. Cheers.

 

[Flynny]

Cheers Tom for the quick reply. I am not completely sure I follow what your
saying, I think do a bit.

Basically there are 71 separate centres, each with a row for each month
dating back to July 99. The 'months' are entered as '1/12/05' (Aussie date
format) for Dec 05 for example, all months are shown as the first day of the
actual month.

Then for dec 05, for each centre, I need to add the first six months of the
preceding 12 months, then add month 7 times the growth factor and so forth to
the 12th month.

So the table looks like this:

Centre 1 1/1/05 $10,000
Centre 1 1/2/05 $15,000
etc
Centre 2 1/1/05 $10,000
Centre 2 1/2/05 $15,000
etc

How do I extract the values for the past 12 months on a month by month basis
for every centre for the purpose of the formula.

Are you

Dear Flynny:

Your decision to put a "Month" column in the table is best anyway. You
don't want to have to add a new column to the table every month. This is
actually the best practice.

Your problem makes very good sense. The solution will be a subquery
accessing the "previous" month's figures. I presume it will be the maximum
value of Month that is less than the current value of Month, and for the
same Centre.

"Crikey"? If it weren't for that guy on TV who wrestles alligators in
Australia, I wouldn't know what that is. So, you're saying there's an
alligator in your code. That's very descriptive!

A bit of detail, please. What kind of data is in the Month column?

I'll give it a try, anyway:

SELECT Centre, Month, Sales, Budget,
(SELECT Sales
FROM YourTable T1
WHERE T1.Centre = T.Centre
AND T1.Month =
(SELECT MAX(Month)
FROM YourTable T2
WHERE T2.Centre = T.Centre
AND T2.Month < T.Month))
AS PrevSales
FROM YourTable T
ORDER BY Centre, Month

I believe this may return the value of Sales (or any other column needed)
from that previous month's row. Warning: Access Jet sometines has problems
with this sort of thing. I've tried to steer clear of that and I think I
have avoided the known problems, but I'm not sure. This isn't documented to
my knowledge - it's just based on experience. You may need to build a
separate query for the two inner levels of subquery, then use that in place
of the whole subquery structure above. More on that later, if you have the
problem.

You could then use this in whatever calculation you have.

What I'm saying is, the above is (I certainly hope) good SQL, but may not
work in Jet. If you have a problem with that, please get back to me with
details.

Be sure to substitute the actual name of your table where the above says
YourTable, OK?

Tom Ellison

G'day, I have a table in my database with the following fields:

Centre
Month
Sales
Budget

There are more fields but these are the only relevant ones. Basically the
table records sales and budget figures for each of our 71 centres. I could
not create a field for every month because the data goes back to July 99
and
also found it difficult to create a graph mapping sales against budget
when
each month was a field, so I have done it that way.

Essentially I want to try and create a report that automatically does what
I
manually do in Excel, which is a determination of a monthly award for the
best growth. Growth is determined for a particular month, lets say Dec 05,
by
adding sales figures for the six months Jan 05 to June 05, then add Jul 05
x
growth factor of 1.5, then add Aug 05 x GF of 2.25 and so on to Nov 05 X
GF
of 11.59, then divide the resulting figure by the total of the GF's, in
this
case 37.17. This growth factor is then calcaluted as a percentage against
actual sales and the centre with the highest percentage wins the monthly
award - make sense, hope so.

So you can see that within the [month] field i need to calculate against
sales recorded for each month going back 12 months for each current month.
Crikey I hope this makes sense to someone. It does to me but I'm writing
it.

Anyway, does anyone get what I am saying and if so, please advise of a
solution if there is one. Cheers.

 

[Tom Ellison]

Dear Flynny:

Is the Month a date/time datatype? Or is it text?

Just so you understand, a date/time column is not in any "format." A format
is what is applied to the data in order to display it, and has nothing to do
with how it is stored, or how it is sorted.

Did you try the query I provided? What does it do?

Tom Ellison

Cheers Tom for the quick reply. I am not completely sure I follow what
your
saying, I think do a bit.

Basically there are 71 separate centres, each with a row for each month
dating back to July 99. The 'months' are entered as '1/12/05' (Aussie date
format) for Dec 05 for example, all months are shown as the first day of
the
actual month.

Then for dec 05, for each centre, I need to add the first six months of
the
preceding 12 months, then add month 7 times the growth factor and so forth
to
the 12th month.

So the table looks like this:

Centre 1 1/1/05 $10,000
Centre 1 1/2/05 $15,000
etc
Centre 2 1/1/05 $10,000
Centre 2 1/2/05 $15,000
etc

How do I extract the values for the past 12 months on a month by month
basis
for every centre for the purpose of the formula.

Are you

Dear Flynny:

Your decision to put a "Month" column in the table is best anyway. You
don't want to have to add a new column to the table every month. This is
actually the best practice.

Your problem makes very good sense. The solution will be a subquery
accessing the "previous" month's figures. I presume it will be the
maximum
value of Month that is less than the current value of Month, and for the
same Centre.

"Crikey"? If it weren't for that guy on TV who wrestles alligators in
Australia, I wouldn't know what that is. So, you're saying there's an
alligator in your code. That's very descriptive!

A bit of detail, please. What kind of data is in the Month column?

I'll give it a try, anyway:

SELECT Centre, Month, Sales, Budget,
(SELECT Sales
FROM YourTable T1
WHERE T1.Centre = T.Centre
AND T1.Month =
(SELECT MAX(Month)
FROM YourTable T2
WHERE T2.Centre = T.Centre
AND T2.Month < T.Month))
AS PrevSales
FROM YourTable T
ORDER BY Centre, Month

I believe this may return the value of Sales (or any other column needed)
from that previous month's row. Warning: Access Jet sometines has
problems
with this sort of thing. I've tried to steer clear of that and I think I
have avoided the known problems, but I'm not sure. This isn't documented
to
my knowledge - it's just based on experience. You may need to build a
separate query for the two inner levels of subquery, then use that in
place
of the whole subquery structure above. More on that later, if you have
the
problem.

You could then use this in whatever calculation you have.

What I'm saying is, the above is (I certainly hope) good SQL, but may not
work in Jet. If you have a problem with that, please get back to me with
details.

Be sure to substitute the actual name of your table where the above says
YourTable, OK?

Tom Ellison

G'day, I have a table in my database with the following fields:

Centre
Month
Sales
Budget

There are more fields but these are the only relevant ones. Basically
the
table records sales and budget figures for each of our 71 centres. I
could
not create a field for every month because the data goes back to July
99
and
also found it difficult to create a graph mapping sales against budget
when
each month was a field, so I have done it that way.

Essentially I want to try and create a report that automatically does
what
I
manually do in Excel, which is a determination of a monthly award for
the
best growth. Growth is determined for a particular month, lets say Dec
05,
by
adding sales figures for the six months Jan 05 to June 05, then add Jul
05
x
growth factor of 1.5, then add Aug 05 x GF of 2.25 and so on to Nov 05
X
GF
of 11.59, then divide the resulting figure by the total of the GF's, in
this
case 37.17. This growth factor is then calcaluted as a percentage
against
actual sales and the centre with the highest percentage wins the
monthly
award - make sense, hope so.

So you can see that within the [month] field i need to calculate
against
sales recorded for each month going back 12 months for each current
month.
Crikey I hope this makes sense to someone. It does to me but I'm
writing
it.

Anyway, does anyone get what I am saying and if so, please advise of a
solution if there is one. Cheers.