Need help w/ difficult query

[Stephanie]

Help! I'm a beginner!

I have a table with historic health insurance rates from
year to year. I need help with creating a calculated
field that will show the % increase from year to year.

The table is designed like this...

rate eff date option tier1rate tier2rate tier3rate

example:

1/1/01 A $100 $125 $150
1/1/01 B $200 $225 $250
1/1/02 A $125 $150 $175
1/1/02 B $225 $250 $275

I want to be able to run a query that says
1/1/2002 A 25% (from year before) (and do it for
each tier)

Any guidance would be appreciated!

 

[Tom Ellison]

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.

 

[Stephanie]

Whoa....You lost me! Did I forget to emphasis I was a
beginner? I think I am over my head. Hope these don't
sound like a dumb question


After selecting the fields [option], [rate eff date]
[tier1]

is the rest to be done in a calcuated field?
"Nz"-???? What is that short for

You would be saving me from utter confusement if you could
break it down into baby steps!

-----Original Message-----
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.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Help! I'm a beginner!

I have a table with historic health insurance rates from
year to year. I need help with creating a calculated
field that will show the % increase from year to year.

The table is designed like this...

rate eff date option tier1rate tier2rate tier3rate

example:

1/1/01 A $100 $125 $150
1/1/01 B $200 $225 $250
1/1/02 A $125 $150 $175
1/1/02 B $225 $250 $275

I want to be able to run a query that says
1/1/2002 A 25% (from year before) (and do it for
each tier)

Any guidance would be appreciated!

.

 

[Tom Ellison]

Dear Stephanie:

The Nz function changes NULL results to zeros. You can see this in online
help. I will leave it off in the simplified version I use here to explain
it and you will see NULLS for the value when there is no previous [rate eff
date], as for the first row of each [option] set.

A simplified version of the query is:

SELECT [option], [rate eff date], [tier1],
(SELECT tier1 FROM YourTable T1
WHERE T1.option = T.option
AND T1.[rate eff date] = DateAdd("yyyy", T.[rate eff date], -1))
AS PreviousYearRate
FROM YourTable T
ORDER BY [option], [rate eff date]

This shows the rate for Tier1 of the previous year, and is null when there
is not record for that previous year on the exact date one year before. I
suggested in my sample results that you would want to show this as a 0%
change from the previous year, and for that I used Nz() in the calculation.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Whoa....You lost me! Did I forget to emphasis I was a
beginner? I think I am over my head. Hope these don't
sound like a dumb question


After selecting the fields [option], [rate eff date]
[tier1]

is the rest to be done in a calcuated field?
"Nz"-???? What is that short for

You would be saving me from utter confusement if you could
break it down into baby steps!

-----Original Message-----
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.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Help! I'm a beginner!

I have a table with historic health insurance rates from
year to year. I need help with creating a calculated
field that will show the % increase from year to year.

The table is designed like this...

rate eff date option tier1rate tier2rate tier3rate

example:

1/1/01 A $100 $125 $150
1/1/01 B $200 $225 $250
1/1/02 A $125 $150 $175
1/1/02 B $225 $250 $275

I want to be able to run a query that says
1/1/2002 A 25% (from year before) (and do it for
each tier)

Any guidance would be appreciated!

.

 

[Tom Ellison]

Duane is exactly correct in this. I did not cover this, but the solution I
gave you for only one tier is readily extensible if you restructure the data
as he suggests.

--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Stephanie,
Are you stuck with the current table structure? A more normalized table
structure would have only one rate amount per record. Your fields for
TierXRates is one source of your troubles. If you can't normalize the table
then you could first use a UNION query to normalize and then create a query
based on the union query.
First create the union query substituting your table name for tblTierRates:
== quniTierRates =====================
SELECT RateEffDate, Option, Tier1Rate AS Rate, "Tier1" AS Tier
FROM tblTierRates
UNION ALL
SELECT RateEffDate, Option, Tier2Rate, "Tier2"
FROM tblTierRates
UNION ALL SELECT RateEffDate, Option, Tier3Rate, "Tier3"
FROM tblTierRates;

Then use quniTierRates for your final query:

SELECT quniTierRates.RateEffDate, quniTierRates.Option, quniTierRates.Rate,
quniTierRates.Tier, quniTierRates_1.Rate AS NextYearRate,
([quniTierRates_1].[Rate]-[quniTierRates].[Rate])/[quniTierRates].[Rate] AS
Increase
FROM quniTierRates AS quniTierRates_1 INNER JOIN quniTierRates ON
(quniTierRates_1.Option = quniTierRates.Option) AND (quniTierRates_1.Tier =

quniTierRates.Tier)
WHERE

(((quniTierRates.RateEffDate)=DateAdd("yyyy",-1,[quniTierRates_1].[RateEffDa
te])));


--
Duane Hookom
MS Access MVP

Whoa....You lost me! Did I forget to emphasis I was a
beginner? I think I am over my head. Hope these don't
sound like a dumb question


After selecting the fields [option], [rate eff date]
[tier1]

is the rest to be done in a calcuated field?
"Nz"-???? What is that short for

You would be saving me from utter confusement if you could
break it down into baby steps!

-----Original Message-----
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.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Help! I'm a beginner!

I have a table with historic health insurance rates from
year to year. I need help with creating a calculated
field that will show the % increase from year to year.

The table is designed like this...

rate eff date option tier1rate tier2rate tier3rate

example:

1/1/01 A $100 $125 $150
1/1/01 B $200 $225 $250
1/1/02 A $125 $150 $175
1/1/02 B $225 $250 $275

I want to be able to run a query that says
1/1/2002 A 25% (from year before) (and do it for
each tier)

Any guidance would be appreciated!



.