School Database info

[george2013]

Hello
Please if you can help me i work on a database for our school, and I need i little help. Encountered the following problem, How can I create a field that automatically send all my students to next year of study, say they are in the year of study 15/07/2012 - 15/07/2013 I wish that at the end of the school year to come 15/07/2013 - 15/07/2014 and as for the year of study become from I II and from II become III
Thank you very much.

 

[way2bord]

Hello
Please if you can help me i work on a database for our school, and I need i little help. Encountered the following problem, How can I create a field that automatically send all my students to next year of study, say they are in the year of study 15/07/2012 - 15/07/2013 I wish that at the end of the school year to come 15/07/2013 - 15/07/2014 and as for the year of study become from I II and from II become III
Thank you very much.

You don't make a field that automatically updates.
You make an UPDATE QUERY. Run the query, it updates your table data.

An alternative solution is to keep your historical data and APPEND new data to your table using an APPEND QUERY:
ie...

tblStudents:
AutoID/StudentID/StartDate/EndDate/StudyYear
1 / 1234 / 07-15-2012 / 07-15-2013 / 1
2 / 1234 / 07-15-2013 / 07-15-2014 / 2

If you need StudyYear to be represented by Roman Numerals, things get a bit trickier.

 

[george2013]

You don't make a field that automatically updates.
You make an UPDATE QUERY. Run the query, it updates your table data.

An alternative solution is to keep your historical data and APPEND new data to your table using an APPEND QUERY:
ie...

tblStudents:
AutoID/StudentID/StartDate/EndDate/StudyYear
1 / 1234 / 07-15-2012 / 07-15-2013 / 1
2 / 1234 / 07-15-2013 / 07-15-2014 / 2

If you need StudyYear to be represented by Roman Numerals, things get a bit trickier.

thank you so much for the reply, I'll try, I hope you succeed

 

[RainLover]

I have seen this before in a school.

What they did was to make a copy of the database, both front and back ends and burn onto a CD which was placed in their safe.

Then they ran an update set of procedures. One set for the simester and another for end of year.

That is not the way I like to do things but it was not my call.

As way2bord said the way to do what you want is through a series of action queries.

Make sure you do this on a copy. Also do not allow others to know how to do this. Perhaps a new form with a password protected set of command buttons.

Have fun

 

[george2013]

I have seen this before in a school.

What they did was to make a copy of the database, both front and back ends and burn onto a CD which was placed in their safe.

Then they ran an update set of procedures. One set for the simester and another for end of year.

That is not the way I like to do things but it was not my call.

As way2bord said the way to do what you want is through a series of action queries.

Make sure you do this on a copy. Also do not allow others to know how to do this. Perhaps a new form with a password protected set of command buttons.

Have fun

Tnx very very much . but just one more question
I think I'm wrong from the start, to explain, I need 4 tables. 1 Details students, 2 students register, 3, Students diplomas 4, Students Olympia, Please tell me how i create the relationship for this .

 

[RainLover]

Details Student has a Primary Key of Autonumber. All the other tables have a Foreign key of Integer.

The three tables then relate to the Primary Key in Details student.

There is a Document on my Sky Drive that has a sample for Naming Conventions. I would suggest that you read it now, before doing any more desigm work.

Click on the Blue link in my signature. You will find it there.

 

[george2013]

Details Student has a Primary Key of Autonumber. All the other tables have a Foreign key of Integer.

The three tables then relate to the Primary Key in Details student.

There is a Document on my Sky Drive that has a sample for Naming Conventions. I would suggest that you read it now, before doing any more desigm work.

Click on the Blue link in my signature. You will find it there.

Thanks for the response, but in the other 3 tables, who will be the primary key?

 

[RainLover]

The Primary Key in every table I build is always Autonumber.

See that document about naming them.

 

[george2013]

The Primary Key in every table I build is always Autonumber.

See that document about naming them.

I Have only 4 posts, i need 10 to see your signature

 

[RainLover]

Here it is.

 

[way2bord]

Here it is.

For a more formal approach to object naming, google: "Leszynski Naming Conventions"

 

[george2013]

Details Student has a Primary Key of Autonumber. All the other tables have a Foreign key of Integer.

The three tables then relate to the Primary Key in Details student.

There is a Document on my Sky Drive that has a sample for Naming Conventions. I would suggest that you read it now, before doing any more desigm work.

Click on the Blue link in my signature. You will find it there.

thank you so much for the answers given,
please explain to me (do not understand) , Details Student has a Primary Key of Autonumber. All the other tables have a Foreign key of Integer. , the part where i have the Foreing Key of Integr. I dont understand pls help me.
and by the way I read your document.

 

[RainLover]

To start your Primary key can be any value. It could be Text if you prefer. It is just that Autonumber is always Unique which is what you want. It only appears once for that table. Hence it is unique.

The Table that you wish to link to, which is the many side, has a Foreign Key. This is not unique and can appear many times.

Tale an employee's work records.

In the Master Table where you have the Primary Key we enter all the things that do not change. Like Name, DOB. In the Child table we enter things that do change or there is many of them.

Looking at employee work hours.

FK Value Hours PK
1 8.5 1
1 7.0 2
1 6.25 3

The Child has its own Primary to denote a particular record. It has a Foregin Key, in this case 1 that relates to the Master. So every record so far belongs to the same person.

The next employee would have a PK of 2 and all records in the child table would have records with the FK as 2 to match the next employee. The PK would be a continuation from where you were up to. In this case 3,4,5,6 etc.

Other things could be different and require yet another Table. Like Address, Phone Number, Next of Kin. These things can change so by putting them in a separate table you know a history. Like current address and previous address.

Hope this helps.

If you like create a new Database using one of the Templates provided. It may help you to understand.