 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / December 2005Database design - incorporate year of a certain event
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 I think maybe I'm missing something, why don't you add a field to the relevant tables and store the year in it ?  SignatureTerry Kreft > 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 [quoted text clipped - 6 lines] > > 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 [quoted text clipped - 4 lines] >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 >>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. [quoted text clipped - 16 lines] > > 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 > 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 [quoted text clipped - 3 lines] > 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 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 > I think I get it. It sounds like you have a table that lists > people and also contains information about their participation in [quoted text clipped - 4 lines] > 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. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ >> 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 [quoted text clipped - 6 lines] > 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 >>> I think I get it. It sounds like you have a table that lists >>> people and also contains information about their participation [quoted text clipped - 10 lines] > 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: > [quoted text clipped - 7 lines] > 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. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ 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 [quoted text clipped - 10 lines] > > 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 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" 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.