question about criteria in query and structure

[gcapp]

Hello,
Hopefully someone can help me with this issue.
I am trying to produce a report based on a query but I am not sure if
have the structure of the table and/or query set up correctly.

I have a table called Students that has ID, LastName, FirstName an
Years Attended. Now this is where I am not sure how to set this up
Under Years Attended, I would like to have it setup, so this databas
can be used for an infinite amount of time. So under the Year
Attended field I have a text field to enter the years attended lik
this "2005, 2006, 2007, 2008", instead of fields for every year.

Now under the query that feeds off of the Students table, under th
criteria for the Years Attended field, I have "[Enter Years Attended]"
So then when you open the query, you are prompted to enter the year
attended.

But here is where the problem is. I would like it where you woul
enter a year like "2005" and get all the students that went to schoo
that year. But in my Students table, the Years Attended field ha
entries like "2004, 2005, 2006, 2007" and the query will not grab th
2005 year.

Is there a way for me to have the Years Attended field in my table lef
the way it is (so I don't have to create fields for every year) and hav
the query grab the correct year?? Is there a way to set that up in th
query??

Any suggestions or changes that could be used here would be greatl
appreciated.

Thank you

 

[Duane Hookom]

A normalized structure would remove the year values from the student table.
Create a related table that would contain the student ID and the year.

tblStudentYears
==============
StudentID relates to Students.ID
AttendYear integer to store a single year

Each year a student attends would create a new record in the table.