Displaying first five non-empty fields.

[mathsbeauty]

Hello! I am new here. This is something interesting and challenging. I doubt whether this is possible in access or not. I have a table in access containing a field for students name and another 10 fields for marks in 10 subjects. A student has appeared exactly for 5 subjects out of 10. Now in a report I want to display exactly 5 subjects with marks for which student has appeared i.e.the problem is to display the first five non-empty fields out of 10. I hope to get some help from experts.

 

[Galaxiom]

It would be easy if your data structure was properly optimised. The marks should be stored in a related table with one record for each subject per student.

Students: StudentID, StudentName, etc
Subjects: SubjectID, SubjectName, etc
Marks: StudentID, SubjectID, Mark, MarkDate, etc

 

[mathsbeauty]

Hello Galaxiom! You did not get my problem. I have well organ.ised data.I have a table in access containing a field for students name and another 10 fields for marks in 10 subjects. A student has appeared exactly for 5 subjects out of 10. Now in a report I want to display exactly 5 subjects with marks for which student has appeared i.e.the problem is to display the first five non-empty fields out of 10.

 

[gemma-the-husky]

Hello Galaxiom! You did not get my problem. I have well organ.ised data.I have a table in access containing a field for students name and another 10 fields for marks in 10 subjects. A student has appeared exactly for 5 subjects out of 10. Now in a report I want to display exactly 5 subjects with marks for which student has appeared i.e.the problem is to display the first five non-empty fields out of 10.

that's what G is saying. Your data is NOT normalised, or well organised. In your case, if you add a new subject, you have to redesign your tables. That's a clear indication of incorrect design. You should have these basic tables, as G said.

students (name etc)

courses (title, etc)

examstaken( student, course, mark)

so each student will have a separate row in the examstaken table JUST for each exam he did take. No blanks to worry about. Now you can just count, sum, average etc etc the total exams for each student.

[instead of the very difficult scenario you now have]

 

[mathsbeauty]

Sorry to say that you are not getting my problem. I have a table
containing 11 columns. First column contains students name. The rest
10 columns contains their marks in 10 different subjects. Each student
has selected exactly five subjects. I want to create a report which
displays students name and below it the five subjects marks which he
has opted for. How can this be done?

 

[gemma-the-husky]

yep

we are getting it - but what we are saying, is that you shouldn't have a table like that.

you shouldn't have separate columns for each subject. You have to try and move away from spreadsheet thinking.

You should change your design to a relational one, and you will find your problem is resolved automatically (or at any rate becomes one that is intrinsically very easy to answer)

 

[boblarson]

Hello Galaxiom! You did not get my problem. I have well organ.ised data.

Yes, he did get your problem. Your problem is your database is NOT well organized and is NOT properly normalized. You should NOT have 10 fields for marks in 10 subjects! This is a typical beginner mistake and so we are attempting to correct that before you get on to thinking that you have it well designed.

So, the solution to your problem is to FIRST, before anything else, is to redesign it like Galaxiom has specified. Sorry, but that is the straight scoop and that comes from him, Dave AND me (professional Access developer with 14 years of Access experience).

 

[mathsbeauty]

Thanks for all your views. Its good that you got my problem. I am not very experienced in access but have completed few simple projects. Can you please describe some procedure in short to solve this problem?

 

[jdraw]

The problem people are advising you on is that your tables need to be Normalized.
There are many examples, some quite detailed and complex.

Here is a tutorial on Normalization with explanation and example.
http://www.phlonx.com/resources/nf3/#intro


You may find others by Googling "Normalization". There may even be videos on Youtube....

Good luck