Ho to obtain TOP 5 marks

[orange]

Do you have a list of Students and Subjects now?
Is there anything you are having problems with at this time?

[Som]

Do you have a list of Students and Subjects now?

Yes, I have the list of students and concerned Subjects with me. I can also feed some arbitrary dummy data for testing purpose and delete later on.

  
StudentID    StudentName    English    Math    Physics    Chemistry    History    Geography
1                 A         40          85        75        82           50         75      ( we will discard 40 and sum the remnant 5 marks) 
2                 B         75          78        82        65           61         63       ( we will discard 61 and sum the rest)

Is there anything you are having problems with at this time?

I apologize for the delayed response. I was reading your links especially the SQL tutorials on the w3schools site.

Meanwhile I was thinking if it is possible to bypass the SubjectID. I mean can I use English, Math etc. directly instead of 1,2...? Isn't that more intuitive and less error-prone at the time of data entry?

I took a look at sub-query and TOP N etc. as kindly suggested by you earlier. But as regards my level of understanding, that is pretty advanced stuff and I need a hand-holding to proceed to the next step, imho. :-)

Warm regards,
Som

[orange]

Your record layout show are in "spreadsheet mode" not database.
In databases your records would be

StudentID Subject and Mark
1             English          40
1             Math             85
1             Physics          75
....
2             English           75

You should have test data to test your model.You can build test data to represent various scenarios.

The junction table has StudentID and SubjectId and Mark.That is for storage.
You retrieve data via queries , and the query can output subjectName from tblSubject based on a join between SubjectID in jnc and tblSubject tables.

Look at queries SELECT in w3schools sql.

[Som]

Your record layout show are in "spreadsheet mode" not database.

Ooops! Hard to get rid of the spreadsheet obsession! :-)

Look at queries SELECT in w3schools sql.

Okay Sir. Checking.

Meanwhile two more questions:
1. I've created a db using msaccess, linking an excel file and put those two in the same folder in my hdd.
Will the access db ( link table) retain its association if I copy these two files along with the folder to another computer?

2. Access has a great report generator as I've seen. Is there any other very good and stable report generator ( preferably free and opensource if possible) that can use an excel file as source?

Warm regards,
Som

[orange]

1. It should if you copy folders and files. You can always Relink.
2. Access report generator is fine for most things.

Where does excel fit in the picture?

[Som]

Thank you once again Sir.

Where does excel fit in the picture?

I am building some test data as per your earlier suggestion.
Earlier I was playing with link table from excel option and report generation earlier. Here is the result.
I want to output exactly a report like this with only Msaccess.


Warm regards,
Som

[Som]

Just ran a crosstab query with some test data. Here is the result.


Meanwhile, reading and practising SELECT query as suggested by you.

Warm regards,
Som