Is there anybody out there who can help me?

[drisconsult]

In my previous thread, I was seeking help on creating a query that will allow me to update a Class, such as Class 2A to Class 3A.
This happens at the end of every academic year in schools when each student moves up a year.
So my question is how can I update an entire Class from 1A to 2A, or from 3B to 4B?
I have a primitive solution but it simply isn't good enough. In my simple solution I load the query with just the class identifier and no names. This would normally mean about 30 to 35 students. I would then manually change each 2A to 3A or 3B to 4B.
Thanking the kind soul in advance, who I know is out there.
Terence Driscoll, London

 

[pbaldy]

How about an update query?

UPDATE TableName SET FieldName = "2A" WHERE FieldName = "1A"

 

[theDBguy]

And if you're asking how not to manually enter the value to change the class into, then you could try using a table showing the conversion or upgrade from one class to another.

 

[drisconsult]

Reply to PBaldy
That certainly is a start. However in the average school say in Kenya, Uganda or Tanzania, there would be over 50 classes. What I am aiming to achieve is where a teacher enters 1A in the query and is what is the update target, which would be 2A inb this case. There must only be one query to deal with every situation.
Regards Terence

 

[pbaldy]

Both values could come from a form where the user inputs them. The conversion table could work as well.

 

[Mark_]

Two solutions;
1) Rather than track by "Class", assign them to a cohort. The cohort is normally when they are expected to graduate. You then change which class their cohort is in as they progress. One record gets updated once. This can also be automated if you have clear rules for it.

For students that do not progress, they are reassigned to the cohort they will be attending with.

2) You have a semester or year child record for each student. End of semester/year, you add a new record for the subsequent semester/year. This would be an add query that goes through all students. It would look up the "Next Class" from a table that has "Current Class" / "Next Class".

Both work. I am working on the second as there is a fair amount that needs to be tracked per student prior to them completing a semester.

 

[drisconsult]

One of the problems I am having is to make the School Management Database open to all teachers. There will be a runtime version and the same version that can be explored by teachers who wish to get involved with Access.
I am providing a PDF guide written in InDesign to show each stage of the database construction. I have avoided VBA as this would only put these teachers off wanting to get involved, although there are functions and expressions used constantly.
This database is aimed at schools in Ghana, Kenya, Nigeria, Tanzania and Uganda where I spent forty years of my teaching career.
I would like to send you the Access guide I have prepared for beginners. It's a 10mb PDF How would I do that?

 

[isladogs]

Terence
Did you follow up my reply to your previous thread as the solution for both questions is almost identical - update query/queries. I ask as you didn't respond to my reply in that thread and have asked almost exactly the same question again

 

[drisconsult]

Hello
My apologies for not being forthcoming. I returned to Access in an effort to get over the remorse I am having on a personal matter.
You will be aware of the Ethiopian plane crash last month in which 157 people were killed. I lost my godson and his wife on that plane and have just returned from Nairobi having attended a memorial service for the Kenya victims.
My daughter also lost two of her work colleagues, so it has been a torrid time all round. I will return to my Access problem later.
Sincere regards
Terence

 

[isladogs]

Sorry to hear of the recent tragedy in your life.
We're all here to help if and when you wish to continue with this question.

 

[drisconsult]

Life has to go on. I will return later, and thank you for your kind words.
Terence

 

[The_Doc_Man]

Having been in a situation of loss and having to go on, I understand. Trust me on this one: Your grief management and recovery time are more important than your project. Give yourself those moments when you need to find a window and stare out of it for a while.

Good luck with your emotional recovery, Terence.

As to the other question, this sounds like the beginnings of a denormalized database. You can certainly continue doing what you are doing, but the moment your student drops out, moves out to another school, or moves in from another school, you could have some issues. Also, what would you do if the student does not move forward? In USA schools, the pass/fail concept still is used for students who do not learn enough to be able to keep up with their classmates and so get held back.

Are you interested in retaining history for these students? I don't know the standards for the schools in the areas you described earlier, so don't know what is needed, but using the only system I know, you have some apparent omissions. (Obviously, it is your design and not mine, so I'm not criticizing so much as just exploring for understanding.)

 

[Mark_]

You have my condolences.

My aunt passed on October 25th. I took some time away from here as well.

 

[drisconsult]

Hello Doc_Man
I would like to send you some material I have developed for schools in Kenya, Uganda, Nigeria, Ghana and Tanzania. Would you kindly let me have an email address, I would value your criticism.
Terence

 

[mike60smart]

Hi Terence

I would also be willing to take a look. If you look at Private messages I have included my email address.

Regards

Mike

 

[isladogs]

Just a reminder to check this answer https://www.access-programmers.co.uk/forums/showpost.php?p=1617627&postcount=2

 

[The_Doc_Man]

drisconsult, I very rarely accept private materials. It is a habit I got into while working with the U.S. Navy and I have found it to be good for me. It is not a personal comment of any kind, except maybe a comments about my method of working with this forum.

 

[drisconsult]

Understood.
Terence

 

[drisconsult]

Can I insert a search parameter to a List Box? I need to isolate certain groups of students.
Terence

 

[pbaldy]

Sure, here's one way (a listbox would work the same as a combo):

http://www.baldyweb.com/CascadingCombo.htm