What do I do with old records?

[Guest]

I maintain a database for a youth group. We serve kids in grades 6 through
12. Because I am database challenged, I have been trying to learn how to
best use Access through the one book my employer bought me, the one seminar
they sent me to, and these newsgroups here! Now I have a task that I don't
have much time for; I'm leaving and only have 3 more workdays until I am gone.

For the last two years, at the end of each school year, I have converted the
graduates along with the younger students: two years ago, the 12th graders
became 13th graders; last year, the 13th graders became 14th graders, and the
12th graders became the new 13th graders, and so on. Now I am leaving this
job (dare I say, Yay?) but I want to move these college-age people out of the
main database and put their records somewhere else. I don't want to just
delete them, because another associate organization may want the information
at some point.

What is the best way to do this? I do not want to use code (don't even
begin to fathom it). I can make new tables, queries, etc., using the
wizards, and was hoping someone here could take my pc-ignorant self through
the steps required to do this.

 

[George Nicholson]

You don't provide a lot of information on your data structure so it's hard
to say what might work best for you, but consider adding an "Active?" field
to your students table. Set Active it to True for everyone in 12th grade or
below. Modify queries, etc. so that their results are based on Active =
True.

Going forward, "Moving" the data somewhere else is probably more trouble
than it is worth (in my opinion..). Adding a simple "flag" field that can be
filtered on keeps all your data intact while allowing you to easily restrict
queries, forms & reports to the "current" data.

Or maybe you don't need to add a new field, maybe you just need to modify
queries, etc. so that they don't show students > 12th grade (or > a certain
age)?

HTH,

 

[Guest]

Add a Yes/No field to your table called something like ActivePlayer and make
the default value Yes. You'll also need to update all players to Yes at
first. Once a year or so change all the college aged students to No.

Then change all your queries, forms, and reports to add this field and put
make its criteria = Yes.

 

[John Vinson]

What is the best way to do this?

The best way is... don't do this!

Instead, create a new grade just for these people. Promote all of them
into that grade (using an Update query, I sure hope you're not doing
this student by student manually!!)

They will then be available using Queries to select only the people in
that "college" grade; if necessary, you can replace references to the
table with references to a query selecting only those students *not*
in the College grade.

John W. Vinson[MVP]

 

[Guest]

Thanks to both of you! Since we're a faith based organization, we refer to
the kids as students, not players, so I think a yes/no field entitled
"Alumna" and just "tossing them all in there" -- that's probably better than
having all 13th and 14th graders, and if, down the line, someone really wants
to know how far past high-school a certain person is, there's always the
Graduation Year field to refer to. I just wish there was a quick way to
enter the Yes into those 957 records! That is a tedious task I'm not looking
forward to.

Thanks for everyone's quick help!

 

[Guest]

Never did an update query ... afraid I'd mess it up and lose data

But I will miss all you folks here (no Access at new job), you are really
MVP's who have taught me much about both Access and Community Forums!

 

[Guest]

What if I were to change that Grade field that already exists, listing grades
"zero" (for those records we do not have that information on) and 6 through
14, from a Number field to a Text field? Would I lose the grade data that is
there? (I never knew that it was a number field, it's not necessary that it
be a number field, we never do any math computations with the grades anyway
and it would be a strange concept to do so anyway.) But if I could change
the field type to a Text field, with say a size of 10 characters, then I
could populate the appropriate records with the word "Alumna".

Right?

 

[John Vinson]

Never did an update query ... afraid I'd mess it up and lose data

Back up your database first, of course (anytime you do anything major
like this... and, in point of fact, every time you do ANYTHING that
you don't want to have to do over!)

Then create a Query. Apply whatever criteria would identify alumnae.
Open the query as a datasheet and check to be sure it's getting the
right people (and only the right people).

Change it to an Update query. Select the Alumna field.

On the Update To line under Alumna type

True

Run the query by clicking the ! icon.

Can't get much simpler than that - and it sure beats manually checking
937 checkboxes!!!

But I will miss all you folks here (no Access at new job), you are really
MVP's who have taught me much about both Access and Community Forums!

Thank you!

Bear in mind that if you're using any Microsoft software, there are
newsgroups for all of them. (Ours is among the best of course... <g>)

John W. Vinson[MVP]

 

[Guest]

Hi Max,

Keep it as a number field. If you ever try to sort on numerical characters
in a text field, strange things happen like:
1
12
13
2
3
4

Instead of 'zero' you could use a really bogus number like 99 or 0 instead.
Just have your queries exclude the number that you chose in the criteria.

As far as updating the 900+ Yes?No fields quickly, it's very easy to do.
Create a query with just the Yes/No field selected. Change it to an update
query and in the Update To row put Yes or, believe it or not, -1. No is 0
and Yes is -1. Very strange indeed.