Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Som is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2015
    Posts
    12

    Ho to obtain TOP 5 marks

    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

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    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.

  3. #3
    Som is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2015
    Posts
    12
    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"?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    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

  5. #5
    Som is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2015
    Posts
    12
    Quote Originally Posted by orange View Post
    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.

    Quote Originally Posted by orange View Post
    "...... 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

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    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.

  7. #7
    Som is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2015
    Posts
    12
    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

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    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?

  9. #9
    Som is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2015
    Posts
    12
    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

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    Think of the middle table with records:
    Code:
    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

  11. #11
    Som is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2015
    Posts
    12
    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

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    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

  13. #13
    Som is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2015
    Posts
    12
    Quote Originally Posted by orange View Post
    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.



    Quote Originally Posted by orange View Post
    Do you have a lot of records?
    Not much, records of only 150 approx students.


    Quote Originally Posted by orange View Post
    Is your data in sqlite?
    No, I am planning to use access for it.


    Quote Originally Posted by orange View Post
    Do you know how to create queries?
    This is my weak point. I can create simple queries.


    Quote Originally Posted by orange View Post
    google w3schools sql for details on sql
    Okay Sir. Checking out and reporting you back at my earliest.


    Warm regards,
    Som

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    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??

  15. #15
    Som is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jan 2015
    Posts
    12
    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

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-31-2014, 05:07 PM
  2. Searching marks field
    By Wilson in forum Access
    Replies: 3
    Last Post: 07-27-2012, 07:37 AM
  3. Concatenate Quotation Marks
    By millerdav99 in forum Programming
    Replies: 3
    Last Post: 04-11-2011, 09:36 AM
  4. Obtain value in record
    By jgelpi16 in forum Programming
    Replies: 1
    Last Post: 01-20-2011, 08:52 AM
  5. obtain value from listbox
    By englisap in forum Programming
    Replies: 6
    Last Post: 01-11-2011, 12:59 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums