6 replies to this topic

[twins]

I'm creating a Student Administration database and I need to do the following but I'm not sure what to do: 1. I want to change my current class from "1A" to "2A" without having to change manually again. Note: I need to take into consideration promotion and retained. So I'm quite sure I can't just use an incrementation formula basis. 2. I need to move student records who have finished school and are leaving to a new table. 3. I want to create a query to search for students living in a particular area. Unfortunately I can't upload my database here so if you need to see my database to increase your understanding then just ask and i shall send to your e-mail. Your help and assistance is very much appreciated. Thank you.

[Ax238]

Hello twins,

Your request is pretty vague and I'm not entirely sure I know what you are asking. What is this database for? (single class, high school college, etc) Maybe if you provided some examples to go along with the explanations, it may help. Here are preliminary responses based on your info:

• UPDATE StudentClasses SET Class = '2A' WHERE StudentID=123 AND Class = '1A'

• Why not just have a "graduated" checkbox field. All aside, I'm sensing some 3NF issues with the structure of the database.

• SELECT * FROM Students WHERE Zip = ?

You can try compressing your database file and uploading it. If you don't feel comfortable posting it here, you can PM/email it to me.

Regards,

Ax

[twins]

Thank you for your reply and sorry for the vague information. I've just uploaded my file into rapidshare.

Here's the link: http://rapidshare.co...RATION.mdb.html

I'm creating this database for a junior high school. My example would be when a student record is entered into say Form 1 and then the next year he is promoted to Form 2 but then I want this to be done automatically without having to change the class manually. And I was thinking of having a checkbox field for when the teacher can tick the retainers and the others promoted to Form 2. So that the new batch of Form 1 students can be entered into Form 1. At the same time as for those students who are going to graduate from school, I need their records to move to another table for Graduated students(though I haven't included this in my database).

[Doug]

While Ax238 is a wonderful Tech and may already have you headed in the right directions...

Here is an additionall resource of MS MVPs specializing in Access
http://www.mvps.org/access/

Best Regards

[Ax238]

As far as the forms go, I think it would be best to store which form the student is on as a field in the Students table. You can then update it whenever you need to, for whatever student you need to. On the form itself, you can just use the records that have the specific form number that form represents. I'm still not a fan of your graduated students table, I think you should keep the students in one table, graduated or not. You could then specify which level they are at or if they are graduated in a student status field/table. That way you aren't moving records around that really won't change (Name, DOB, contact info, etc).

[twins]

Ok. So say I don't move the graduated students to a new table and remain them in the current table, how do I get them to be excluded in the form when the new batch of students get promoted. It will be very cumbersome to change each and every students in the form to a new class when they are promoted or retained, do you know of any methods to deal with this? If not then I'll just have to leave it as it is... There is another task I would like to add which matters with the address function... Example: The students address is Spg 343, House no 2, Gadong, Kg Katok. As the method of entering the address is not standardised, what criteria do I enter when searching for the address? Say I would like to search for students living in Kg Katok. Is it possible to do this the same way I did with my class query?

[Ax238]

For the first item, you can just use a single UPDATE query to promote or graduate the students. For example:

UPDATE Students SET Class = 'GRAD' WHERE Class = '5C'

That's it, nothing to it. You'd want to promote the graduating class first and then each class in reverse sequential order. I don't know if I completely understand the process or terminology, maybe you could explain it better to me.

On the second item, I would advise you to split the address out into different fields, like Address1, Address2, City, Province, PostCode. With the format you have now, you'd have to search based off the whole address, as follows:

SELECT * FROM Students WHERE ADDRESS LIKE '*Kg Katok*'

Let me know of any other questions.

Ax