By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,466 Members | 1,125 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,466 IT Pros & Developers. It's quick & easy.

Database design - incorporate year of a certain event

P: n/a

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
Dec 19 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
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
Dec 19 '05 #2

P: n/a
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

Dec 19 '05 #3

P: n/a
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
Dec 19 '05 #4

P: n/a
"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
Dec 19 '05 #5

P: n/a
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

Dec 19 '05 #6

P: n/a
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/
Dec 19 '05 #7

P: n/a
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

Dec 20 '05 #8

P: n/a
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"
Dec 20 '05 #9

P: n/a
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
Dec 20 '05 #10

P: n/a
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

Dec 20 '05 #11

P: n/a
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/
Dec 20 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.