Ho to obtain TOP 5 marks

[Som]

Hello everyone,



I am very new to Access. Kindly excuse my ignorance.

I've made a table like this. ( with 8 columns)

Roll, Name, Subject1, Subject2, Subject3, Subject4, Subject5, Subject6.

I want to discard the lowest marks among these 6 subjects and SUM those remnant five marks in the 9th column in a Report.

Previously in Excel, I've used "LARGE(A1:A6,{1,2,3,4,5})" and got my desired result but with access I am at my wit's end.

Kindly help.

Warm regards,
Som

[orange]

Your table structure is incorrect for database. You will find that Access is very different than Excel.

I recommend you work through this tutorial to get a better understanding of database, database design, tables, relationships and
most important - to understand Normalization.

[Som]

First thank you a ton for the great pointer. I found your recommended link very helpful after my first glance.

I'll be highly obliged if there is a specific, simple and quick solution or step by step explanation to my problem keeping in mind my complete beginner's level of understanding and immediate requirement to finish this project.

I found an easy example of calculation of only total marks using sqlite database:
SQL syntax:
UPDATE TABLE_NAME set COL4 = COL1+ COL2 + COL3;

( UPDATE STUDENTS set TOTAL = ENGLISH + MATH + SCIENCE; )

Now my questions are:
1. Is there anything simple ( including obtaining best of five marks function ) that can be done in access preserving my incorrect table structure?
2. If not, what can be the ideal table structure or step by step procedure in simple words to solve the problem?

Warm regards,
Som

NB. Could any Admin kindly fix my typo in the title of this thread i.e. "How to" instead of my nervous and hesitant "Ho to"?

[orange]

Is COL4 a field in your Table?

Assuming you have a number of Students who have Marks in 6 Subjects , and not knowing what ROLL represents, a more appropriate structure may be:

tblStudent
StudentId PK
StudentName

tblSubject
SubjectID PK
SubjectName
OtherSubjectInfo

tbl_jnc_StudentTakesSubject
StudentID
SubjectID
Mark


tblStudent---->tbl_jnc_StudentTakesSubject<---------tblSubject

Review these links Junction table and Many to Many relationship

[Som]

Is COL4 a field in your Table?

No, COL4 was not in the Sqlite Database. It was created on the fly after I executed the query with "set" function.

"...... and not knowing what ROLL represents, ...."

I apologize for the confusion. ROLL is a unique identifier and Primary Key, akin to studentID, in my Students table.


I'm going through your links again and I'll create the tables soon and report you back as early as possible.

Warm regards,
Som

[orange]

Are you using an Access front end to a sqllite BE? I haven't tried that, but am very interested in any info you might have.

[Som]

Earlier I used Firefox SQLITE Manager plugin as front end to SQLITE database (as back end) just to play/manipulate some data in my local standalone instance of Opensimulator, an open source multi-platform, multi-user 3D application server. (http://opensimulator.org/wiki/Main_Page).

I want to make the whole student database in access mainly because if has a very convenient, customizable "on the fly" report generation facility, especially with the "expression builder".

Regarding my progress, this is what I have done so far from your valuable inputs/suggestions.


Kindly guide me correcting my mistakes.

Warm regards,
Som

[orange]

In the middle table -- which really represents Exam Results (or something similar) - you need a Mark field.
This structure will allow a record for each combination of
StudentID, SubjectId, and will record a Mark.
Now if there were multiple exams, you would add another field ExamDate and include it in the composite PK.

When you have repeating fields eg Elective1, Elective2, Elective3... it is a red fag that the Table has probably not been Normalized.

What exactly is an Elective? How does it differ from Subject? Are there core Subjects, and Choices/elective Subjects?

[Som]

1. I am a bit lost here with the mark field and how it will be associated with the concerned subject.

2. To simply the matter, let's think there is only one exam for the time being.

3. Electives are just other subjects, like History, Geography, Physics, Chemistry, Math etc. Kindly treat them as normal/core subjects. So it is a total of six subjects and from the total of those we will have to discard the lowest mark.

Warm regards,
Som

[orange]

Think of the middle table with records:

StudentId, SubjectID, Mark
1,              4,               79 ---------------> so student: John, Subject: Geography scores a Mark of 79
2,              4,               82---------------> so student: Otis, Subject: Geography scores a Mark of 82
3,              4,               67---------------> so student: Sally, Subject: Geography scores a Mark of 67
...
1,              2,               80---------------> so student: John, Subject: Algebra  scores a Mark of 80
1,              9,               78---------------> so student: John, Subject: History scores a Mark of 78

Structure wise:

tblSubject
SubjectId
SubjectName


It seems elective /core depends on the Student and Subjects (if it is Student selectable)
So I'd add a field in the junction table

SubjectType <-------------------either core or elective depending on the student

[Som]

Thank you sir for the detailed guidance. Much Appreciated.
Here is my progress so far. Kindly have a look.



And



And



I am eagerly awaiting your further instructions.
Warm regards,
Som

[orange]

OK, at this point what exactly is our question? Do you have a lot of records? Is your data in sqlite?

Do you know how to create queries?

google w3schools sql for details on sql

[Som]

OK, at this point what exactly is our question?

I want to obtain the best of five marks of each student discarding the lowest subject mark.

Do you have a lot of records?

Not much, records of only 150 approx students.

Is your data in sqlite?

No, I am planning to use access for it.

Do you know how to create queries?

This is my weak point. I can create simple queries.

google w3schools sql for details on sql

Okay Sir. Checking out and reporting you back at my earliest.


Warm regards,
Som

[orange]

Here is a good starting point for TOP N is http://allenbrowne.com/subquery-01.html

Look for the heading TOP N Records per Group

How do you plan to get the existing records into the database? If you already have them in sqlite, you can use an ODBC link, or export to a CSV.
Or are these records not in electronic form??

[Som]

Thank you sir. I'm checking out your links.

There is no existing record yet. Earlier I used excel to calculate everything and imported those fields into access and generated the report. For sure that served my purpose but now I feel that was an unsmart way to do it.
So now I want to learn the grammar of access to do it in the right way.


I will get the student wise marks per subject soon in hard copy. That will be 6 paper strips comprising StudentId, Name, and Mark.
After that, I need to post these into the table of this database.

Warm regards,
Som