S
Stephanie
Tom-
You were kind enough to answer my question concerning
my "difficult query" -however I have a few more
questions! Obviously I am very much the beginner and I
hope this does not sound dumb! I looked up the meaning of
a subquery and the nz function-however I think I need you
to help me break your info down to baby steps
Below is you answer:
Dear Stephanie:
For each row, you need to also locate the value for that
same option the
year before. Of course, that row may not exist. This is
definitely true
for the first year values.
Finding the value for the previous year needs to be based
on the [rate eff
date] column you show. A subquery is used to find this
value.
Because this needs to be grouped by [option] I suggest the
results should be
structured more like this:
option rate eff date tier1
tier2 tier3
A 1/1/01 $100.00 0% $125.00 0%
$150.00 0%
A 1/1/02 $125.00 25% $150.00 20%
$175.00 17%
B 1/1/01 . . .
Neglecting tier2 and tier3 (which are done identically), a
query to do this
could look like this:
SELECT [option], [rate eff date], [tier1]
tier1 - Nz(SELECT tier1 FROM YourTable T1
WHERE T1.option = T.option AND T1.[rate eff date] =
DateAdd("yyyy",
T.[rate eff date], -1), T.tier1) /
Nz(SELECT tier1 FROM YourTable T1
WHERE T1.option = T.option AND T1.[rate eff date] =
DateAdd("yyyy",
T.[rate eff date], -1), 1) * 100
AS PercentChange
FROM YourTable T
ORDER BY [option], [rate eff date]
You must substitute the actual name of YourTable.
I recommend you get this working for one tier, than add
the others
My Question:
Do I type your "string" in the Critera line of the Tier1
field? I am really confused by T1.option. I typed
literaly what you typed (except I inserted "historic
rates" for YOURTABLE" and I couldn't get it to work!!
Your help would be much appreciated. I am so confused and
in over my head! Can you help
You were kind enough to answer my question concerning
my "difficult query" -however I have a few more
questions! Obviously I am very much the beginner and I
hope this does not sound dumb! I looked up the meaning of
a subquery and the nz function-however I think I need you
to help me break your info down to baby steps
Below is you answer:
Dear Stephanie:
For each row, you need to also locate the value for that
same option the
year before. Of course, that row may not exist. This is
definitely true
for the first year values.
Finding the value for the previous year needs to be based
on the [rate eff
date] column you show. A subquery is used to find this
value.
Because this needs to be grouped by [option] I suggest the
results should be
structured more like this:
option rate eff date tier1
tier2 tier3
A 1/1/01 $100.00 0% $125.00 0%
$150.00 0%
A 1/1/02 $125.00 25% $150.00 20%
$175.00 17%
B 1/1/01 . . .
Neglecting tier2 and tier3 (which are done identically), a
query to do this
could look like this:
SELECT [option], [rate eff date], [tier1]
tier1 - Nz(SELECT tier1 FROM YourTable T1
WHERE T1.option = T.option AND T1.[rate eff date] =
DateAdd("yyyy",
T.[rate eff date], -1), T.tier1) /
Nz(SELECT tier1 FROM YourTable T1
WHERE T1.option = T.option AND T1.[rate eff date] =
DateAdd("yyyy",
T.[rate eff date], -1), 1) * 100
AS PercentChange
FROM YourTable T
ORDER BY [option], [rate eff date]
You must substitute the actual name of YourTable.
I recommend you get this working for one tier, than add
the others
My Question:
Do I type your "string" in the Critera line of the Tier1
field? I am really confused by T1.option. I typed
literaly what you typed (except I inserted "historic
rates" for YOURTABLE" and I couldn't get it to work!!
Your help would be much appreciated. I am so confused and
in over my head! Can you help