how do I query dates

[Guest]

I am setting up a school database of former students. Right now, I have a
"dates attended" column with years entered (e.g., "89-95"), although I know
I'll probably need to put this in a better format. I'd like to be able to
query a certain year (as in, all students who attended in 1992), but of
course that doesn't seem possible with the way I entered the data. I'd
rather not type in each year separately, as we are a PreK-12 school and some
students attended for many years. How can I set this up or set up my query
better? (It's too late to redo the entire table; right now each row is a
different student, and I need to keep it that way.)
Please answer in SIMPLE, step-by-step terms... I'm just starting to figure
this out!
THANK YOU!

 

[Ken Snell [MVP]]

Why is it too late to redo the table? That is your best solution, as
anything that we help you devise for this problem will likely not be the
solution for the next problem related to the wrongly stored data.

You don't need to delete what you have. Instead, create a new table that has
these fields:
StudentID
YearAttended

The above two fields could be a primary key together, or should be a unique
index.

Then, create records in this new table for each student for each year. This
could be done by a specially designed query that would read your current
"bad" field and convert to individual records for you, which would minimize
your data entry time.

Then delete the "dates attended" field from your original table. This new
table joins back to the original table through the StudentID field (or
whatever you have named it), and you can easily query to find students who
attended before a certain year, after a certain year, during a certain year,
etc.