Change Between Periods

[Takeadoe]

Dear NG - I'm very new to Access and would appreciate some guidance.
My table looks like the following:

Year County VarA VarB VarC etc.
1982 Athens 900 50 11.7
1983 Athens 700 40 21

All I want to do is calculate the % change in Variables A, B, and C
from 1982 to 1983. I know this should be very easy, but I'm not sure
how to do it.

Any general guidance would really be appreciated.

Mike

[ManningFan]

Mike -
In a spreadsheet this is easy. In your table, the way it is set up,
it is not.

You have a few options. You can write the values into an array,
which would handle this particular example well. You can also
transpose your data, which would be a good idea if you plan on adding
more records to the dataset later on.

I think you should consider changing the dataset to something more
attuned to how you're going to look at it. Possibly change the
structure so there are 4 fields; Year, County, Var and Value so that:
1982 Athens VarA 900
1982 Athens VarB 50
1982 Athens VarC 11.7

You could then join the table to itself where Year = Year - 1. This
way you could have in one query:
Country Var Val1 Val2
Athens VarA 900 700

Then you would just create a new column in a query: PctChg: (Val2 -
Val1) / Val1

Takeadoe wrote:

Dear NG - I'm very new to Access and would appreciate some guidance.
My table looks like the following:

Year County VarA VarB VarC etc.
1982 Athens 900 50 11.7
1983 Athens 700 40 21

All I want to do is calculate the % change in Variables A, B, and C
from 1982 to 1983. I know this should be very easy, but I'm not sure
how to do it.

Any general guidance would really be appreciated.

Mike

[Takeadoe]

Hey - (I feel strange calling you "ManningFan" as I'm sure this isn't
your real name) - Your response just turned a big, bright light on in
this little brain of mine. The issue is database design. I've often
wondered about how to best set up my data and I think I just found the
answer. What you've proposed makes all the sense in the world! I'm
always looking at differences between years and I'm always transposing
my data to do that (in SAS, that is, as I'm new to Access). It never
occured to me to restructure my data as you've suggested.

I have separate tables with deer-car crashes, harvest, crop damage
complaints, etc. I could combine all that into a single table if I set
it up as you've suggested. My only concern would be missing values for
some fields in some years. Do you see that as a big problem?

What a great idea!

Mike

ManningFan wrote:

Mike -
In a spreadsheet this is easy. In your table, the way it is set up,
it is not.

You have a few options. You can write the values into an array,
which would handle this particular example well. You can also
transpose your data, which would be a good idea if you plan on adding
more records to the dataset later on.

I think you should consider changing the dataset to something more
attuned to how you're going to look at it. Possibly change the
structure so there are 4 fields; Year, County, Var and Value so that:
1982 Athens VarA 900
1982 Athens VarB 50
1982 Athens VarC 11.7

You could then join the table to itself where Year = Year - 1. This
way you could have in one query:
Country Var Val1 Val2
Athens VarA 900 700

Then you would just create a new column in a query: PctChg: (Val2 -
Val1) / Val1

Takeadoe wrote:

Dear NG - I'm very new to Access and would appreciate some guidance.
My table looks like the following:

Year County VarA VarB VarC etc.
1982 Athens 900 50 11.7
1983 Athens 700 40 21

All I want to do is calculate the % change in Variables A, B, and C
from 1982 to 1983. I know this should be very easy, but I'm not sure
how to do it.

Any general guidance would really be appreciated.

Mike

[ManningFan]

I don't think missing values will react any differently no matter how
you set it up. You could always just use Nz() to convert nulls to
zeroes, and error-trap or flag records that are effected by them.

If that helps you out I just ask one thing: Pay It Forward, Mike.

Takeadoe wrote:

Hey - (I feel strange calling you "ManningFan" as I'm sure this isn't
your real name) - Your response just turned a big, bright light on in
this little brain of mine. The issue is database design. I've often
wondered about how to best set up my data and I think I just found the
answer. What you've proposed makes all the sense in the world! I'm
always looking at differences between years and I'm always transposing
my data to do that (in SAS, that is, as I'm new to Access). It never
occured to me to restructure my data as you've suggested.

I have separate tables with deer-car crashes, harvest, crop damage
complaints, etc. I could combine all that into a single table if I set
it up as you've suggested. My only concern would be missing values for
some fields in some years. Do you see that as a big problem?

What a great idea!

Mike

ManningFan wrote:

Mike -
In a spreadsheet this is easy. In your table, the way it is set up,
it is not.

You have a few options. You can write the values into an array,
which would handle this particular example well. You can also
transpose your data, which would be a good idea if you plan on adding
more records to the dataset later on.

I think you should consider changing the dataset to something more
attuned to how you're going to look at it. Possibly change the
structure so there are 4 fields; Year, County, Var and Value so that:
1982 Athens VarA 900
1982 Athens VarB 50
1982 Athens VarC 11.7

You could then join the table to itself where Year = Year - 1. This
way you could have in one query:
Country Var Val1 Val2
Athens VarA 900 700

Then you would just create a new column in a query: PctChg: (Val2 -
Val1) / Val1

Takeadoe wrote:

Dear NG - I'm very new to Access and would appreciate some guidance.
My table looks like the following:
>
Year County VarA VarB VarC etc.
1982 Athens 900 50 11.7
1983 Athens 700 40 21
>
All I want to do is calculate the % change in Variables A, B, and C
from 1982 to 1983. I know this should be very easy, but I'm not sure
how to do it.
>
Any general guidance would really be appreciated.
>
Mike