Help with... er... not sure how to describe this

[Will]

Hello all

I'm not sure how to word this, which is one reason why I couldn't find
anything on Google to help me, so please bear with me as I try to
explain myself...

I am trying to create a database for a youth club, with relevant info on
members, non members, who attended what activity when etc etc. The
thing I am trying to do is, each youngster will be in a particular year
group in school (i.e. first year, second year etc), I have a simple
table with member records (the basics: name, date of birth, address,
next of kin etc), and one table for year groups, and each member will be
linked to the year group they belong to.

The thing I am trying to figure out how to do is, when the next year
comes, what is the best way for me to go in and change the year group
collectively? Like for instance, how do I change all the first year
kids to second year? And second to third? And so on... The only way I
could think of is going into each record and changing manually, but not
exactly ideal when we're working with over 50 kids per year group and
potentially into 4 figures for the whole club...

Does anyone have any suggestions or solutions? I tried to look for
school database examples on google but instead I get a bunch of "how to
do databases" from school websites lol

I'm guessing there is some kind of macro or programming that can be used
to do this, but I just don't know what that is, can someone please point
me in the right direction?!

Thanks so much in advance for the help and time

Kind regards
Will

[pietlinden]

can't you calculate the year group they belong to? What's the rule for
that? Is it based on the child's age, or on the year the child joined?
Then you can use DateDiff() to figure it out on the fly - and you
won't ever have to update the stuff manually. then when the next year
comes, your query will rerun and will automatically update itself. Or
you could make the value static (may be necessary) and use an update
query to change it.

[adm]

the short answer, as pietlin explains, is to use an update query.
Access lets you create these using Query Wizard, etc. To do it with a
simple SQL statement, it would be something like:

UPDATE tblStudents SET YearGroup = "Second" WHERE YearGroup = "First"

Create a different update query for each year group. Make sure you are
careful about the order you run them in...otherwise you'll put all the
first years in with the 2nd years, and then you won't be able to
distinguish them.

But as pietlin explained, you should probably reconsider this way of
tracking things and base it on something calculable(sp?) instead.

[Bob Quintal]

"adm" <ad*****@yahoo.com> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

the short answer, as pietlin explains, is to use an update
query. Access lets you create these using Query Wizard, etc.
To do it with a simple SQL statement, it would be something
like:

UPDATE tblStudents SET YearGroup = "Second" WHERE YearGroup =
"First"

Create a different update query for each year group. Make sure
you are careful about the order you run them in...otherwise
you'll put all the first years in with the 2nd years, and then
you won't be able to distinguish them.

But as pietlin explained, you should probably reconsider this
way of tracking things and base it on something
calculable(sp?) instead.

Or modify the table slightly, change the yeargroup to a number.
Then at the change of year, run an update query that adds one to
every row. An enhancement would be to add a lookup table with two
columns, the number and its description.

--
Bob Quintal

PA is y I've altered my email address.

[Will]

Bob Quintal wrote:

"adm" <ad*****@yahoo.com> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

the short answer, as pietlin explains, is to use an update
query. Access lets you create these using Query Wizard, etc.
To do it with a simple SQL statement, it would be something
like:

UPDATE tblStudents SET YearGroup = "Second" WHERE YearGroup =
"First"

Create a different update query for each year group. Make sure
you are careful about the order you run them in...otherwise
you'll put all the first years in with the 2nd years, and then
you won't be able to distinguish them.

But as pietlin explained, you should probably reconsider this
way of tracking things and base it on something
calculable(sp?) instead.

Or modify the table slightly, change the yeargroup to a number.
Then at the change of year, run an update query that adds one to
every row. An enhancement would be to add a lookup table with two
columns, the number and its description.

Thanks to everyone for the responses (for some reason btopenworld didn't
pick up pietlan's reply, had to go to Google to find it!), really
appreciate it!

The problem with the calculation method is that you could have different
aged kids belonging to the same year group, so it's hard to define them
that way, but it looks like an Update query is the way to go... at the
moment, I do have a lookup table for the year groups, I just wasn't sure
how to do the actual updating from one year group to another
collectively, but I'm gonna try the Update query one... I guess it's
just making it so that it Updates in the right order!

Thanks once more for everyone's time and help, most appreciated.

Cheers!
Will