Database design - incorporate year of a certain event

[Kevin]

I have a problem I'm trying to figure out how to resolve in Access. I
have a volunteer database for a project which occurs each year. Currently
I have to go in and wipe out the data each year to start new. I want to
somehow be able to incorporate the year's info into the database.

For instance, keep 2005's information, and add 2006's. So next year I can
pull up 2005 and say "oh yeah, so-and-so participated that year". Any
ideas?

Kevin

[Mike Preston]

On Mon, 19 Dec 2005 06:27:13 GMT, Kevin <wi******@hotmail.com> wrote:

I have a problem I'm trying to figure out how to resolve in Access. I
have a volunteer database for a project which occurs each year. Currently
I have to go in and wipe out the data each year to start new. I want to
somehow be able to incorporate the year's info into the database.

For instance, keep 2005's information, and add 2006's. So next year I can
pull up 2005 and say "oh yeah, so-and-so participated that year". Any
ideas?

Kevin, this is such a trivial matter that I must not be understanding
your question. You have a table. That table has information in it.
You add a field called "ProjectYear". You enter a year (2004, 2005,
etc.) into that field.

Your database now has the ability to distinguish based on the year.

What am I missing?

mike

[Terry Kreft]

I think maybe I'm missing something, why don't you add a field to the
relevant tables and store the year in it ?

--
Terry Kreft

"Kevin" <wi******@hotmail.com> wrote in message
news:pa****************************@hotmail.com...

I have a problem I'm trying to figure out how to resolve in Access. I
have a volunteer database for a project which occurs each year. Currently
I have to go in and wipe out the data each year to start new. I want to
somehow be able to incorporate the year's info into the database.

For instance, keep 2005's information, and add 2006's. So next year I can
pull up 2005 and say "oh yeah, so-and-so participated that year". Any
ideas?

Kevin

[Kevin]

On Mon, 19 Dec 2005 09:31:06 +0000, Mike Preston wrote:

On Mon, 19 Dec 2005 06:27:13 GMT, Kevin <wi******@hotmail.com> wrote:

I have a problem I'm trying to figure out how to resolve in Access. I
have a volunteer database for a project which occurs each year.
Currently I have to go in and wipe out the data each year to start new.
I want to somehow be able to incorporate the year's info into the
database.

For instance, keep 2005's information, and add 2006's. So next year I
can pull up 2005 and say "oh yeah, so-and-so participated that year".
Any ideas?

Kevin, this is such a trivial matter that I must not be understanding your
question. You have a table. That table has information in it. You add a
field called "ProjectYear". You enter a year (2004, 2005, etc.) into that
field.

Your database now has the ability to distinguish based on the year.

What am I missing?

mike

Maybe I'm just thinking about it too much. A field "ProjectYear" would be
fine, but if the database is used year after year, and one volunteer's
data is in there from the previous year, it would already say "2005" for
example. What happens the next year? If I change it to "2006", I no
longer have a record of them taking part in 2005. So, I guess the
question is, given a volunteer table (name, address, volunteer site, etc.)
how do I relate the years participated?

Kevin

[Keith Wilby]

"Kevin" <wi******@hotmail.com> wrote in message
news:pa****************************@hotmail.com...

On Mon, 19 Dec 2005 09:31:06 +0000, Mike Preston wrote:

Maybe I'm just thinking about it too much. A field "ProjectYear" would be
fine, but if the database is used year after year, and one volunteer's
data is in there from the previous year, it would already say "2005" for
example. What happens the next year? If I change it to "2006", I no
longer have a record of them taking part in 2005. So, I guess the
question is, given a volunteer table (name, address, volunteer site, etc.)
how do I relate the years participated?

You'd just copy the 2005 record and replace the year in the new record,
shirley?

Keith.
www.keithwilby.com

[jeremygetsmail]

I think I get it. It sounds like you have a table that lists people and
also contains information about their participation in your program.
You should build a second table. one table will hold a list of the
people, and any information that describes those people. That table
will have an autonumber primary key (check help if those terms are new
to you). The second table will have information regarding the
participation of these people in your probram, including that field
ProjecYear.

For a great discussion of why this is the right approach, check out
Paul Litwin's article "The Fundamentals of Relational Database Design".
It's posted, with Paul's permission, on my old website at
http://www.abcdatabase.com, in the developers's section.

Jeremy

[David W. Fenton]

je************@gmail.com wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:

I think I get it. It sounds like you have a table that lists
people and also contains information about their participation in
your program. You should build a second table. one table will hold
a list of the people, and any information that describes those
people. That table will have an autonumber primary key (check help
if those terms are new to you). The second table will have
information regarding the participation of these people in your
probram, including that field ProjecYear.

And this second table will have a record for each person for each
year they participated.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Kevin]

On Mon, 19 Dec 2005 09:05:08 -0800, jeremygetsmail wrote:

I think I get it. It sounds like you have a table that lists people and
also contains information about their participation in your program. You
should build a second table. one table will hold a list of the people, and
any information that describes those people. That table will have an
autonumber primary key (check help if those terms are new to you). The
second table will have information regarding the participation of these
people in your probram, including that field ProjecYear.

For a great discussion of why this is the right approach, check out Paul
Litwin's article "The Fundamentals of Relational Database Design". It's
posted, with Paul's permission, on my old website at
http://www.abcdatabase.com, in the developers's section.

Jeremy

Hit the nail on the head, Jeremy. I'll explore the second table. I do
know what an autonumber key is. ;) I'm no dummy, but I'm no DBA master
either that's for sure. My only practical experience was a college course,
and this database I built a couple of years back. :)

Thanks,

Kevin

[Kevin]

On Mon, 19 Dec 2005 09:05:08 -0800, jeremygetsmail wrote:

For a great discussion of why this is the right approach, check out Paul
Litwin's article "The Fundamentals of Relational Database Design". It's
posted, with Paul's permission, on my old website at
http://www.abcdatabase.com, in the developers's section.

All I get is : "this is the new server"

[Kevin]

On Mon, 19 Dec 2005 14:13:14 -0600, David W. Fenton wrote:

je************@gmail.com wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:

I think I get it. It sounds like you have a table that lists people and
also contains information about their participation in your program. You
should build a second table. one table will hold a list of the people,
and any information that describes those people. That table will have an
autonumber primary key (check help if those terms are new to you). The
second table will have information regarding the participation of these
people in your probram, including that field ProjecYear.

And this second table will have a record for each person for each year
they participated.

Seems like a lot of repeating data, but I guess that's the way to do
it.

each record holding something like:

id (key from first table)
year
date
site

Now, there's 3 events per year. Should I create a record for each event,
per year, per person? So potentially each person could have 3 records per
year. If I tried to combine one record per year I'd probably end up with
many null values. :\

Kevin

[jeremygetsmail]

Huh. Seems I was so spacey yesterday I typed in someone else's URL!
Sorry about that. You should have more luck at
http://www.abcdataworks.com.

Jeremy

[David W. Fenton]

Kevin <wi******@hotmail.com> wrote in
news:pa**************************@hotmail.com:

On Mon, 19 Dec 2005 14:13:14 -0600, David W. Fenton wrote:

je************@gmail.com wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:

I think I get it. It sounds like you have a table that lists
people and also contains information about their participation
in your program. You should build a second table. one table will
hold a list of the people, and any information that describes
those people. That table will have an autonumber primary key
(check help if those terms are new to you). The second table
will have information regarding the participation of these
people in your probram, including that field ProjecYear.
And this second table will have a record for each person for each
year they participated.

Seems like a lot of repeating data, but I guess that's the way to
do it.

Well, that's natural if you're not going to overwrite last year's
data with this year's. The only alternative is repeating fields
within the record, and that's obviously a bad choice.
each record holding something like:

id (key from first table)
year
date
site

Now, there's 3 events per year. Should I create a record for each
event, per year, per person? So potentially each person could have
3 records per year. If I tried to combine one record per year I'd
probably end up with many null values. :\

Well, now you've changed the definition of the problem.

But I dont' see that there's anything at all wrong with your
description here.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/