Need to first define the problem

atejani · Nov 19, 2019

Hello,

We are a charitable organization called WIPAHS here in Tanzania (Africa) looking after the needs of marginalized communities. Currently we use excel VLOOKUP to generate our report cards for the students but we would like to track the students progress as they move from year to year.

Each class has a fixed number of students and fixed subjects. Basically when I change the class I need the sub-form/datasheet to change the subjects and students depending on another table. It needs to look like an excel sheet (datasheet with students downwards and subjects sideways) and not a normal form which I see on many templates.

Our budgets are very limited so I have been tasked to learn how to do it in Access. I need to first phrase the problem in google so I can find an answer. Any tips from your side would be much appreciated. Am willing to learn VBA/SQL as I enjoy self-studying.

Thanks and regards,

Asief.

isladogs · Nov 19, 2019

Hi and welcome to AWF.
You posted to sample databases which is a moderated area not intended for questions.
I've moved it to a more suitable forum.
Someone will be along soon to offer suggestions

vba_php · Nov 19, 2019

atejani said:
Basically when I change the class I need the sub-form/datasheet to change the subjects and students depending on another table. It needs to look like an excel sheet (datasheet with students downwards and subjects sideways) and not a normal form which I see on many templates.

Asief,

in access, form objects can be viewed in "datasheet" view and if you want a form to display like, for example, the tab called "Grade 1A Data" in your sample excel file, you would create a form in access, set the recordsource to a query which joins the fields of the "students" table and "classes" table and set the "default view" to datasheet. you've got a large amount of data, so I'm not sure you'll find anyone here who is willing to provide u with a sample database that you can learn from without being paid for doing it.

mike60smart · Nov 19, 2019

Hi

You say "Each class has a fixed number of students and fixed subjects."

When a Student moves to a higher Class do the Subjects stay the same??

isladogs · Nov 19, 2019

Asief
I'm not trying to sell you anything but you could have a look at the free demo ACCDE versions of two of my commercial schools apps : School Data Analyser and Student ICT Database.
The first is a very wide ranging app and a big download. The full version is almost certainly well beyond your budget. However it may give you some ideas.

The second app is more specific but may help you get started. See if it helps

atejani · Nov 23, 2019

mike60smart said:
Hi

You say "Each class has a fixed number of students and fixed subjects."

When a Student moves to a higher Class do the Subjects stay the same??

No they do not.. any idea how to go about it?

mike60smart · Nov 23, 2019

Hi
With the number of Class's & Students it would take some time to put together an example for you.

atejani · Nov 23, 2019

Just a simple example would do - 5 students and 2 classes... I could then replicate it on a larget scale

mike60smart · Nov 23, 2019

What does OL & TE represent in Subject Results??

isladogs · Nov 23, 2019

Hopefully the attached quick demo I just created will help.
Note the use of a junction table tblStudentClasses.
Also the use of referential integrity in the relationships window.

I do recommend you look at the links I provided and possibly download the demo versions.

Pat Hartman · Nov 23, 2019

@atejani,
Relational databases are not spreadsheets. Attempting to replicate a spreadsheet will just lead to frustration and a hatred for Access. To be successful, you need to take off your Excel hat and put on an Access hat and think about the problem in terms of relational tables.

Given what I see in the spreadsheet, it would take a competent Access developer less than 10 hours to reproduce what you have in a way that in a way that will allow you to grow. The problem with spreadsheets is the data is stored in the same way it is presented and in a RDBMS, you store the data in an optimal fashion but report it in whatever format makes sense for the user. So the same data can easily have multiple presentations. Given your spreadsheet design, you have to modify formulas to add subjects or students and build new pages to add new classes. All that goes away once this is replicated in Access.

Start by doing some reading on normalization. You will end up with several tables.

Students
Subjects
Classes
SubjectsClasses - subjects for each class.
StudentsClasses - Students in each class
StudentSubjectGrades - grades and comments for each subject

Take a stab at properly normalizing the tables and we'll help you to move on from there but creating a correct set of tables is required before moving on to anything else.

isladogs · Nov 23, 2019

Largely agree with Pat's comments

The starter app I supplied earlier has 4 tables
tblStudents
tblSubjects
tblClasses - Class & subject
tblStudentClasses - Students in each class

In my opinion there is no reason to add a separate junction table tblSubjectClasses as each class will be for only one subject. If that's not the case for you then add it as well

I omitted the grades part as I wanted to leave something for you to do yourself.
However I suggest you need to add at least two tables for this:
tblGrades - grades available for each type of class
tblStudentGrades - student grades and comments for each class taken

mike60smart · Nov 24, 2019

Hi Asief

Do you have specific Terms within an Academic Year?

Pat Hartman · Nov 25, 2019

In my opinion there is no reason to add a separate junction table tblSubjectClasses as each class will be for only one subject. If that's not the case for you then add it as well

I had the impression from the question that "class" referred to academic year which is why I created a separate table.

Need to first define the problem

atejani

New member

Attachments

isladogs

MVP / VIP

vba_php

Forum Troll

mike60smart

Registered User.

isladogs

MVP / VIP

atejani

New member

mike60smart

Registered User.

atejani

New member

mike60smart

Registered User.

isladogs

MVP / VIP

Attachments

Pat Hartman

Super Moderator

isladogs

MVP / VIP

Attachments

mike60smart

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread